Skip to content

Instantly share code, notes, and snippets.

@wlmcewen

wlmcewen/query_1.sql

Created December 28, 2015 16:33
Show Gist options
  • Save wlmcewen/fd1e1967b9614f278e1d to your computer and use it in GitHub Desktop.
Save wlmcewen/fd1e1967b9614f278e1d to your computer and use it in GitHub Desktop.
Download ZIP
API-3685 testing 12/28
Raw
query_1.sql
--- old
SELECT newsfeed_item_id FROM "newsfeed_item" WHERE (newsfeed_item_id IN (select distinct on (source_id) newsfeed_item_id from newsfeed_item where newsfeed_item.subscriber_id='20150623231104072663000000' AND ("notification_sent" = 't' AND "newsfeed_item_approved" = 't') order by source_id, (select event_created_at from newsfeed_item_event where newsfeed_item.newsfeed_item_id = newsfeed_item_event.newsfeed_item_id order by event_created_at desc limit 1) desc nulls last, last_event_at desc nulls last)) ORDER BY (select event_created_at from newsfeed_item_event where newsfeed_item.newsfeed_item_id = newsfeed_item_event.newsfeed_item_id order by event_created_at desc limit 1) desc nulls last, newsfeed_item.last_event_at desc nulls last, newsfeed_item.newsfeed_item_id desc LIMIT 200 OFFSET 0
---new
SELECT newsfeed_item.source_id, MIN(newsfeed_item.newsfeed_item_id) as newsfeed_item_id, MAX(COALESCE(event_created_at, newsfeed_item_created_at)) as last_event_at FROM "newsfeed_item" LEFT OUTER JOIN newsfeed_item_event ON newsfeed_item.newsfeed_item_id = newsfeed_item_event.newsfeed_item_id AND event_created_at >= newsfeed_item.newsfeed_item_created_at WHERE (newsfeed_item.subscriber_id='20150623231104072663000000' AND "notification_sent" = 't' AND "newsfeed_item_approved" = 't') GROUP BY newsfeed_item.source_id ORDER BY last_event_at desc, newsfeed_item.source_id desc LIMIT 200 OFFSET 0;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=55937.91..55937.92 rows=2 width=12) (actual time=1341.832..1341.861 rows=200 loops=1)
-> Sort (cost=55937.91..55937.92 rows=2 width=12) (actual time=1341.831..1341.844 rows=200 loops=1)
Sort Key: ((SubPlan 1)), newsfeed_item.last_event_at, newsfeed_item.newsfeed_item_id
Sort Method: top-N heapsort Memory: 35kB
-> Nested Loop (cost=55906.89..55937.90 rows=2 width=12) (actual time=783.280..1341.457 rows=566 loops=1)
-> Unique (cost=55906.32..55906.33 rows=2 width=4) (actual time=783.183..783.536 rows=566 loops=1)
-> Sort (cost=55906.32..55906.32 rows=2 width=4) (actual time=783.182..783.307 rows=566 loops=1)
Sort Key: "ANY_subquery".newsfeed_item_id
Sort Method: quicksort Memory: 51kB
-> Subquery Scan on "ANY_subquery" (cost=55885.45..55906.31 rows=2 width=4) (actual time=782.616..782.997 rows=566 loops=1)
-> Unique (cost=55885.45..55906.25 rows=2 width=39) (actual time=782.615..782.855 rows=566 loops=1)
-> Sort (cost=55885.45..55895.85 rows=4160 width=39) (actual time=782.614..782.670 rows=566 loops=1)
Sort Key: newsfeed_item_1.source_id, ((SubPlan 2)), newsfeed_item_1.last_event_at
Sort Method: quicksort Memory: 69kB
-> Index Scan using index_newsfeed_item_subscriber_id on newsfeed_item newsfeed_item_1 (cost=0.70..55635.38 rows=4160 width=39) (actual time=0.263..780.580 rows=566 loops=1)
Index Cond: (subscriber_id = '20150623231104072663000000'::bpchar)
Filter: (notification_sent AND newsfeed_item_approved)
Rows Removed by Filter: 42
SubPlan 2
-> Limit (cost=11.14..11.15 rows=1 width=8) (actual time=1.361..1.361 rows=1 loops=566)
-> Sort (cost=11.14..11.16 rows=6 width=8) (actual time=1.360..1.360 rows=1 loops=566)
Sort Key: newsfeed_item_event_1.event_created_at
Sort Method: top-N heapsort Memory: 25kB
-> Index Scan using index_newsfeed_item_id on newsfeed_item_event newsfeed_item_event_1 (cost=0.57..11.11 rows=6 width=8) (actual time=1.015..1.354 rows=2 loops=566)
Index Cond: (newsfeed_item_1.newsfeed_item_id = newsfeed_item_id)
-> Index Scan using newsfeed_item_pkey on newsfeed_item (cost=0.57..4.61 rows=1 width=12) (actual time=0.946..0.962 rows=1 loops=566)
Index Cond: (newsfeed_item_id = "ANY_subquery".newsfeed_item_id)
SubPlan 1
-> Limit (cost=11.14..11.15 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=566)
-> Sort (cost=11.14..11.16 rows=6 width=8) (actual time=0.020..0.020 rows=1 loops=566)
Sort Key: newsfeed_item_event.event_created_at
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_newsfeed_item_id on newsfeed_item_event (cost=0.57..11.11 rows=6 width=8) (actual time=0.013..0.016 rows=2 loops=566)
Index Cond: (newsfeed_item.newsfeed_item_id = newsfeed_item_id)
Total runtime: 1342.020 ms
(35 rows)
---new
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=31532.87..31532.87 rows=2 width=47) (actual time=4557.629..4557.660 rows=200 loops=1)
-> Sort (cost=31532.87..31532.87 rows=2 width=47) (actual time=4557.628..4557.642 rows=200 loops=1)
Sort Key: (max(COALESCE(newsfeed_item_event.event_created_at, newsfeed_item.newsfeed_item_created_at))), newsfeed_item.source_id
Sort Method: top-N heapsort Memory: 40kB
-> HashAggregate (cost=31532.80..31532.86 rows=2 width=47) (actual time=4557.335..4557.447 rows=566 loops=1)
-> Nested Loop Left Join (cost=1.40..31501.60 rows=4160 width=47) (actual time=0.167..4555.133 rows=1269 loops=1)
-> Index Scan using index_newsfeed_item_subscriber_id on newsfeed_item (cost=0.70..9271.18 rows=4160 width=39) (actual time=0.086..197.814 rows=566 loops=1)
Index Cond: (subscriber_id = '20150623231104072663000000'::bpchar)
Filter: (notification_sent AND newsfeed_item_approved)
Rows Removed by Filter: 42
-> Index Only Scan using unique_newsfeed_item_event_key on newsfeed_item_event (cost=0.70..5.28 rows=2 width=12) (actual time=7.639..7.696 rows=2 loops=566)
Index Cond: ((newsfeed_item_id = newsfeed_item.newsfeed_item_id) AND (event_created_at >= newsfeed_item.newsfeed_item_created_at))
Heap Fetches: 603
Total runtime: 4557.728 ms
(14 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=31532.87..31532.87 rows=2 width=47) (actual time=16.202..16.235 rows=200 loops=1)
-> Sort (cost=31532.87..31532.87 rows=2 width=47) (actual time=16.202..16.213 rows=200 loops=1)
Sort Key: (max(COALESCE(newsfeed_item_event.event_created_at, newsfeed_item.newsfeed_item_created_at))), newsfeed_item.source_id
Sort Method: top-N heapsort Memory: 40kB
-> HashAggregate (cost=31532.80..31532.86 rows=2 width=47) (actual time=15.957..16.038 rows=566 loops=1)
-> Nested Loop Left Join (cost=1.40..31501.60 rows=4160 width=47) (actual time=0.108..14.832 rows=1269 loops=1)
-> Index Scan using index_newsfeed_item_subscriber_id on newsfeed_item (cost=0.70..9271.18 rows=4160 width=39) (actual time=0.063..1.165 rows=566 loops=1)
Index Cond: (subscriber_id = '20150623231104072663000000'::bpchar)
Filter: (notification_sent AND newsfeed_item_approved)
Rows Removed by Filter: 42
-> Index Only Scan using unique_newsfeed_item_event_key on newsfeed_item_event (cost=0.70..5.28 rows=2 width=12) (actual time=0.021..0.023 rows=2 loops=566)
Index Cond: ((newsfeed_item_id = newsfeed_item.newsfeed_item_id) AND (event_created_at >= newsfeed_item.newsfeed_item_created_at))
Heap Fetches: 603
Total runtime: 16.310 ms
(14 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment