Skip to content

Instantly share code, notes, and snippets.

@mrvisser

mrvisser/PSQL-Version Secret

Last active July 30, 2016 13:14
Show Gist options
  • Save mrvisser/a22dddbdaa64ae1aa3dbc9637617b70d to your computer and use it in GitHub Desktop.
Save mrvisser/a22dddbdaa64ae1aa3dbc9637617b70d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
PSQL-Version
=> select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.7 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
Raw
Q-CC-Underestimated.sql
select * from client_courses where client_id = 7 and external_term_id = '1662'
Raw
Q-Report.sql
select uvi.*, ri.rule_name, ri.score_data from (
select distinct on (u.client_id, u.external_course_id, u.external_file_id, uv.upload_time)
row_number() over(
order by
u.client_id,
u.external_course_id,
u.external_file_id,
uv.upload_time,
r.generated_time desc
) as row_ordering,
u.external_course_id,
u.external_file_id,
uv.upload_time,
uv.mime_type,
uv.name,
uv.description,
uv.library_reference,
u.deleted_at,
ci.mime_type,
r.id as result_id
from
uploads u
inner join
upload_versions uv on (uv.upload_id = u.id)
inner join
client_courses cc on (cc.client_id = u.client_id and cc.external_course_id = u.external_course_id)
left join
content_items ci on (ci.id = uv.content_item_id)
left join
results r on (r.content_item_id = ci.id)
where
cc.client_id = 7 and
cc.external_term_id = '1662'
order by
u.client_id,
u.external_course_id,
u.external_file_id,
uv.upload_time,
r.generated_time desc
) uvi
left join
result_items ri on (ri.result_id = uvi.result_id)
order by
uvi.row_ordering;
Raw
QP-CC-Underestimated.txt
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on client_courses (cost=142.47..5918.01 rows=36 width=67) (actual time=0.423..2.003 rows=3472 loops=1)
Recheck Cond: (client_id = 7)
Filter: ((external_term_id)::text = '1662'::text)
Heap Blocks: exact=78
-> Bitmap Index Scan on client_course_pk (cost=0.00..142.46 rows=3472 width=0) (actual time=0.389..0.389 rows=3472 loops=1)
Index Cond: (client_id = 7)
Planning time: 14.326 ms
Execution time: 2.596 ms
Raw
QP-Report-DisableHashJoin.txt
Sort (cost=263195.04..263198.04 rows=1202 width=218) (actual time=15602.288..15850.752 rows=836463 loops=1)
Sort Key: (row_number() OVER (?))
Sort Method: external sort Disk: 136664kB
-> Nested Loop Left Join (cost=260665.28..263133.55 rows=1202 width=218) (actual time=12242.512..14285.574 rows=836463 loops=1)
-> Unique (cost=260664.72..260666.68 rows=56 width=197) (actual time=12242.482..12930.285 rows=410604 loops=1)
-> WindowAgg (cost=260664.72..260666.12 rows=56 width=197) (actual time=12242.480..12663.857 rows=410604 loops=1)
-> Sort (cost=260664.72..260664.86 rows=56 width=197) (actual time=12242.469..12384.130 rows=410604 loops=1)
Sort Key: u.external_course_id, u.external_file_id, uv.upload_time, r.generated_time
Sort Method: external sort Disk: 49624kB
-> Merge Left Join (cost=250977.13..260663.09 rows=56 width=197) (actual time=4737.478..5625.614 rows=410604 loops=1)
Merge Cond: (ci.id = r.content_item_id)
-> Sort (cost=8188.89..8189.03 rows=56 width=189) (actual time=2522.484..2621.263 rows=410604 loops=1)
Sort Key: ci.id
Sort Method: quicksort Memory: 84395kB
-> Nested Loop Left Join (cost=158.32..8187.27 rows=56 width=189) (actual time=0.323..2264.234 rows=410604 loops=1)
-> Nested Loop (cost=157.89..8160.12 rows=56 width=160) (actual time=0.316..1473.757 rows=410604 loops=1)
-> Nested Loop (cost=157.46..8059.52 rows=56 width=39) (actual time=0.309..399.637 rows=410640 loops=1)
-> Bitmap Heap Scan on client_courses cc (cost=157.02..6159.96 rows=44 width=15) (actual time=0.267..2.119 rows=3472 loops=1)
Recheck Cond: (client_id = 7)
Filter: ((external_term_id)::text = '1662'::text)
Heap Blocks: exact=78
-> Bitmap Index Scan on client_course_pk (cost=0.00..157.01 rows=3812 width=0) (actual time=0.248..0.248 rows=3472 loops=1)
Index Cond: (client_id = 7)
-> Index Scan using upload_client_course_idx on uploads u (cost=0.43..43.03 rows=14 width=39) (actual time=0.012..0.066 rows=118 loops=3472)
Index Cond: ((client_id = 7) AND ((external_course_id)::text = (cc.external_course_id)::text))
-> Index Scan using upload_version_pk on upload_versions uv (cost=0.43..1.79 rows=1 width=137) (actual time=0.001..0.002 rows=1 loops=410640)
Index Cond: (upload_id = u.id)
-> Index Scan using content_items_pkey on content_items ci (cost=0.43..0.47 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=410604)
Index Cond: (id = uv.content_item_id)
-> Sort (cost=242787.26..247630.80 rows=1937413 width=24) (actual time=2214.939..2518.096 rows=1272688 loops=1)
Sort Key: r.content_item_id
Sort Method: external sort Disk: 71976kB
-> Seq Scan on results r (cost=0.00..40466.13 rows=1937413 width=24) (actual time=0.006..542.936 rows=1937413 loops=1)
-> Index Scan using result_rule_name_idx on result_items ri (cost=0.56..43.83 rows=21 width=37) (actual time=0.001..0.002 rows=2 loops=410604)
Index Cond: (result_id = r.id)
Raw
QP-Report-Normal.txt
Sort (cost=55147.05..55147.55 rows=200 width=219) (actual time=58005.814..58246.514 rows=836463 loops=1)
Sort Key: (row_number() OVER (?))
Sort Method: external sort Disk: 136664kB
-> Nested Loop Left Join (cost=54455.80..55139.41 rows=200 width=219) (actual time=54949.221..56870.507 rows=836463 loops=1)
-> Unique (cost=54455.25..54456.96 rows=49 width=197) (actual time=54949.191..55588.542 rows=410604 loops=1)
-> WindowAgg (cost=54455.25..54456.47 rows=49 width=197) (actual time=54949.189..55342.631 rows=410604 loops=1)
-> Sort (cost=54455.25..54455.37 rows=49 width=197) (actual time=54949.178..55080.868 rows=410604 loops=1)
Sort Key: u.external_course_id, u.external_file_id, uv.upload_time, r.generated_time
Sort Method: external sort Disk: 49624kB
-> Hash Right Join (cost=6721.95..54453.87 rows=49 width=197) (actual time=2529.797..48270.023 rows=410604 loops=1)
Hash Cond: (r.content_item_id = ci.id)
-> Seq Scan on results r (cost=0.00..40466.13 rows=1937413 width=24) (actual time=0.003..462.588 rows=1937413 loops=1)
-> Hash (cost=6721.34..6721.34 rows=49 width=189) (actual time=2526.972..2526.972 rows=410604 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 56564kB
-> Nested Loop Left Join (cost=143.76..6721.34 rows=49 width=189) (actual time=0.314..2307.292 rows=410604 loops=1)
-> Nested Loop (cost=143.34..6697.58 rows=49 width=160) (actual time=0.306..1494.837 rows=410604 loops=1)
-> Nested Loop (cost=142.90..6612.05 rows=49 width=39) (actual time=0.299..404.894 rows=410640 loops=1)
-> Bitmap Heap Scan on client_courses cc (cost=142.47..5918.01 rows=36 width=15) (actual time=0.260..2.109 rows=3472 loops=1)
Recheck Cond: (client_id = 7)
Filter: ((external_term_id)::text = '1662'::text)
Heap Blocks: exact=78
-> Bitmap Index Scan on client_course_pk (cost=0.00..142.46 rows=3472 width=0) (actual time=0.244..0.244 rows=3472 loops=1)
Index Cond: (client_id = 7)
-> Index Scan using upload_client_course_idx on uploads u (cost=0.43..19.23 rows=5 width=39) (actual time=0.011..0.067 rows=118 loops=3472)
Index Cond: ((client_id = 7) AND ((external_course_id)::text = (cc.external_course_id)::text))
-> Index Scan using upload_version_pk on upload_versions uv (cost=0.43..1.74 rows=1 width=137) (actual time=0.002..0.002 rows=1 loops=410640)
Index Cond: (upload_id = u.id)
-> Index Scan using content_items_pkey on content_items ci (cost=0.43..0.47 rows=1 width=37) (actual time=0.001..0.001 rows=1 loops=410604)
Index Cond: (id = uv.content_item_id)
-> Index Scan using result_rule_name_idx on result_items ri (cost=0.56..13.88 rows=4 width=38) (actual time=0.001..0.002 rows=2 loops=410604)
Index Cond: (result_id = r.id)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment