explain.depesz.com

PostgreSQL's explain analyze made readable

Result: izhn : test

Settings
# exclusive inclusive rows x rows loops node
1. 363.078 68,438.986 ↓ 500.0 500 1

Limit (cost=1,140,671.23..1,140,671.24 rows=1 width=36) (actual time=68,075.878..68,438.986 rows=500 loops=1)

2. 89.505 68,075.908 ↓ 500.0 500 1

Sort (cost=1,140,671.23..1,140,671.24 rows=1 width=36) (actual time=68,075.876..68,075.908 rows=500 loops=1)

  • Sort Key: tmp."Order Name", tmp.order_discr
  • Sort Method: top-N heapsort Memory: 65kB
3. 13.634 67,986.403 ↓ 105,389.0 105,389 1

Subquery Scan on tmp (cost=1,140,668.21..1,140,671.22 rows=1 width=36) (actual time=67,926.389..67,986.403 rows=105,389 loops=1)

4. 119.947 67,972.769 ↓ 105,389.0 105,389 1

HashAggregate (cost=1,140,668.21..1,140,671.21 rows=1 width=40) (actual time=67,926.388..67,972.769 rows=105,389 loops=1)

  • Group Key: "*SELECT* 1".name
  • Filter: (CASE WHEN (GROUPING("*SELECT* 1".name) = 0) THEN 1 ELSE 0 END = 1)
5. 12.873 67,852.822 ↑ 19.4 106,869 1

Append (cost=602,825.87..1,109,516.87 rows=2,076,756 width=1,223) (actual time=50,552.067..67,852.822 rows=106,869 loops=1)

6. 55.918 67,839.946 ↑ 19.4 106,869 1

Result (cost=602,825.87..1,099,133.06 rows=2,076,755 width=1,223) (actual time=50,552.066..67,839.946 rows=106,869 loops=1)

7. 11.243 67,784.028 ↑ 19.4 106,869 1

Append (cost=602,825.87..1,067,981.74 rows=2,076,755 width=1,215) (actual time=50,552.062..67,784.028 rows=106,869 loops=1)

8. 61.612 67,772.784 ↑ 19.4 106,869 1

Result (cost=602,825.87..1,057,597.96 rows=2,076,754 width=1,215) (actual time=50,552.061..67,772.784 rows=106,869 loops=1)

9. 11.437 67,711.172 ↑ 19.4 106,869 1

Append (cost=602,825.87..1,031,638.53 rows=2,076,754 width=1,211) (actual time=50,552.058..67,711.172 rows=106,869 loops=1)

10. 0.000 67,699.732 ↑ 19.4 106,869 1

Subquery Scan on *SELECT* 1 (cost=602,825.87..1,021,254.76 rows=2,076,753 width=1,006) (actual time=50,552.057..67,699.732 rows=106,869 loops=1)

11. 3,243.428 68,012.965 ↑ 19.4 106,869 1

Gather (cost=602,825.87..995,295.35 rows=2,076,753 width=1,002) (actual time=50,552.054..68,012.965 rows=106,869 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 14,468.841 64,769.537 ↑ 24.3 35,623 3 / 3

Parallel Hash Join (cost=601,825.87..786,620.05 rows=865,314 width=1,002) (actual time=50,518.586..64,769.537 rows=35,623 loops=3)

  • Hash Cond: (o_3.id = l.shopify_order_id)
13. 3.900 11,341.352 ↑ 1.3 35,130 3 / 3

Parallel Append (cost=662.55..95,640.35 rows=44,686 width=673) (actual time=227.314..11,341.352 rows=35,130 loops=3)

14. 6,246.914 6,364.439 ↑ 1.3 15,000 3 / 3

Parallel Bitmap Heap Scan on shopify_orders_2019_06_09 o_3 (cost=1,000.34..54,760.13 rows=19,135 width=779) (actual time=120.029..6,364.439 rows=15,000 loops=3)

  • Recheck Cond: (shop_id = 80)
  • Filter: ((created_at >= '2019-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2019-09-28 22:59:59'::timestamp without time zone))
  • Heap Blocks: exact=1,900
15. 117.525 117.525 ↑ 1.0 45,000 1 / 3

Bitmap Index Scan on shopify_orders_2019_06_09_on_shop_id (cost=0.00..988.86 rows=45,924 width=0) (actual time=352.576..352.576 rows=45,000 loops=1)

  • Index Cond: (shop_id = 80)
16. 2,484.385 2,560.933 ↓ 1.2 14,824 2 / 3

Parallel Bitmap Heap Scan on shopify_orders_2019_03_06 o_2 (cost=662.55..19,743.84 rows=12,588 width=589) (actual time=116.997..3,841.400 rows=14,824 loops=2)

  • Recheck Cond: (shop_id = 80)
  • Filter: ((created_at >= '2019-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2019-09-28 22:59:59'::timestamp without time zone))
  • Heap Blocks: exact=87
17. 76.548 76.548 ↑ 1.0 29,648 1 / 3

Bitmap Index Scan on shopify_orders_2019_03_06_on_shop_id (cost=0.00..655.00 rows=30,210 width=0) (actual time=229.645..229.645 rows=29,648 loops=1)

  • Index Cond: (shop_id = 80)
18. 1,091.420 1,138.015 ↓ 2.4 17,946 1 / 3

Parallel Bitmap Heap Scan on shopify_orders_2019_09_12 o_4 (cost=399.72..9,724.43 rows=7,489 width=610) (actual time=140.097..3,414.045 rows=17,946 loops=1)

  • Recheck Cond: (shop_id = 80)
  • Filter: ((created_at >= '2019-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2019-09-28 22:59:59'::timestamp without time zone))
  • Heap Blocks: exact=2,093
19. 46.595 46.595 ↑ 1.0 17,946 1 / 3

Bitmap Index Scan on shopify_orders_2019_09_12_on_shop_id (cost=0.00..395.22 rows=17,974 width=0) (actual time=139.786..139.786 rows=17,946 loops=1)

  • Index Cond: (shop_id = 80)
20. 1,243.590 1,274.064 ↓ 2.3 12,795 1 / 3

Parallel Bitmap Heap Scan on shopify_orders_2019_01_03 o_1 (cost=290.22..11,188.52 rows=5,473 width=584) (actual time=93.755..3,822.193 rows=12,795 loops=1)

  • Recheck Cond: (shop_id = 80)
  • Filter: ((created_at >= '2019-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2019-09-28 22:59:59'::timestamp without time zone))
  • Heap Blocks: exact=1,144
21. 30.474 30.474 ↑ 1.0 12,795 1 / 3

Bitmap Index Scan on shopify_orders_2019_01_03_on_shop_id (cost=0.00..286.94 rows=13,136 width=0) (actual time=91.422..91.422 rows=12,795 loops=1)

  • Index Cond: (shop_id = 80)
22. 0.001 0.001 ↓ 0.0 0 1 / 3

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

  • Filter: ((created_at >= '2019-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2019-09-28 22:59:59'::timestamp without time zone) AND (shop_id = 80))
23. 6,819.350 38,959.344 ↑ 1.2 1,788,351 3 / 3

Parallel Hash (cost=497,632.81..497,632.81 rows=2,217,881 width=254) (actual time=38,959.344..38,959.344 rows=1,788,351 loops=3)

  • Buckets: 16,384 Batches: 512 Memory Usage: 3,328kB
24. 32,139.994 32,139.994 ↑ 1.2 1,788,351 3 / 3

Parallel Seq Scan on shopify_line_items l (cost=0.00..497,632.81 rows=2,217,881 width=254) (actual time=9.066..32,139.994 rows=1,788,351 loops=3)

25. 0.001 0.003 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.01 rows=1 width=1,136) (actual time=0.002..0.003 rows=0 loops=1)

26. 0.002 0.002 ↓ 0.0 0 1

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

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

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

28. 0.001 0.001 ↓ 0.0 0 1

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

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

Subquery Scan on *SELECT* 4 (cost=0.00..0.03 rows=1 width=1,199) (actual time=0.003..0.003 rows=0 loops=1)

30. 0.002 0.003 ↓ 0.0 0 1

HashAggregate (cost=0.00..0.01 rows=1 width=1,187) (actual time=0.002..0.003 rows=0 loops=1)

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

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

  • One-Time Filter: false
Planning time : 43.597 ms
Execution time : 68,440.604 ms