explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NAO6

Settings
# exclusive inclusive rows x rows loops node
1. 0.058 6,243.170 ↑ 1.0 500 1

Subquery Scan on final_tmp (cost=33,561,246.84..33,561,253.09 rows=500 width=237) (actual time=6,243.047..6,243.170 rows=500 loops=1)

2. 0.046 6,243.112 ↑ 1.0 500 1

Limit (cost=33,561,246.84..33,561,248.09 rows=500 width=241) (actual time=6,243.045..6,243.112 rows=500 loops=1)

3. 2.443 6,243.066 ↑ 338.2 500 1

Sort (cost=33,561,246.84..33,561,669.60 rows=169,103 width=241) (actual time=6,243.045..6,243.066 rows=500 loops=1)

  • Sort Key: tmp."Product Type", tmp.order_discr
  • Sort Method: top-N heapsort Memory: 124kB
4. 0.833 6,240.623 ↑ 19.7 8,564 1

Subquery Scan on tmp (cost=23,484,832.47..33,552,820.62 rows=169,103 width=241) (actual time=6,170.669..6,240.623 rows=8,564 loops=1)

5. 18.488 6,239.790 ↑ 19.7 8,564 1

GroupAggregate (cost=23,484,832.47..33,551,129.59 rows=169,103 width=255) (actual time=6,170.668..6,239.790 rows=8,564 loops=1)

  • Group Key: p.product_type, (to_char(timezone('Europe/Amsterdam'::text, timezone('utc'::text, sv.created_at)), 'MM-DD-YYYY'::text)), scd.user_agent, (sales_location_name(sv.id))
  • Filter: (CASE WHEN ((GROUPING(p.product_type) = 0) AND (GROUPING((to_char(timezone('Europe/Amsterdam'::text, timezone('utc'::text, sv.created_at)), 'MM-DD-YYYY'::text))) = 0) AND (GROUPING(scd.user_agent) = 0) AND (GROUPING((sales_location_name(sv.id))) = 0)) THEN 1 ELSE 0 END = 1)
6. 222.030 6,221.302 ↑ 370.1 91,376 1

Sort (cost=23,484,832.47..23,569,384.13 rows=33,820,662 width=219) (actual time=6,170.654..6,221.302 rows=91,376 loops=1)

  • Sort Key: p.product_type, (to_char(timezone('Europe/Amsterdam'::text, timezone('utc'::text, sv.created_at)), 'MM-DD-YYYY'::text)), scd.user_agent, (sales_location_name(sv.id))
  • Sort Method: external merge Disk: 14,432kB
7. 4,537.134 5,999.272 ↑ 370.1 91,376 1

Nested Loop Left Join (cost=194,773.56..13,112,113.68 rows=33,820,662 width=219) (actual time=1,106.026..5,999.272 rows=91,376 loops=1)

8. 182.796 1,353.318 ↑ 112.0 10,882 1

Hash Left Join (cost=194,773.00..1,320,271.54 rows=1,219,135 width=30) (actual time=1,105.721..1,353.318 rows=10,882 loops=1)

  • Hash Cond: (sv.shopify_product_id = p.id)
9. 1.787 66.470 ↑ 112.0 10,882 1

Subquery Scan on sv (cost=0.56..1,095,480.87 rows=1,219,135 width=28) (actual time=0.067..66.470 rows=10,882 loops=1)

10. 1.045 64.683 ↑ 112.0 10,882 1

Append (cost=0.56..1,083,289.52 rows=1,219,135 width=1,259) (actual time=0.066..64.683 rows=10,882 loops=1)

11. 3.444 63.637 ↑ 112.0 10,882 1

Result (cost=0.56..1,077,193.80 rows=1,219,134 width=1,259) (actual time=0.066..63.637 rows=10,882 loops=1)

12. 0.894 60.193 ↑ 112.0 10,882 1

Append (cost=0.56..1,058,906.79 rows=1,219,134 width=1,251) (actual time=0.063..60.193 rows=10,882 loops=1)

13. 3.360 59.296 ↑ 112.0 10,882 1

Result (cost=0.56..1,052,811.05 rows=1,219,132 width=1,251) (actual time=0.063..59.296 rows=10,882 loops=1)

14. 0.876 55.936 ↑ 112.0 10,882 1

Append (cost=0.56..1,037,571.90 rows=1,219,132 width=1,247) (actual time=0.060..55.936 rows=10,882 loops=1)

15. 3.637 55.059 ↑ 112.0 10,882 1

Subquery Scan on *SELECT* 1 (cost=0.56..1,031,476.22 rows=1,219,131 width=969) (actual time=0.060..55.059 rows=10,882 loops=1)

