explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6IKF : test

Settings
# exclusive inclusive rows x rows loops node
1. 756.364 55,321.922 ↓ 500.0 500 1

Limit (cost=1,192,293.22..1,192,293.23 rows=1 width=36) (actual time=54,565.519..55,321.922 rows=500 loops=1)

2. 192.260 54,565.558 ↓ 500.0 500 1

Sort (cost=1,192,293.22..1,192,293.23 rows=1 width=36) (actual time=54,565.518..54,565.558 rows=500 loops=1)

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

Subquery Scan on tmp (cost=1,192,290.20..1,192,293.21 rows=1 width=36) (actual time=54,251.848..54,373.298 rows=105,389 loops=1)

4. 188.955 54,334.681 ↓ 105,389.0 105,389 1

HashAggregate (cost=1,192,290.20..1,192,293.20 rows=1 width=40) (actual time=54,251.846..54,334.681 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. 18.086 54,145.726 ↑ 20.2 106,869 1

Append (cost=605,063.61..1,159,903.57 rows=2,159,109 width=1,223) (actual time=43,651.752..54,145.726 rows=106,869 loops=1)

6. 52.161 54,127.638 ↑ 20.2 106,869 1

Result (cost=605,063.61..1,149,108.00 rows=2,159,108 width=1,223) (actual time=43,651.752..54,127.638 rows=106,869 loops=1)

7. 11.098 54,075.477 ↑ 20.2 106,869 1

Append (cost=605,063.61..1,116,721.38 rows=2,159,108 width=1,215) (actual time=43,651.748..54,075.477 rows=106,869 loops=1)

8. 88.866 54,064.378 ↑ 20.2 106,869 1

Result (cost=605,063.61..1,105,925.83 rows=2,159,107 width=1,215) (actual time=43,651.747..54,064.378 rows=106,869 loops=1)

9. 11.852 53,975.512 ↑ 20.2 106,869 1

Append (cost=605,063.61..1,078,936.99 rows=2,159,107 width=1,211) (actual time=43,651.742..53,975.512 rows=106,869 loops=1)

10. 0.000 53,963.658 ↑ 20.2 106,869 1

Subquery Scan on *SELECT* 1 (cost=605,063.61..1,068,141.45 rows=2,159,106 width=1,006) (actual time=43,651.742..53,963.658 rows=106,869 loops=1)

11. 4,911.779 54,616.203 ↑ 20.2 106,869 1

Gather (cost=605,063.61..1,041,152.63 rows=2,159,106 width=1,002) (actual time=43,651.737..54,616.203 rows=106,869 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 6,336.953 49,704.424 ↑ 25.3 35,623 3 / 3

Parallel Hash Join (cost=604,063.61..824,242.03 rows=899,628 width=1,002) (actual time=43,650.151..49,704.424 rows=35,623 loops=3)

  • Hash Cond: (o_3.id = l.shopify_order_id)
13. 4.910 8,753.212 ↑ 1.3 35,130 3 / 3

Parallel Append (cost=412.69..99,707.52 rows=45,037 width=674) (actual time=194.120..8,753.212 rows=35,130 loops=3)

14. 4,997.709 5,117.937 ↑ 1.3 15,000 3 / 3

Parallel Bitmap Heap Scan on shopify_orders_2019_06_09 o_3 (cost=1,031.33..55,848.11 rows=19,511 width=779) (actual time=123.554..5,117.937 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=2,169
15. 120.228 120.228 ↑ 1.0 45,000 1 / 3

Bitmap Index Scan on shopify_orders_2019_06_09_on_shop_id (cost=0.00..1,019.63 rows=46,827 width=0) (actual time=360.684..360.684 rows=45,000 loops=1)

  • Index Cond: (shop_id = 80)
16. 1,641.930 1,709.781 ↓ 2.3 17,946 1 / 3

Parallel Bitmap Heap Scan on shopify_orders_2019_09_12 o_4 (cost=412.69..10,070.56 rows=7,757 width=610) (actual time=207.997..5,129.342 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))
17. 67.851 67.851 ↑ 1.0 17,946 1 / 3

Bitmap Index Scan on shopify_orders_2019_09_12_on_shop_id (cost=0.00..408.04 rows=18,616 width=0) (actual time=203.553..203.553 rows=17,946 loops=1)

  • Index Cond: (shop_id = 80)
18. 984.820 1,034.767 ↓ 1.2 6,398 2 / 3

Parallel Bitmap Heap Scan on shopify_orders_2019_01_03 o_1 (cost=301.92..12,021.31 rows=5,242 width=584) (actual time=77.160..1,552.150 rows=6,398 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=994
19. 49.946 49.946 ↓ 1.0 12,795 1 / 3

Bitmap Index Scan on shopify_orders_2019_01_03_on_shop_id (cost=0.00..298.78 rows=12,581 width=0) (actual time=149.839..149.839 rows=12,795 loops=1)

  • Index Cond: (shop_id = 80)
20. 885.817 885.817 ↓ 2.4 29,648 1 / 3

Parallel Seq Scan on shopify_orders_2019_03_06 o_2 (cost=0.00..21,542.36 rows=12,526 width=588) (actual time=11.399..2,657.451 rows=29,648 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))
  • Rows Removed by Filter: 221,822
21. 0.000 0.000 ↓ 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.001 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))
22. 5,874.944 34,614.259 ↑ 1.2 1,816,981 3 / 3

Parallel Hash (cost=498,071.74..498,071.74 rows=2,261,774 width=253) (actual time=34,614.258..34,614.259 rows=1,816,981 loops=3)

  • Buckets: 16,384 Batches: 512 Memory Usage: 3,360kB
23. 28,739.315 28,739.315 ↑ 1.2 1,816,981 3 / 3

Parallel Seq Scan on shopify_line_items l (cost=0.00..498,071.74 rows=2,261,774 width=253) (actual time=0.018..28,739.315 rows=1,816,981 loops=3)

24. 0.001 0.002 ↓ 0.0 0 1

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

25. 0.001 0.001 ↓ 0.0 0 1

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

  • One-Time Filter: false
26. 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)

27. 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
28. 0.001 0.002 ↓ 0.0 0 1

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

29. 0.001 0.001 ↓ 0.0 0 1

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

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

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

  • One-Time Filter: false
Planning time : 423.006 ms
Execution time : 55,323.467 ms