explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hxDu : Optimization for: plan #GaIK

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.573 203.807 ↑ 4.2 299 1

Unique (cost=6,721.48..6,734.12 rows=1,264 width=63) (actual time=202.831..203.807 rows=299 loops=1)

  • Buffers: shared hit=26,083
2. 1.309 203.234 ↑ 1.4 880 1

Sort (cost=6,721.48..6,724.64 rows=1,264 width=63) (actual time=202.829..203.234 rows=880 loops=1)

  • Sort Key: cu.id, tm.id, ct.id, od.created_at
  • Sort Method: quicksort Memory: 148kB
  • Buffers: shared hit=26,083
3. 2.856 201.925 ↑ 1.4 880 1

WindowAgg (cost=6,612.12..6,656.36 rows=1,264 width=63) (actual time=198.745..201.925 rows=880 loops=1)

  • Buffers: shared hit=26,083
4. 1.481 199.069 ↑ 1.4 880 1

Sort (cost=6,612.12..6,615.28 rows=1,264 width=63) (actual time=198.614..199.069 rows=880 loops=1)

  • Sort Key: cu.id, ct.id, od.created_at
  • Sort Method: quicksort Memory: 148kB
  • Buffers: shared hit=26,083
5. 1.055 197.588 ↑ 1.4 880 1

Hash Left Join (cost=1,463.08..6,547.00 rows=1,264 width=63) (actual time=81.302..197.588 rows=880 loops=1)

  • Hash Cond: ((cu.id = pod.customer_id) AND (tm.id = ptm.id))
  • Buffers: shared hit=26,083
6. 0.939 138.024 ↑ 1.4 880 1

Hash Join (cost=157.70..5,228.96 rows=1,264 width=55) (actual time=22.782..138.024 rows=880 loops=1)

  • Hash Cond: (od.term_id = tm.id)
  • Buffers: shared hit=7,637
7. 1.557 137.052 ↑ 1.4 880 1

Nested Loop (cost=156.30..5,210.18 rows=1,264 width=55) (actual time=22.741..137.052 rows=880 loops=1)

  • Buffers: shared hit=7,636
8. 0.974 131.975 ↑ 1.4 880 1

Hash Join (cost=156.01..4,769.02 rows=1,264 width=48) (actual time=22.722..131.975 rows=880 loops=1)

  • Hash Cond: (pr.category_id = ct.id)
  • Buffers: shared hit=4,995
9. 0.942 130.470 ↑ 1.4 880 1

Hash Join (cost=119.30..4,714.91 rows=1,270 width=48) (actual time=22.183..130.470 rows=880 loops=1)

  • Hash Cond: (ol.product_id = pr.id)
  • Buffers: shared hit=4,967
10. 1.364 127.427 ↑ 1.4 880 1

Nested Loop (cost=4.65..4,582.80 rows=1,270 width=48) (actual time=20.063..127.427 rows=880 loops=1)

  • Buffers: shared hit=4,892
11. 0.913 122.983 ↑ 1.9 770 1

Hash Join (cost=4.23..3,645.48 rows=1,468 width=48) (actual time=20.031..122.983 rows=770 loops=1)

  • Hash Cond: (od.last_order_state_id = os.id)
  • Buffers: shared hit=1,808
12. 60.866 122.035 ↑ 7.7 815 1

Hash Join (cost=2.58..3,605.55 rows=6,293 width=52) (actual time=5.189..122.035 rows=815 loops=1)

  • Hash Cond: (od.shop_id = sh.id)
  • Join Filter: ((sh.shop_fid = 31) OR ((sh.shop_fid = 17) AND (od.created_at >= '2017-01-01 00:00:00+01'::timestamp with time zone)))
  • Rows Removed by Join Filter: 116
  • Buffers: shared hit=1,807
13. 61.137 61.137 ↑ 1.0 122,345 1

Seq Scan on orders od (cost=0.00..3,028.45 rows=122,345 width=36) (actual time=0.007..61.137 rows=122,345 loops=1)

  • Buffers: shared hit=1,805
14. 0.004 0.032 ↑ 1.0 2 1

