explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GaIK

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.114 60.952 ↑ 4.6 303 1

Unique (cost=6,735.65..6,749.56 rows=1,391 width=77) (actual time=60.793..60.952 rows=303 loops=1)

  • Buffers: shared hit=28,489
2. 0.659 60.838 ↑ 1.3 1,040 1

Sort (cost=6,735.65..6,739.12 rows=1,391 width=77) (actual time=60.792..60.838 rows=1,040 loops=1)

  • Sort Key: cu.id, tm.id, ct.id, od.created_at
  • Sort Method: quicksort Memory: 195kB
  • Buffers: shared hit=28,489
3. 1.766 60.179 ↑ 1.3 1,040 1

WindowAgg (cost=6,614.34..6,663.02 rows=1,391 width=77) (actual time=58.368..60.179 rows=1,040 loops=1)

  • Buffers: shared hit=28,489
4. 0.648 58.413 ↑ 1.3 1,040 1

Sort (cost=6,614.34..6,617.82 rows=1,391 width=63) (actual time=58.350..58.413 rows=1,040 loops=1)

  • Sort Key: cu.id, ct.id, od.created_at
  • Sort Method: quicksort Memory: 195kB
  • Buffers: shared hit=28,489
5. 0.259 57.765 ↑ 1.3 1,040 1

Hash Left Join (cost=1,568.02..6,541.72 rows=1,391 width=63) (actual time=26.034..57.765 rows=1,040 loops=1)

  • Hash Cond: ((cu.id = pod.customer_id) AND (tm.id = ptm.id))
  • Buffers: shared hit=28,489
6. 0.217 37.280 ↑ 1.3 1,040 1

Hash Join (cost=222.88..5,182.65 rows=1,391 width=55) (actual time=5.801..37.280 rows=1,040 loops=1)

  • Hash Cond: (od.term_id = tm.id)
  • Buffers: shared hit=8,884
7. 0.224 37.053 ↑ 1.3 1,040 1

Hash Join (cost=221.48..5,176.74 rows=1,391 width=55) (actual time=5.787..37.053 rows=1,040 loops=1)

  • Hash Cond: (pr.category_id = ct.id)
  • Buffers: shared hit=8,883
8. 0.264 36.686 ↑ 1.3 1,040 1

Hash Join (cost=165.77..5,117.34 rows=1,391 width=55) (actual time=5.639..36.686 rows=1,040 loops=1)

  • Hash Cond: (ol.product_id = pr.id)
  • Buffers: shared hit=8,836
9. 0.512 35.728 ↑ 1.3 1,040 1

Nested Loop (cost=5.94..4,953.85 rows=1,391 width=55) (actual time=4.938..35.728 rows=1,040 loops=1)

  • Buffers: shared hit=8,718
10. 0.620 33.136 ↑ 1.3 1,040 1

Nested Loop (cost=5.65..4,481.64 rows=1,391 width=48) (actual time=4.928..33.136 rows=1,040 loops=1)

  • Join Filter: ((os.shop_fid = 28) OR (ol.quantity > ol.refunded))
  • Buffers: shared hit=5,586
11. 0.228 30.658 ↑ 1.6 929 1

Hash Join (cost=5.23..3,455.80 rows=1,503 width=52) (actual time=4.912..30.658 rows=929 loops=1)

  • Hash Cond: (od.last_order_state_id = os.id)
  • Buffers: shared hit=1,851
12. 8.571 30.418 ↑ 6.4 1,003 1

