explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E6WC

Settings
# exclusive inclusive rows x rows loops node
1. 2,155.325 2,823.042 ↑ 1.0 1,000 1

Limit (cost=583,359.48..583,361.98 rows=1,000 width=1,167) (actual time=2,822.938..2,823.042 rows=1,000 loops=1)

  • Functions: 139
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 21.645 ms, Inlining 25.654 ms, Optimization 1385.882 ms, Emission 742.871 ms, Total 2176.051 ms
2. 27.566 667.717 ↑ 40.6 1,000 1

Sort (cost=583,359.48..583,460.94 rows=40,583 width=1,167) (actual time=667.676..667.717 rows=1,000 loops=1)

  • Sort Key: lot.name, bi.basketitemid
  • Sort Method: top-N heapsort Memory: 1026kB
3. 114.064 640.151 ↑ 1.0 40,580 1

WindowAgg (cost=575,554.20..581,134.36 rows=40,583 width=1,167) (actual time=524.007..640.151 rows=40,580 loops=1)

4. 38.136 526.087 ↑ 1.0 40,580 1

Sort (cost=575,554.20..575,655.66 rows=40,583 width=550) (actual time=523.860..526.087 rows=40,580 loops=1)

  • Sort Key: lot.name
  • Sort Method: quicksort Memory: 24375kB
5. 11.558 487.951 ↑ 1.0 40,580 1

Hash Left Join (cost=33,080.80..572,447.86 rows=40,583 width=550) (actual time=304.232..487.951 rows=40,580 loops=1)

  • Hash Cond: ((a.articleid)::integer = (pm.articleid)::integer)
6. 8.217 404.473 ↑ 1.0 40,580 1

Hash Left Join (cost=31,403.50..570,664.02 rows=40,583 width=546) (actual time=232.259..404.473 rows=40,580 loops=1)

  • Hash Cond: ((a.parentarticleid)::integer = (lot.articleid)::integer)
7. 12.804 395.736 ↑ 1.0 40,580 1

Hash Left Join (cost=30,807.83..569,961.81 rows=40,583 width=518) (actual time=231.696..395.736 rows=40,580 loops=1)

  • Hash Cond: ((a.articleid)::integer = (sgi6.articleid)::integer)
8. 12.764 359.949 ↑ 1.0 40,580 1

Hash Left Join (cost=25,972.65..565,020.10 rows=40,583 width=510) (actual time=208.394..359.949 rows=40,580 loops=1)

  • Hash Cond: ((a.articleid)::integer = (sgi5.articleid)::integer)
9. 15.715 314.969 ↑ 1.0 40,580 1

Nested Loop Left Join (cost=20,251.92..559,192.83 rows=40,583 width=502) (actual time=175.587..314.969 rows=40,580 loops=1)

10. 12.562 299.254 ↑ 1.0 40,580 1

Hash Left Join (cost=20,251.49..339,652.59 rows=40,583 width=498) (actual time=175.535..299.254 rows=40,580 loops=1)

  • Hash Cond: ((pa.purchasearticleid)::integer = (tn.purchasearticleid)::integer)
11. 16.724 280.687 ↑ 1.0 40,580 1

Nested Loop Left Join (cost=19,239.02..337,003.06 rows=40,583 width=491) (actual time=169.328..280.687 rows=40,580 loops=1)

12. 12.480 263.963 ↑ 1.0 40,580 1

Hash Join (cost=19,238.58..31,778.72 rows=40,583 width=491) (actual time=169.269..263.963 rows=40,580 loops=1)

  • Hash Cond: ((a.defaultsalearticleid)::integer = (sa.salearticleid)::integer)
13. 9.651 199.365 ↑ 1.0 40,583 1

Hash Join (cost=13,418.36..25,851.96 rows=40,583 width=424) (actual time=116.772..199.365 rows=40,583 loops=1)

  • Hash Cond: ((b.supplierid)::integer = (s.companyid)::integer)
14. 10.367 169.153 ↑ 1.0 40,583 1

Hash Join (cost=8,685.55..21,012.61 rows=40,583 width=399) (actual time=95.872..169.153 rows=40,583 loops=1)

  • Hash Cond: ((bi.basketid)::integer = (b.basketid)::integer)
15. 46.188 157.841 ↑ 1.0 40,583 1

Hash Join (cost=8,492.01..20,712.31 rows=40,583 width=386) (actual time=94.892..157.841 rows=40,583 loops=1)

  • Hash Cond: ((a.articleid)::integer = (pa.articleid)::integer)
16. 17.100 17.100 ↑ 1.1 146,905 1

Seq Scan on articles a (cost=0.00..11,211.62 rows=160,762 width=81) (actual time=0.007..17.100 rows=146,905 loops=1)

17. 19.282 94.553 ↑ 1.0 40,583 1