Hash (cost=2.55..2.55 rows=2 width=24) (actual time=0.032..0.032 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
15. 0.028 0.028 ↑ 1.0 2 1

Seq Scan on shops sh (cost=0.00..2.55 rows=2 width=24) (actual time=0.018..0.028 rows=2 loops=1)

  • Filter: ((shop_fid = 31) OR (shop_fid = 17))
  • Rows Removed by Filter: 35
  • Buffers: shared hit=2
16. 0.010 0.035 ↑ 1.0 7 1

Hash (cost=1.56..1.56 rows=7 width=4) (actual time=0.035..0.035 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
17. 0.025 0.025 ↑ 1.0 7 1

Seq Scan on order_states os (cost=0.00..1.56 rows=7 width=4) (actual time=0.014..0.025 rows=7 loops=1)

  • Filter: (shop_fid = ANY ('{2,4,10,16,17,29,30}'::integer[]))
  • Rows Removed by Filter: 23
  • Buffers: shared hit=1
18. 3.080 3.080 ↑ 1.0 1 770

Index Scan using orders_lines_order_id_idx on orders_lines ol (cost=0.42..0.63 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=770)

  • Index Cond: (order_id = od.id)
  • Filter: (quantity > refunded)
  • Buffers: shared hit=3,084
19. 1.039 2.101 ↑ 1.0 1,762 1

Hash (cost=92.62..92.62 rows=1,762 width=8) (actual time=2.101..2.101 rows=1,762 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 85kB
  • Buffers: shared hit=75
20. 1.062 1.062 ↑ 1.0 1,762 1

Seq Scan on products pr (cost=0.00..92.62 rows=1,762 width=8) (actual time=0.006..1.062 rows=1,762 loops=1)

  • Buffers: shared hit=75
21. 0.209 0.531 ↑ 1.0 387 1

Hash (cost=31.87..31.87 rows=387 width=4) (actual time=0.531..0.531 rows=387 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=28
22. 0.322 0.322 ↑ 1.0 387 1

Seq Scan on categories ct (cost=0.00..31.87 rows=387 width=4) (actual time=0.005..0.322 rows=387 loops=1)

  • Buffers: shared hit=28
23. 3.520 3.520 ↑ 1.0 1 880

Index Scan using customers_pkey on customers cu (cost=0.29..0.34 rows=1 width=11) (actual time=0.003..0.004 rows=1 loops=880)

  • Index Cond: (id = od.customer_id)
  • Buffers: shared hit=2,641
24. 0.017 0.033 ↓ 1.1 19 1

Hash (cost=1.18..1.18 rows=18 width=4) (actual time=0.033..0.033 rows=19 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
25. 0.016 0.016 ↓ 1.1 19 1

Seq Scan on terms tm (cost=0.00..1.18 rows=18 width=4) (actual time=0.006..0.016 rows=19 loops=1)

  • Buffers: shared hit=1
26. 2.966 58.509 ↓ 120.7 5,071 1

Hash (cost=1,304.75..1,304.75 rows=42 width=16) (actual time=58.509..58.509 rows=5,071 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 302kB
  • Buffers: shared hit=18,446
27. 4.751 55.543 ↓ 120.7 5,071 1

Merge Join (cost=1,304.06..1,304.75 rows=42 width=16) (actual time=48.419..55.543 rows=5,071 loops=1)

  • Merge Cond: (ptm.id = pod.term_id)
  • Buffers: shared hit=18,446
28. 0.029 0.042 ↓ 1.1 19 1

Sort (cost=1.56..1.60 rows=18 width=4) (actual time=0.033..0.042 rows=19 loops=1)

  • Sort Key: ptm.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1
29. 0.013 0.013 ↓ 1.1 19 1

Seq Scan on terms ptm (cost=0.00..1.18 rows=18 width=4) (actual time=0.004..0.013 rows=19 loops=1)

  • Buffers: shared hit=1
30. 5.419 50.750 ↓ 120.7 5,071 1

Sort (cost=1,302.50..1,302.61 rows=42 width=16) (actual time=48.367..50.750 rows=5,071 loops=1)

  • Sort Key: pod.term_id
  • Sort Method: quicksort Memory: 430kB
  • Buffers: shared hit=18,445
31. 5.545 45.331 ↓ 120.7 5,071 1

Hash Join (cost=14.15..1,301.37 rows=42 width=16) (actual time=0.994..45.331 rows=5,071 loops=1)

  • Hash Cond: (pod.last_order_state_id = pos.id)
  • Buffers: shared hit=18,445
32. 10.163 39.765 ↓ 30.9 5,559 1

Nested Loop (cost=12.50..1,298.63 rows=180 width=20) (actual time=0.949..39.765 rows=5,559 loops=1)

  • Buffers: shared hit=18,444
33. 5.192 12.925 ↓ 30.9 5,559 1

Nested Loop (cost=12.20..1,236.17 rows=180 width=4) (actual time=0.933..12.925 rows=5,559 loops=1)

  • Buffers: shared hit=1,733
34. 0.010 0.010 ↑ 1.0 1 1

Index Scan using products_shop_fid_key on products ppr (cost=0.28..8.29 rows=1 width=4) (actual time=0.008..0.010 rows=1 loops=1)

  • Index Cond: (shop_fid = 970)
  • Buffers: shared hit=3
35. 7.057 7.723 ↓ 12.3 5,559 1

Bitmap Heap Scan on orders_lines pol (cost=11.93..1,223.35 rows=452 width=8) (actual time=0.917..7.723 rows=5,559 loops=1)

  • Recheck Cond: (product_id = ppr.id)
  • Heap Blocks: exact=1,709
  • Buffers: shared hit=1,730
36. 0.666 0.666 ↓ 12.3 5,559 1

Bitmap Index Scan on orders_lines_product_id_idx (cost=0.00..11.81 rows=452 width=0) (actual time=0.666..0.666 rows=5,559 loops=1)

  • Index Cond: (product_id = ppr.id)
  • Buffers: shared hit=21
37. 16.677 16.677 ↑ 1.0 1 5,559

Index Scan using orders_pkey on orders pod (cost=0.29..0.34 rows=1 width=24) (actual time=0.002..0.003 rows=1 loops=5,559)

  • Index Cond: (id = pol.order_id)
  • Buffers: shared hit=16,711
38. 0.008 0.021 ↑ 1.0 7 1

Hash (cost=1.56..1.56 rows=7 width=4) (actual time=0.021..0.021 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
39. 0.013 0.013 ↑ 1.0 7 1

Seq Scan on order_states pos (cost=0.00..1.56 rows=7 width=4) (actual time=0.009..0.013 rows=7 loops=1)

  • Filter: (shop_fid = ANY ('{2,4,10,16,17,29,30}'::integer[]))
  • Rows Removed by Filter: 23
  • Buffers: shared hit=1
Planning time : 6.705 ms
Execution time : 204.193 ms