explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uNQK : daily kpi #1

Settings
# exclusive inclusive rows x rows loops node
1. 1,710.991 194,269.576 ↑ 1.0 1 1

Aggregate (cost=336,221.83..336,221.84 rows=1 width=120) (actual time=194,269.576..194,269.576 rows=1 loops=1)

  • Filter: (count(1) > 0)
2. 251.934 192,558.585 ↓ 382,557.3 1,147,672 1

Nested Loop Left Join (cost=327,677.10..336,221.64 rows=3 width=50) (actual time=4,744.074..192,558.585 rows=1,147,672 loops=1)

3. 349.064 187,715.963 ↓ 382,557.3 1,147,672 1

Nested Loop (cost=327,676.68..336,220.23 rows=3 width=44) (actual time=4,744.033..187,715.963 rows=1,147,672 loops=1)

4. 6,009.390 186,340.899 ↓ 51,300.0 51,300 1

Nested Loop (cost=327,676.25..336,198.16 rows=1 width=40) (actual time=4,744.023..186,340.899 rows=51,300 loops=1)

  • Join Filter: (NOT (venues.linked_venue_id IS DISTINCT FROM venues_2.linked_venue_id))
  • Rows Removed by Join Filter: 83,978,100
5. 164,169.121 167,711.709 ↓ 51,300.0 51,300 1

Hash Join (cost=194,725.43..203,185.78 rows=1 width=32) (actual time=3,461.447..167,711.709 rows=51,300 loops=1)

  • Hash Cond: (order_requests.supplier_store_id = supplier_stores.id)
  • Join Filter: ((NOT (venues.linked_venue_id IS DISTINCT FROM venues_1.linked_venue_id)) AND (NOT (date_trunc('DAY'::text, order_requests.created_at) IS DISTINCT FROM (date_trunc('DAY'::text, order_requests_1.created_at)))))
  • Rows Removed by Join Filter: 1,649,527,551
6. 26.601 89.379 ↓ 248.2 51,370 1

Hash Join (cost=194.87..5,045.97 rows=207 width=32) (actual time=2.830..89.379 rows=51,370 loops=1)

  • Hash Cond: (order_requests.venue_id = venues.id)
7. 60.393 60.393 ↓ 125.4 51,797 1

Seq Scan on order_requests (cost=0.00..4,847.49 rows=413 width=32) (actual time=0.405..60.393 rows=51,797 loops=1)

  • Filter: ((date_part('year'::text, created_at))::integer = (date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone))::integer)
  • Rows Removed by Filter: 32,682
8. 0.618 2.385 ↓ 1.8 3,665 1