Hash Join (cost=3.58..3,434.85 rows=6,441 width=52) (actual time=1.089..30.418 rows=1,003 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,850
13. 21.836 21.836 ↑ 1.0 125,233 1

Seq Scan on orders od (cost=0.00..3,099.33 rows=125,233 width=36) (actual time=0.004..21.836 rows=125,233 loops=1)

  • Buffers: shared hit=1,847
14. 0.003 0.011 ↑ 1.0 2 1

Hash (cost=3.55..3.55 rows=2 width=24) (actual time=0.010..0.011 rows=2 loops=1)

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

Seq Scan on shops sh (cost=0.00..3.55 rows=2 width=24) (actual time=0.005..0.008 rows=2 loops=1)

  • Filter: ((shop_fid = 31) OR (shop_fid = 17))
  • Rows Removed by Filter: 35
  • Buffers: shared hit=3
16. 0.003 0.012 ↑ 1.0 7 1

Hash (cost=1.56..1.56 rows=7 width=8) (actual time=0.012..0.012 rows=7 loops=1)

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

Seq Scan on order_states os (cost=0.00..1.56 rows=7 width=8) (actual time=0.006..0.009 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. 1.858 1.858 ↑ 4.0 1 929

Index Scan using orders_lines_order_id_idx on orders_lines ol (cost=0.42..0.62 rows=4 width=12) (actual time=0.002..0.002 rows=1 loops=929)

  • Index Cond: (order_id = od.id)
  • Buffers: shared hit=3,735
19. 2.080 2.080 ↑ 1.0 1 1,040

Index Scan using customers_pkey on customers cu (cost=0.29..0.34 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=1,040)

  • Index Cond: (id = od.customer_id)
  • Buffers: shared hit=3,132
20. 0.249 0.694 ↑ 1.0 1,859 1

Hash (cost=136.59..136.59 rows=1,859 width=8) (actual time=0.694..0.694 rows=1,859 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 89kB
  • Buffers: shared hit=118
21. 0.445 0.445 ↑ 1.0 1,859 1

Seq Scan on products pr (cost=0.00..136.59 rows=1,859 width=8) (actual time=0.003..0.445 rows=1,859 loops=1)

  • Buffers: shared hit=118
22. 0.045 0.143 ↑ 1.0 387 1

Hash (cost=50.87..50.87 rows=387 width=4) (actual time=0.143..0.143 rows=387 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=47
23. 0.098 0.098 ↑ 1.0 387 1

Seq Scan on categories ct (cost=0.00..50.87 rows=387 width=4) (actual time=0.002..0.098 rows=387 loops=1)

  • Buffers: shared hit=47
24. 0.005 0.010 ↓ 1.1 19 1

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

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

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

  • Buffers: shared hit=1
26. 0.804 20.226 ↓ 131.7 5,398 1

Hash (cost=1,344.53..1,344.53 rows=41 width=16) (actual time=20.226..20.226 rows=5,398 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 318kB
  • Buffers: shared hit=19,605
27. 0.864 19.422 ↓ 131.7 5,398 1

Merge Join (cost=1,343.83..1,344.53 rows=41 width=16) (actual time=18.323..19.422 rows=5,398 loops=1)

  • Merge Cond: (pod.term_id = ptm.id)
  • Buffers: shared hit=19,605
28. 1.250 18.546 ↓ 131.7 5,398 1

Sort (cost=1,342.27..1,342.37 rows=41 width=16) (actual time=18.306..18.546 rows=5,398 loops=1)

  • Sort Key: pod.term_id
  • Sort Method: quicksort Memory: 446kB
  • Buffers: shared hit=19,604
29. 1.055 17.296 ↓ 131.7 5,398 1

Hash Join (cost=14.28..1,341.17 rows=41 width=16) (actual time=0.649..17.296 rows=5,398 loops=1)

  • Hash Cond: (pod.last_order_state_id = pos.id)
  • Buffers: shared hit=19,604
30. 6.040 16.227 ↓ 33.8 5,910 1

Nested Loop (cost=12.63..1,339.00 rows=175 width=20) (actual time=0.622..16.227 rows=5,910 loops=1)

  • Buffers: shared hit=19,603
31. 0.700 4.277 ↓ 33.8 5,910 1

Nested Loop (cost=12.33..1,280.04 rows=175 width=4) (actual time=0.608..4.277 rows=5,910 loops=1)

  • Buffers: shared hit=1,833
32. 0.008 0.008 ↑ 1.0 1 1

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

  • Index Cond: (shop_fid = 970)
  • Buffers: shared hit=3
33. 3.186 3.569 ↓ 12.6 5,910 1

Bitmap Heap Scan on orders_lines pol (cost=12.06..1,267.06 rows=469 width=8) (actual time=0.598..3.569 rows=5,910 loops=1)

  • Recheck Cond: (product_id = ppr.id)
  • Heap Blocks: exact=1,805
  • Buffers: shared hit=1,830
34. 0.383 0.383 ↓ 12.6 5,911 1

Bitmap Index Scan on orders_lines_product_id_idx (cost=0.00..11.94 rows=469 width=0) (actual time=0.383..0.383 rows=5,911 loops=1)

  • Index Cond: (product_id = ppr.id)
  • Buffers: shared hit=25
35. 5.910 5.910 ↑ 1.0 1 5,910

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

  • Index Cond: (id = pol.order_id)
  • Buffers: shared hit=17,770
36. 0.007 0.014 ↑ 1.0 7 1

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

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

Seq Scan on order_states pos (cost=0.00..1.56 rows=7 width=4) (actual time=0.004..0.007 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
38. 0.007 0.012 ↑ 1.0 18 1

Sort (cost=1.56..1.60 rows=18 width=4) (actual time=0.011..0.012 rows=18 loops=1)

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

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

  • Buffers: shared hit=1
Planning time : 4.779 ms
Execution time : 61.271 ms