Hash (cost=7,984.72..7,984.72 rows=40,583 width=309) (actual time=94.553..94.553 rows=40,583 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 8972kB
18. 15.534 75.271 ↑ 1.0 40,583 1

Hash Join (cost=6,186.36..7,984.72 rows=40,583 width=309) (actual time=56.424..75.271 rows=40,583 loops=1)

  • Hash Cond: ((bi.purchasearticleid)::integer = (pa.purchasearticleid)::integer)
19. 3.986 3.986 ↑ 1.0 40,583 1

Seq Scan on dp_basketitems bi (cost=0.00..1,691.83 rows=40,583 width=238) (actual time=0.020..3.986 rows=40,583 loops=1)

20. 34.952 55.751 ↓ 1.0 131,742 1

Hash (cost=4,552.27..4,552.27 rows=130,727 width=71) (actual time=55.751..55.751 rows=131,742 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 15606kB
21. 20.799 20.799 ↓ 1.0 131,742 1

Seq Scan on purchasearticles pa (cost=0.00..4,552.27 rows=130,727 width=71) (actual time=0.012..20.799 rows=131,742 loops=1)

22. 0.332 0.945 ↓ 1.0 2,026 1

Hash (cost=168.24..168.24 rows=2,024 width=17) (actual time=0.944..0.945 rows=2,026 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 119kB
23. 0.613 0.613 ↓ 1.0 2,026 1

Seq Scan on dp_baskets b (cost=0.00..168.24 rows=2,024 width=17) (actual time=0.024..0.613 rows=2,026 loops=1)

24. 8.234 20.561 ↓ 1.0 40,446 1

Hash (cost=4,227.36..4,227.36 rows=40,436 width=33) (actual time=20.561..20.561 rows=40,446 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3173kB
25. 12.327 12.327 ↓ 1.0 40,446 1

Seq Scan on companies s (cost=0.00..4,227.36 rows=40,436 width=33) (actual time=0.027..12.327 rows=40,446 loops=1)

26. 29.591 52.118 ↓ 1.0 136,268 1

Hash (cost=4,118.99..4,118.99 rows=136,099 width=75) (actual time=52.118..52.118 rows=136,268 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 16606kB
27. 22.527 22.527 ↓ 1.0 136,268 1

Seq Scan on salearticles sa (cost=0.00..4,118.99 rows=136,099 width=75) (actual time=0.061..22.527 rows=136,268 loops=1)

28. 0.000 0.000 ↓ 0.0 0 40,580

Index Scan using orderitems_pkey on orderitems oi (cost=0.44..7.52 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=40,580)

  • Index Cond: ((orderitemid)::integer = (bi.orderitemid)::integer)
29. 3.097 6.005 ↑ 1.1 20,572 1

Hash (cost=731.38..731.38 rows=22,487 width=15) (actual time=6.005..6.005 rows=20,572 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1221kB
30. 2.908 2.908 ↑ 1.0 22,425 1

Seq Scan on purchasearticles_tnved tn (cost=0.00..731.38 rows=22,487 width=15) (actual time=0.015..2.908 rows=22,425 loops=1)

  • Filter: ((begindate < now()) AND ((enddate > now()) OR (enddate IS NULL)))
  • Rows Removed by Filter: 2174
31. 0.000 0.000 ↓ 0.0 0 40,580

Index Scan using orders_pkey on orders o (cost=0.43..5.41 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=40,580)

  • Index Cond: ((orderid)::integer = (oi.orderid)::integer)
32. 9.934 32.216 ↑ 1.0 68,363 1

Hash (cost=4,861.07..4,861.07 rows=68,773 width=12) (actual time=32.216..32.216 rows=68,363 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3962kB
33. 18.330 22.282 ↑ 1.0 68,363 1

Bitmap Heap Scan on storegroupitems sgi5 (cost=1,701.41..4,861.07 rows=68,773 width=12) (actual time=4.197..22.282 rows=68,363 loops=1)

  • Recheck Cond: ((storegroupid)::integer = 5)
  • Heap Blocks: exact=2270
34. 3.952 3.952 ↓ 1.0 69,479 1

Bitmap Index Scan on ui_storegroupitems_articlegroup (cost=0.00..1,684.22 rows=68,773 width=0) (actual time=3.952..3.952 rows=69,479 loops=1)

  • Index Cond: ((storegroupid)::integer = 5)
35. 8.857 22.983 ↓ 1.0 58,944 1

Hash (cost=4,101.75..4,101.75 rows=58,674 width=12) (actual time=22.983..22.983 rows=58,944 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3045kB
36. 11.503 14.126 ↓ 1.0 58,944 1

Bitmap Heap Scan on storegroupitems sgi6 (cost=1,068.33..4,101.75 rows=58,674 width=12) (actual time=2.849..14.126 rows=58,944 loops=1)

  • Recheck Cond: ((storegroupid)::integer = 6)
  • Heap Blocks: exact=2167
37. 2.623 2.623 ↓ 1.0 58,948 1

Bitmap Index Scan on i_storegroupitems_articleid_group6 (cost=0.00..1,053.66 rows=58,674 width=0) (actual time=2.623..2.623 rows=58,948 loops=1)

38. 0.034 0.520 ↓ 9.9 169 1

Hash (cost=595.45..595.45 rows=17 width=36) (actual time=0.520..0.520 rows=169 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
39. 0.016 0.486 ↓ 9.9 169 1

Subquery Scan on lot (cost=594.94..595.45 rows=17 width=36) (actual time=0.341..0.486 rows=169 loops=1)

40. 0.166 0.470 ↓ 9.9 169 1

GroupAggregate (cost=594.94..595.28 rows=17 width=36) (actual time=0.336..0.470 rows=169 loops=1)

  • Group Key: aci.articleid
41. 0.053 0.304 ↓ 9.9 169 1

Sort (cost=594.94..594.98 rows=17 width=12) (actual time=0.296..0.304 rows=169 loops=1)

  • Sort Key: aci.articleid
  • Sort Method: quicksort Memory: 37kB
42. 0.049 0.251 ↓ 9.9 169 1

Hash Join (cost=46.18..594.59 rows=17 width=12) (actual time=0.174..0.251 rows=169 loops=1)

  • Hash Cond: ((aci.articleclassificationid)::integer = (ac.articleclassificationid)::integer)
43. 0.081 0.108 ↓ 1.4 169 1

Bitmap Heap Scan on articleclassificationitems aci (cost=5.60..553.69 rows=125 width=8) (actual time=0.051..0.108 rows=169 loops=1)

  • Recheck Cond: ((articleclassificationtypeid)::integer = 72)
  • Filter: ((begindate < now()) AND ((enddate > now()) OR (enddate IS NULL)))
  • Heap Blocks: exact=13
44. 0.027 0.027 ↓ 1.1 169 1

Bitmap Index Scan on i_articleclassificationitems_actid (cost=0.00..5.57 rows=153 width=0) (actual time=0.027..0.027 rows=169 loops=1)

  • Index Cond: ((articleclassificationtypeid)::integer = 72)
45. 0.045 0.094 ↑ 1.0 70 1

Hash (cost=39.70..39.70 rows=70 width=12) (actual time=0.094..0.094 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
46. 0.027 0.049 ↑ 1.0 70 1

Bitmap Heap Scan on articleclassifications ac (cost=4.83..39.70 rows=70 width=12) (actual time=0.039..0.049 rows=70 loops=1)

  • Recheck Cond: ((articleclassificationtypeid)::integer = 72)
  • Heap Blocks: exact=2
47. 0.022 0.022 ↑ 1.0 70 1

Bitmap Index Scan on articleclassifications_actid (cost=0.00..4.81 rows=70 width=0) (actual time=0.022..0.022 rows=70 loops=1)

  • Index Cond: ((articleclassificationtypeid)::integer = 72)
48. 6.987 71.920 ↓ 123.9 57,346 1

Hash (cost=1,671.51..1,671.51 rows=463 width=8) (actual time=71.920..71.920 rows=57,346 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2609kB
49. 3.803 64.933 ↓ 123.9 57,346 1

Subquery Scan on pm (cost=1,662.25..1,671.51 rows=463 width=8) (actual time=53.755..64.933 rows=57,346 loops=1)

50. 25.304 61.130 ↓ 123.9 57,346 1

HashAggregate (cost=1,662.25..1,666.88 rows=463 width=8) (actual time=53.746..61.130 rows=57,346 loops=1)

  • Group Key: aci_1.articleid
51. 4.185 35.826 ↓ 123.9 57,346 1

Nested Loop (cost=5.70..1,659.94 rows=463 width=8) (actual time=1.195..35.826 rows=57,346 loops=1)

52. 0.025 0.025 ↑ 1.0 4 1

Index Only Scan using i_articleclassifications_36 on articleclassifications ac_1 (cost=0.13..18.64 rows=4 width=4) (actual time=0.019..0.025 rows=4 loops=1)

  • Heap Fetches: 4
53. 26.080 31.616 ↓ 154.2 14,336 4

Bitmap Heap Scan on articleclassificationitems aci_1 (cost=5.57..409.39 rows=93 width=12) (actual time=1.658..7.904 rows=14,336 loops=4)

  • Recheck Cond: (((articleclassificationid)::integer = (ac_1.articleclassificationid)::integer) AND (begindate < now()) AND ((articleclassificationtypeid)::integer = 36))
  • Filter: ((enddate > now()) OR (enddate IS NULL))
  • Rows Removed by Filter: 3995
  • Heap Blocks: exact=9925
54. 5.536 5.536 ↓ 164.4 18,582 4

Bitmap Index Scan on i_articleclassificationitems_36 (cost=0.00..5.55 rows=113 width=0) (actual time=1.384..1.384 rows=18,582 loops=4)

  • Index Cond: (((articleclassificationid)::integer = (ac_1.articleclassificationid)::integer) AND (begindate < now()))
Execution time : 2,852.656 ms