Skip to content

Instantly share code, notes, and snippets.

@jvanasco

jvanasco/sql explains Secret

Last active December 23, 2015 05:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jvanasco/bc1dd38ca06e52c9a090 to your computer and use it in GitHub Desktop.
Save jvanasco/bc1dd38ca06e52c9a090 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
sql explains
EXPLAIN ANALYZE SELECT attachment.*
FROM attachment
JOIN message_2_attachment ON attachment.id = message_2_attachment.attachment_id
JOIN message ON message_2_attachment.message_id = message.id
JOIN mailing ON mailing.message_id = message.id
WHERE mailing.useraccount_id = 10083
ORDER BY mailing.event_timestamp desc limit 10 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=159374.75..159374.78 rows=10 width=3201) (actual time=31050.005..31050.009 rows=10 loops=1)
-> Sort (cost=159374.75..159485.93 rows=44473 width=3201) (actual time=31050.002..31050.003 rows=10 loops=1)
Sort Key: mailing.event_timestamp
Sort Method: top-N heapsort Memory: 28kB
-> Hash Join (cost=96588.85..158413.71 rows=44473 width=3201) (actual time=22590.630..30761.213 rows=44292 loops=1)
Hash Cond: (message_2_attachment.attachment_id = attachment.id)
-> Hash Join (cost=11595.74..18165.10 rows=44473 width=16) (actual time=555.996..1258.691 rows=44292 loops=1)
Hash Cond: (message_2_attachment.message_id = message.id)
-> Seq Scan on message_2_attachment (cost=0.00..2842.32 rows=163732 width=16) (actual time=0.075..99.709 rows=163732 loops=1)
-> Hash (cost=10809.83..10809.83 rows=42793 width=24) (actual time=555.592..555.592 rows=42717 loops=1)
Buckets: 2048 Batches: 4 Memory Usage: 591kB
-> Hash Join (cost=5667.59..10809.83 rows=42793 width=24) (actual time=203.979..515.778 rows=42717 loops=1)
Hash Cond: (mailing.message_id = message.id)
-> Seq Scan on mailing (cost=0.00..3303.88 rows=42793 width=16) (actual time=0.015..91.128 rows=42717 loops=1)
Filter: (useraccount_id = 10083)
Rows Removed by Filter: 115833
-> Hash (cost=3074.26..3074.26 rows=158026 width=8) (actual time=203.477..203.477 rows=158026 loops=1)
Buckets: 4096 Batches: 8 Memory Usage: 779kB
-> Seq Scan on message (cost=0.00..3074.26 rows=158026 width=8) (actual time=0.031..86.935 rows=158026 loops=1)
-> Hash (cost=29063.38..29063.38 rows=137738 width=3193) (actual time=21764.201..21764.201 rows=137738 loops=1)
Buckets: 1024 Batches: 512 Memory Usage: 448kB
-> Seq Scan on attachment (cost=0.00..29063.38 rows=137738 width=3193) (actual time=0.067..774.379 rows=137738 loops=1)
Total runtime: 31050.543 ms
(23 rows)
Time: 31054.371 ms
===================================================================================================
EXPLAIN ANALYZE SELECT * FROM attachment WHERE id IN ( 188732 , 188733 , 188734 , 188735 , 188736 , 188737 , 172788 , 188738 , 188739 , 188740 );
--------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Index Scan using attachment_pkey on attachment (cost=0.01..81.43 rows=10 width=3193) (actual time=32.884..33.662 rows=10 loops=1)
Index Cond: (id = ANY ('{188732,188733,188734,188735,188736,188737,172788,188738,188739,188740}'::integer[]))
Total runtime: 34.210 ms
(3 rows)
===================================================================================================
EXPLAIN ANALYZE SELECT message_2_attachment.attachment_id
FROM message_2_attachment
JOIN message ON message.id = message_2_attachment.message_id
JOIN mailing ON mailing.message_id = message.id
WHERE mailing.useraccount_id = 10083
ORDER BY mailing.event_timestamp desc limit 10 ;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=19123.22..19123.25 rows=10 width=16) (actual time=1155.872..1155.878 rows=10 loops=1)
-> Sort (cost=19123.22..19234.07 rows=44338 width=16) (actual time=1155.870..1155.872 rows=10 loops=1)
Sort Key: mailing.event_timestamp
Sort Method: top-N heapsort Memory: 25kB
-> Hash Join (cost=11595.74..18165.10 rows=44338 width=16) (actual time=710.799..1128.765 rows=44292 loops=1)
Hash Cond: (message_2_attachment.message_id = message.id)
-> Seq Scan on message_2_attachment (cost=0.00..2842.32 rows=163732 width=16) (actual time=1.269..137.855 rows=163732 loops=1)
-> Hash (cost=10809.83..10809.83 rows=42793 width=24) (actual time=709.299..709.299 rows=42717 loops=1)
Buckets: 2048 Batches: 4 Memory Usage: 591kB
-> Hash Join (cost=5667.59..10809.83 rows=42793 width=24) (actual time=343.190..668.594 rows=42717 loops=1)
Hash Cond: (mailing.message_id = message.id)
-> Seq Scan on mailing (cost=0.00..3303.88 rows=42793 width=16) (actual time=0.018..104.413 rows=42717 loops=1)
Filter: (useraccount_id = 10083)
Rows Removed by Filter: 115833
-> Hash (cost=3074.26..3074.26 rows=158026 width=8) (actual time=342.689..342.689 rows=158026 loops=1)
Buckets: 4096 Batches: 8 Memory Usage: 779kB
-> Seq Scan on message (cost=0.00..3074.26 rows=158026 width=8) (actual time=0.216..223.336 rows=158026 loops=1)
Total runtime: 1156.070 ms
(18 rows)
===================================================================================================
EXPLAIN ANALYZE SELECT attachment.*
FROM
mailing
INNER JOIN
message on message.id = mailing.message_id
INNER JOIN
message_2_attachment on message.id = message_2_attachment.message_id
INNER JOIN
attachment on message_2_attachment.attachment_id = attachment.id
where mailing.useraccount_id = 10083
order by mailing.event_timestamp desc limit 10
;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=158549.32..158549.34 rows=10 width=3201) (actual time=151872.392..151872.396 rows=10 loops=1)
-> Sort (cost=158549.32..158660.16 rows=44338 width=3201) (actual time=151872.389..151872.391 rows=10 loops=1)
Sort Key: mailing.event_timestamp
Sort Method: top-N heapsort Memory: 28kB
-> Hash Join (cost=96588.85..157591.19 rows=44338 width=3201) (actual time=33091.827..151607.408 rows=44292 loops=1)
Hash Cond: (message_2_attachment.attachment_id = attachment.id)
-> Hash Join (cost=11595.74..17346.44 rows=44338 width=16) (actual time=641.292..1719.591 rows=44292 loops=1)
Hash Cond: (message_2_attachment.message_id = message.id)
-> Seq Scan on message_2_attachment (cost=0.00..2842.32 rows=163732 width=16) (actual time=0.141..573.434 rows=163732 loops=1)
-> Hash (cost=10809.83..10809.83 rows=42793 width=24) (actual time=640.941..640.941 rows=42717 loops=1)
Buckets: 2048 Batches: 4 Memory Usage: 591kB
-> Hash Join (cost=5667.59..10809.83 rows=42793 width=24) (actual time=275.077..600.823 rows=42717 loops=1)
Hash Cond: (mailing.message_id = message.id)
-> Seq Scan on mailing (cost=0.00..3303.88 rows=42793 width=16) (actual time=0.168..109.343 rows=42717 loops=1)
Filter: (useraccount_id = 10083)
Rows Removed by Filter: 115833
-> Hash (cost=3074.26..3074.26 rows=158026 width=8) (actual time=274.390..274.390 rows=158026 loops=1)
Buckets: 4096 Batches: 8 Memory Usage: 779kB
-> Seq Scan on message (cost=0.00..3074.26 rows=158026 width=8) (actual time=0.042..128.681 rows=158026 loops=1)
-> Hash (cost=29063.38..29063.38 rows=137738 width=3193) (actual time=31918.079..31918.079 rows=137738 loops=1)
Buckets: 1024 Batches: 512 Memory Usage: 448kB
-> Seq Scan on attachment (cost=0.00..29063.38 rows=137738 width=3193) (actual time=0.167..1258.053 rows=137738 loops=1)
Total runtime: 151872.970 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment