explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Isb : test

Settings
# exclusive inclusive rows x rows loops node
1. 0.060 2,218.741 ↑ 1.0 10 1

Limit (cost=4,827,479.47..4,830,127.81 rows=10 width=164) (actual time=2,218.671..2,218.741 rows=10 loops=1)

  • Buffers: shared hit=302880 read=7
2. 0.329 2,218.681 ↑ 40.5 210 1

Subquery Scan on tmp (cost=4,774,512.77..7,027,981.17 rows=8,509 width=164) (actual time=2,217.503..2,218.681 rows=210 loops=1)

  • Filter: ((tmp.display_row = 1) OR (tmp.display_grand_total = 1))
  • Buffers: shared hit=302880 read=7
3. 0.806 2,218.352 ↑ 683.5 1,248 1

Aggregate (cost=4,774,512.77..7,015,185.56 rows=853,041 width=204) (actual time=2,217.501..2,218.352 rows=1,248 loops=1)

  • Buffers: shared hit=302880 read=7
4. 7.564 2,217.546 ↑ 7,846.0 259 1

Sort (cost=4,774,512.77..4,779,593.03 rows=2,032,103 width=164) (actual time=2,217.488..2,217.546 rows=259 loops=1)

  • Sort Key: sv.email, sv.number, (order_month(sv.id)), (to_char(sv.created_at, 'MM-DD-YYYY'::text)), (order_day(sv.id)), sv.name
  • Sort Method: quicksort Memory: 322kB
  • Buffers: shared hit=302880 read=7
5. 410.628 2,209.982 ↑ 1,044.2 1,946 1

Subquery Scan on sv (cost=0..4,228,207.83 rows=2,032,103 width=164) (actual time=14.069..2,209.982 rows=1,946 loops=1)

  • Buffers: shared hit=302874 read=7
6. 1.278 1,799.354 ↑ 1,044.2 1,946 1

Append (cost=0..3,186,755.04 rows=2,032,103 width=1,223) (actual time=11.843..1,799.354 rows=1,946 loops=1)

  • Buffers: shared hit=291054 read=7
7. 2.108 1,798.073 ↑ 1,044.2 1,946 1

Result (cost=0..3,176,593.5 rows=2,032,102 width=1,223) (actual time=11.842..1,798.073 rows=1,946 loops=1)

  • Buffers: shared hit=291054 read=7
8. 1.095 1,795.965 ↑ 1,044.2 1,946 1

Append (cost=0..3,146,111.97 rows=2,032,102 width=1,215) (actual time=11.839..1,795.965 rows=1,946 loops=1)

  • Buffers: shared hit=291054 read=7
9. 2.018 1,794.868 ↑ 1,044.2 1,946 1

Result (cost=0..3,135,951.45 rows=2,032,101 width=1,215) (actual time=11.839..1,794.868 rows=1,946 loops=1)

  • Buffers: shared hit=291054 read=7
10. 1.125 1,792.850 ↑ 1,044.2 1,946 1

Append (cost=0..3,110,550.19 rows=2,032,101 width=1,211) (actual time=11.835..1,792.85 rows=1,946 loops=1)

  • Buffers: shared hit=291054 read=7
11. 2.621 1,791.723 ↑ 1,044.2 1,946 1

Subquery Scan on *SELECT* 1 (cost=0..3,100,389.68 rows=2,032,100 width=877) (actual time=11.835..1,791.723 rows=1,946 loops=1)

  • Buffers: shared hit=291054 read=7
12. 1,693.559 1,789.102 ↑ 1,044.2 1,946 1

Nested Loop (cost=0..3,074,988.43 rows=2,032,100 width=873) (actual time=11.831..1,789.102 rows=1,946 loops=1)

  • Buffers: shared hit=291054 read=7
13. 1.149 15.193 ↑ 1.1 1,607 1

Append (cost=0..1,002.03 rows=1,746 width=550) (actual time=7.746..15.193 rows=1,607 loops=1)

  • Buffers: shared hit=310 read=7
14. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: ((o.created_at >= '2019-01-01 18:30:00'::timestamp without time zone) AND (o.created_at <= '2019-02-01 06:18:05.33'::timestamp without time zone) AND (o.shop_id = 5))
15. 6.328 14.031 ↑ 1.1 1,607 1

Bitmap Heap Scan on shopify_orders_2019_01_06 o_1 (cost=53.43..976.67 rows=1,743 width=550) (actual time=7.743..14.031 rows=1,607 loops=1)

  • Filter: (o_1.shop_id = 5)
  • Buffers: shared hit=306 read=7
16. 7.703 7.703 ↓ 1.0 2,074 1

Bitmap Index Scan on shopify_orders_2019_01_06_created_at (cost=0..52.99 rows=2,071 width=0) (actual time=7.703..7.703 rows=2,074 loops=1)

  • Index Cond: ((o_1.created_at >= '2019-01-01 18:30:00'::timestamp without time zone) AND (o_1.created_at <= '2019-02-01 06:18:05.33'::timestamp without time zone))
  • Buffers: shared hit=2 read=7
17. 0.007 0.007 ↓ 0.0 0 1

Index Scan using shopify_orders_2018_06_12_created_at on shopify_orders_2018_06_12 o_2 (cost=0.29..8.31 rows=1 width=542) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((o_2.created_at >= '2019-01-01 18:30:00'::timestamp without time zone) AND (o_2.created_at <= '2019-02-01 06:18:05.33'::timestamp without time zone))
  • Filter: (o_2.shop_id = 5)
  • Buffers: shared hit=2
18. 0.004 0.004 ↓ 0.0 0 1

Index Scan using shopify_orders_2017_06_12_created_at on shopify_orders_2017_06_12 o_3 (cost=0.29..8.31 rows=1 width=563) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: ((o_3.created_at >= '2019-01-01 18:30:00'::timestamp without time zone) AND (o_3.created_at <= '2019-02-01 06:18:05.33'::timestamp without time zone))
  • Filter: (o_3.shop_id = 5)
  • Buffers: shared hit=2
19. 12.856 80.350 ↑ 8.0 1 1,607

Append (cost=0..14.72 rows=8 width=200) (actual time=0.031..0.05 rows=1 loops=1,607)

  • Buffers: shared hit=19356
20. 0.000 0.000 ↓ 0.0 0 1,607

Seq Scan on shopify_line_items l (cost=0..0 rows=1 width=304) (actual time=0..0 rows=0 loops=1,607)

  • Filter: (o.id = l.shopify_order_id)
21. 51.424 51.424 ↑ 1.0 1 1,607

Index Scan using shopify_line_items_2019_01_06_on_shopify_order_id on shopify_line_items_2019_01_06 l_1 (cost=0.29..1.56 rows=1 width=207) (actual time=0.028..0.032 rows=1 loops=1,607)

  • Index Cond: (l_1.shopify_order_id = o.id)
  • Buffers: shared hit=4893
22. 4.821 4.821 ↓ 0.0 0 1,607

Index Scan using shopify_line_items_2019_06_12_on_shopify_order_id on shopify_line_items_2019_06_12 l_2 (cost=0.29..3.03 rows=2 width=184) (actual time=0.003..0.003 rows=0 loops=1,607)

  • Index Cond: (l_2.shopify_order_id = o.id)
  • Buffers: shared hit=3214
23. 3.214 3.214 ↓ 0.0 0 1,607

Index Scan using shopify_line_items_2018_01_06_on_shopify_order_id on shopify_line_items_2018_01_06 l_3 (cost=0.29..4.05 rows=1 width=206) (actual time=0.002..0.002 rows=0 loops=1,607)

  • Index Cond: (l_3.shopify_order_id = o.id)
  • Buffers: shared hit=3214
24. 3.214 3.214 ↓ 0.0 0 1,607

Index Scan using shopify_line_items_2018_06_12_on_shopify_order_id on shopify_line_items_2018_06_12 l_4 (cost=0.29..3.07 rows=1 width=204) (actual time=0.002..0.002 rows=0 loops=1,607)

  • Index Cond: (l_4.shopify_order_id = o.id)
  • Buffers: shared hit=3214
25. 1.607 1.607 ↓ 0.0 0 1,607

Index Scan using shopify_line_items_2017_01_06_on_shopify_order_id on shopify_line_items_2017_01_06 l_5 (cost=0.14..0.19 rows=1 width=304) (actual time=0.001..0.001 rows=0 loops=1,607)

  • Index Cond: (l_5.shopify_order_id = o.id)
  • Buffers: shared hit=1607
26. 3.214 3.214 ↓ 0.0 0 1,607

Index Scan using shopify_line_items_2017_06_12_on_shopify_order_id on shopify_line_items_2017_06_12 l_6 (cost=0.29..2.79 rows=1 width=191) (actual time=0.002..0.002 rows=0 loops=1,607)

  • Index Cond: (l_6.shopify_order_id = o.id)
  • Buffers: shared hit=3214
27. 0.001 0.002 ↓ 0.0 0 1

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

28. 0.001 0.001 ↓ 0.0 0 1

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

29. 0.001 0.002 ↓ 0.0 0 1

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

30. 0.001 0.001 ↓ 0.0 0 1

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

31. 0.001 0.003 ↓ 0.0 0 1

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

32. 0.001 0.002 ↓ 0.0 0 1

Aggregate (cost=0..1.01 rows=1 width=1,186) (actual time=0.002..0.002 rows=0 loops=1)

33. 0.001 0.001 ↓ 0.0 0 1

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