explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cmam

Settings
# exclusive inclusive rows x rows loops node
1. 2,133.523 2,133.523 ↓ 733.0 733 1

CTE Scan on snapshots (cost=20,837.15..20,837.17 rows=1 width=64) (actual time=2,066.500..2,133.523 rows=733 loops=1)

2.          

CTE s

3. 172.222 1,273.959 ↑ 1.0 9,944 1

Limit (cost=10,639.74..14,510.16 rows=9,944 width=114) (actual time=182.553..1,273.959 rows=9,944 loops=1)

4. 826.306 1,101.737 ↑ 1.0 9,944 1

Hash Join (cost=10,639.74..14,510.16 rows=9,944 width=114) (actual time=182.526..1,101.737 rows=9,944 loops=1)

  • Hash Cond: (i.url = u.id)
5. 93.795 93.795 ↑ 1.0 9,944 1

Seq Scan on overstock_items i (cost=0.00..1,358.30 rows=9,944 width=118) (actual time=0.038..93.795 rows=9,944 loops=1)

  • Filter: (active AND (amazon_account = 'A1B43WMIJ58I3U'::text))
6. 82.801 181.636 ↑ 1.6 9,069 1

Hash (cost=10,459.33..10,459.33 rows=14,433 width=70) (actual time=181.629..181.636 rows=9,069 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1074kB
7. 98.835 98.835 ↑ 1.6 9,069 1

Seq Scan on overstock_urls u (cost=0.00..10,459.33 rows=14,433 width=70) (actual time=0.026..98.835 rows=9,069 loops=1)

8.          

CTE max_times

9. 73.692 2,072.184 ↓ 1.5 733 1

HashAggregate (cost=2,074.51..2,079.53 rows=502 width=19) (actual time=2,066.344..2,072.184 rows=733 loops=1)

  • Group Key: amazon_notifications.asin
10. 176.746 1,998.492 ↓ 3.5 1,782 1

Nested Loop (cost=224.31..1,945.24 rows=502 width=1,418) (actual time=1,516.942..1,998.492 rows=1,782 loops=1)

11. 172.336 1,604.012 ↓ 49.5 9,897 1

HashAggregate (cost=223.74..225.74 rows=200 width=32) (actual time=1,516.510..1,604.012 rows=9,897 loops=1)

  • Group Key: s.asin
12. 1,431.676 1,431.676 ↑ 1.0 9,944 1

CTE Scan on s (cost=0.00..198.88 rows=9,944 width=32) (actual time=182.598..1,431.676 rows=9,944 loops=1)

13. 217.734 217.734 ↓ 0.0 0 9,897

Index Scan using amazon_notfications_asin_added_ids on amazon_notifications (cost=0.57..8.59 rows=1 width=1,418) (actual time=0.019..0.022 rows=0 loops=9,897)

  • Index Cond: ((asin = s.asin) AND (added_at > (timezone('utc'::text, now()) - '01:00:00'::interval)))
14.          

CTE snapshots

15. 22.000 2,120.949 ↓ 733.0 733 1

Nested Loop (cost=0.44..4,247.47 rows=1 width=1,418) (actual time=2,066.459..2,120.949 rows=733 loops=1)

16. 2,083.556 2,083.556 ↓ 1.5 733 1

CTE Scan on max_times t (cost=0.00..10.04 rows=502 width=40) (actual time=2,066.383..2,083.556 rows=733 loops=1)

17. 15.393 15.393 ↑ 1.0 1 733

Index Scan using amazon_notifications_offerchangetime_idx on amazon_notifications n (cost=0.44..8.43 rows=1 width=1,418) (actual time=0.014..0.021 rows=1 loops=733)

  • Index Cond: (to_timestamp((details #>> '{Notification,NotificationPayload,AnyOfferChangedNotification,OfferChangeTrigger,TimeOfOfferChange}'::text[])) = t.max_time)
  • Filter: (t.asin = asin)
  • Rows Removed by Filter: 0
Planning time : 1.635 ms
Execution time : 2,145.546 ms