16. 2.375 51.422 ↑ 112.0 10,882 1

Nested Loop (cost=0.56..1,016,237.08 rows=1,219,131 width=965) (actual time=0.057..51.422 rows=10,882 loops=1)

17. 0.736 14.007 ↑ 1.1 8,760 1

Append (cost=0.00..8,166.54 rows=9,495 width=610) (actual time=0.034..14.007 rows=8,760 loops=1)

18. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on shopify_orders o (cost=0.00..0.00 rows=1 width=644) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((test IS NOT TRUE) AND (created_at >= '2020-03-23 23:00:00'::timestamp without time zone) AND (created_at <= '2020-06-21 21:59:59'::timestamp without time zone) AND (shop_id = 485))
19. 10.723 10.723 ↑ 1.3 7,215 1

Index Scan using shopify_orders_2020_03_06_ix_shop_id_created_at on shopify_orders_2020_03_06 o_1 (cost=0.56..7,674.54 rows=9,096 width=611) (actual time=0.032..10.723 rows=7,215 loops=1)

  • Index Cond: ((shop_id = 485) AND (created_at >= '2020-03-23 23:00:00'::timestamp without time zone) AND (created_at <= '2020-06-21 21:59:59'::timestamp without time zone))
  • Filter: (test IS NOT TRUE)
20. 2.546 2.546 ↓ 3.9 1,545 1

Index Scan using shopify_orders_shopify_orders_2020_06_09_shop_id on shopify_orders_2020_06_09 o_2 (cost=0.43..444.53 rows=398 width=594) (actual time=0.012..2.546 rows=1,545 loops=1)

  • Index Cond: (shop_id = 485)
  • Filter: ((test IS NOT TRUE) AND (created_at >= '2020-03-23 23:00:00'::timestamp without time zone) AND (created_at <= '2020-06-21 21:59:59'::timestamp without time zone))
  • Rows Removed by Filter: 182
21. 35.040 35.040 ↑ 128.0 1 8,760

Index Scan using index_shopify_line_items_on_shopify_order_id_2 on shopify_line_items l (cost=0.56..103.28 rows=128 width=219) (actual time=0.003..0.004 rows=1 loops=8,760)

  • Index Cond: (shopify_order_id = o.id)
22. 0.000 0.001 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.02 rows=1 width=1,069) (actual time=0.001..0.001 rows=0 loops=1)

23. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=1,041) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
24. 0.000 0.001 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=0.00..0.02 rows=1 width=1,157) (actual time=0.001..0.001 rows=0 loops=1)

25. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=1,125) (actual time=0.000..0.001 rows=0 loops=1)

  • One-Time Filter: false
26. 0.001 0.002 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0.00..0.05 rows=1 width=1,157) (actual time=0.002..0.002 rows=0 loops=1)

27. 0.000 0.001 ↓ 0.0 0 1

HashAggregate (cost=0.00..0.01 rows=1 width=1,117) (actual time=0.001..0.001 rows=0 loops=1)

  • Group Key: id
28. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=115) (actual time=0.000..0.001 rows=0 loops=1)

  • One-Time Filter: false
29. 0.000 0.001 ↓ 0.0 0 1

Subquery Scan on *SELECT* 5 (cost=0.00..0.05 rows=1 width=1,164) (actual time=0.001..0.001 rows=0 loops=1)

30. 0.001 0.001 ↓ 0.0 0 1

HashAggregate (cost=0.00..0.01 rows=1 width=1,112) (actual time=0.001..0.001 rows=0 loops=1)

  • Group Key: id
31. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=105) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
32. 350.366 1,104.052 ↑ 1.0 1,698,349 1

Hash (cost=162,971.08..162,971.08 rows=1,732,108 width=18) (actual time=1,104.052..1,104.052 rows=1,698,349 loops=1)

  • Buckets: 65,536 Batches: 32 Memory Usage: 3,355kB
33. 753.686 753.686 ↑ 1.0 1,698,349 1

Seq Scan on shopify_products p (cost=0.00..162,971.08 rows=1,732,108 width=18) (actual time=0.012..753.686 rows=1,698,349 loops=1)

34. 108.820 108.820 ↑ 3.5 8 10,882

Index Scan using index_shopify_client_details_on_shopify_order_id on shopify_client_details scd (cost=0.56..2.25 rows=28 width=149) (actual time=0.004..0.010 rows=8 loops=10,882)

  • Index Cond: (shopify_order_id = sv.id)
Planning time : 6.648 ms
Execution time : 6,245.771 ms