Hash (cost=169.59..169.59 rows=2,022 width=16) (actual time=2.384..2.385 rows=3,665 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 204kB
9. 1.767 1.767 ↓ 1.8 3,665 1

Seq Scan on venues (cost=0.00..169.59 rows=2,022 width=16) (actual time=0.013..1.767 rows=3,665 loops=1)

  • Filter: CASE WHEN ((shop_type)::text = ANY ('{Großhändler,Hersteller,Intern}'::text[])) THEN false ELSE true END
  • Rows Removed by Filter: 392
10. 18.585 3,453.209 ↑ 1.4 67,546 1

Hash (cost=192,545.05..192,545.05 rows=97,721 width=40) (actual time=3,453.209..3,453.209 rows=67,546 loops=1)

  • Buckets: 65,536 (originally 65536) Batches: 16 (originally 2) Memory Usage: 3,585kB
11. 14.242 3,434.624 ↑ 1.4 67,546 1

Hash Join (cost=176,718.11..192,545.05 rows=97,721 width=40) (actual time=2,749.638..3,434.624 rows=67,546 loops=1)

  • Hash Cond: (supplier_stores_1.id = supplier_stores.id)
12. 273.903 3,420.173 ↑ 5.7 67,546 1

GroupAggregate (cost=176,702.14..186,282.62 rows=383,219 width=32) (actual time=2,749.404..3,420.173 rows=67,546 loops=1)

  • Group Key: (date_trunc('DAY'::text, order_requests_1.created_at)), supplier_stores_1.id, venues_1.linked_venue_id
13. 1,792.792 3,146.270 ↓ 5.1 1,948,814 1

Sort (cost=176,702.14..177,660.19 rows=383,219 width=32) (actual time=2,749.392..3,146.270 rows=1,948,814 loops=1)

  • Sort Key: (date_trunc('DAY'::text, order_requests_1.created_at)), supplier_stores_1.id, venues_1.linked_venue_id
  • Sort Method: external merge Disk: 79,984kB
14. 446.696 1,353.478 ↓ 5.1 1,948,814 1

Nested Loop (cost=211.26..131,992.78 rows=383,219 width=32) (actual time=2.282..1,353.478 rows=1,948,814 loops=1)

15. 36.842 79.892 ↓ 5.2 82,689 1

Hash Join (cost=210.83..4,307.04 rows=15,795 width=32) (actual time=2.219..79.892 rows=82,689 loops=1)

  • Hash Cond: (order_requests_1.venue_id = venues_1.id)
16. 25.247 40.989 ↓ 2.6 83,662 1

Hash Join (cost=15.96..3,835.79 rows=31,582 width=32) (actual time=0.152..40.989 rows=83,662 loops=1)

  • Hash Cond: (order_requests_1.supplier_store_id = supplier_stores_1.id)
17. 15.624 15.624 ↓ 1.0 84,479 1

Seq Scan on order_requests order_requests_1 (cost=0.00..3,195.83 rows=82,583 width=32) (actual time=0.006..15.624 rows=84,479 loops=1)

18. 0.009 0.118 ↓ 1.0 52 1

Hash (cost=15.33..15.33 rows=51 width=8) (actual time=0.117..0.118 rows=52 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
19. 0.031 0.109 ↓ 1.0 52 1

Hash Join (cost=9.00..15.33 rows=51 width=8) (actual time=0.069..0.109 rows=52 loops=1)

  • Hash Cond: (supplier_stores_1.supplier_id = suppliers_1.id)
20. 0.032 0.032 ↓ 1.0 129 1

Seq Scan on supplier_stores supplier_stores_1 (cost=0.00..5.34 rows=127 width=16) (actual time=0.003..0.032 rows=129 loops=1)

  • Filter: (NOT demo)
  • Rows Removed by Filter: 7
21. 0.006 0.046 ↑ 1.0 46 1

Hash (cost=8.43..8.43 rows=46 width=8) (actual time=0.045..0.046 rows=46 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
22. 0.040 0.040 ↑ 1.0 46 1

Seq Scan on suppliers suppliers_1 (cost=0.00..8.43 rows=46 width=8) (actual time=0.013..0.040 rows=46 loops=1)

  • Filter: ((status)::text = 'live'::text)
  • Rows Removed by Filter: 68
23. 0.594 2.061 ↓ 1.8 3,665 1

Hash (cost=169.59..169.59 rows=2,022 width=16) (actual time=2.061..2.061 rows=3,665 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 204kB
24. 1.467 1.467 ↓ 1.8 3,665 1

Seq Scan on venues venues_1 (cost=0.00..169.59 rows=2,022 width=16) (actual time=0.014..1.467 rows=3,665 loops=1)

  • Filter: CASE WHEN ((shop_type)::text = ANY ('{Großhändler,Hersteller,Intern}'::text[])) THEN false ELSE true END
  • Rows Removed by Filter: 392
25. 826.890 826.890 ↑ 2.8 24 82,689

Index Only Scan using index_order_request_items_on_order_request_id on order_request_items order_request_items_1 (cost=0.43..7.34 rows=68 width=8) (actual time=0.003..0.010 rows=24 loops=82,689)

  • Index Cond: (order_request_id = order_requests_1.id)
  • Heap Fetches: 1,948,903
26. 0.010 0.209 ↓ 1.0 52 1

Hash (cost=15.33..15.33 rows=51 width=8) (actual time=0.209..0.209 rows=52 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
27. 0.046 0.199 ↓ 1.0 52 1

Hash Join (cost=9.00..15.33 rows=51 width=8) (actual time=0.146..0.199 rows=52 loops=1)

  • Hash Cond: (supplier_stores.supplier_id = suppliers.id)
28. 0.050 0.050 ↓ 1.0 129 1

Seq Scan on supplier_stores (cost=0.00..5.34 rows=127 width=16) (actual time=0.011..0.050 rows=129 loops=1)

  • Filter: (NOT demo)
  • Rows Removed by Filter: 7
29. 0.007 0.103 ↑ 1.0 46 1

Hash (cost=8.43..8.43 rows=46 width=8) (actual time=0.103..0.103 rows=46 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
30. 0.096 0.096 ↑ 1.0 46 1

Seq Scan on suppliers (cost=0.00..8.43 rows=46 width=8) (actual time=0.012..0.096 rows=46 loops=1)

  • Filter: ((status)::text = 'live'::text)
  • Rows Removed by Filter: 68
31. 11,644.122 12,619.800 ↑ 1.2 1,638 51,300

HashAggregate (cost=132,950.82..132,969.76 rows=1,894 width=16) (actual time=0.026..0.246 rows=1,638 loops=51,300)

  • Group Key: venues_2.linked_venue_id
32. 245.889 975.678 ↓ 5.1 1,948,814 1

Nested Loop (cost=211.26..131,034.73 rows=383,219 width=16) (actual time=2.125..975.678 rows=1,948,814 loops=1)

33. 30.635 68.277 ↓ 5.2 82,689 1

Hash Join (cost=210.83..4,307.04 rows=15,795 width=24) (actual time=2.099..68.277 rows=82,689 loops=1)

  • Hash Cond: (order_requests_2.venue_id = venues_2.id)
34. 22.015 35.780 ↓ 2.6 83,662 1

Hash Join (cost=15.96..3,835.79 rows=31,582 width=24) (actual time=0.228..35.780 rows=83,662 loops=1)

  • Hash Cond: (order_requests_2.supplier_store_id = supplier_stores_2.id)
35. 13.596 13.596 ↓ 1.0 84,479 1

Seq Scan on order_requests order_requests_2 (cost=0.00..3,195.83 rows=82,583 width=32) (actual time=0.010..13.596 rows=84,479 loops=1)

36. 0.008 0.169 ↓ 1.0 52 1

Hash (cost=15.33..15.33 rows=51 width=8) (actual time=0.168..0.169 rows=52 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
37. 0.032 0.161 ↓ 1.0 52 1

Hash Join (cost=9.00..15.33 rows=51 width=8) (actual time=0.113..0.161 rows=52 loops=1)

  • Hash Cond: (supplier_stores_2.supplier_id = suppliers_2.id)
38. 0.048 0.048 ↓ 1.0 129 1

Seq Scan on supplier_stores supplier_stores_2 (cost=0.00..5.34 rows=127 width=16) (actual time=0.012..0.048 rows=129 loops=1)

  • Filter: (NOT demo)
  • Rows Removed by Filter: 7
39. 0.007 0.081 ↑ 1.0 46 1

Hash (cost=8.43..8.43 rows=46 width=8) (actual time=0.081..0.081 rows=46 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
40. 0.074 0.074 ↑ 1.0 46 1

Seq Scan on suppliers suppliers_2 (cost=0.00..8.43 rows=46 width=8) (actual time=0.036..0.074 rows=46 loops=1)

  • Filter: ((status)::text = 'live'::text)
  • Rows Removed by Filter: 68
41. 0.428 1.862 ↓ 1.8 3,665 1

Hash (cost=169.59..169.59 rows=2,022 width=16) (actual time=1.862..1.862 rows=3,665 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 204kB
42. 1.434 1.434 ↓ 1.8 3,665 1

Seq Scan on venues venues_2 (cost=0.00..169.59 rows=2,022 width=16) (actual time=0.013..1.434 rows=3,665 loops=1)

  • Filter: CASE WHEN ((shop_type)::text = ANY ('{Großhändler,Hersteller,Intern}'::text[])) THEN false ELSE true END
  • Rows Removed by Filter: 392
43. 661.512 661.512 ↑ 2.8 24 82,689

Index Only Scan using index_order_request_items_on_order_request_id on order_request_items order_request_items_2 (cost=0.43..7.34 rows=68 width=8) (actual time=0.003..0.008 rows=24 loops=82,689)

  • Index Cond: (order_request_id = order_requests_2.id)
  • Heap Fetches: 1,948,903
44. 1,026.000 1,026.000 ↑ 3.1 22 51,300

Index Scan using index_order_request_items_on_order_request_id on order_request_items (cost=0.43..21.39 rows=68 width=20) (actual time=0.013..0.020 rows=22 loops=51,300)

  • Index Cond: (order_request_id = order_requests.id)
45. 4,590.688 4,590.688 ↑ 1.0 1 1,147,672

Index Scan using stock_items_pkey on stock_items (cost=0.42..0.46 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=1,147,672)

  • Index Cond: (order_request_items.stock_item_id = id)
Planning time : 5.943 ms
Execution time : 194,305.538 ms