explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DQyh

Settings
# exclusive inclusive rows x rows loops node
1. 163.358 20,464.940 ↓ 0.0 0 1

Hash Left Join (cost=722,044.38..761,578.35 rows=5,380 width=56) (actual time=20,464.940..20,464.940 rows=0 loops=1)

  • Hash Cond: (b2b.customer_id = cm.previouscustomerid)
  • Filter: ((COALESCE(cm.currentcustomerid, b2b.customer_id) = 20584) OR (b2b.customer_id IS NULL))
  • Rows Removed by Filter: 1088633
2. 424.300 20,301.561 ↓ 1.0 1,088,633 1

Hash Full Join (cost=722,042.66..757,025.97 rows=1,075,903 width=56) (actual time=19,882.822..20,301.561 rows=1,088,633 loops=1)

  • Hash Cond: (((COALESCE(cm_1.currentcustomerid, ind.customer_id)) = b2b.customer_id) AND ((COALESCE(mm.currentmodelid, indp.model_id)) = b2b.model_id) AND (color.id = b2b.color_id) AND (size.id = b2b.size_id) AND (dnp.deliverydate = b2b.delivery) AND (ind.season_id = b2b.season_id))
  • Filter: (((b2b.delivery >= '2010-01-01'::date) OR (b2b.delivery IS NULL)) AND (((COALESCE(cm_1.currentcustomerid, ind.customer_id)) IS DISTINCT FROM b2b.customer_id) OR ((COALESCE(mm.currentmodelid, indp.model_id)) IS DISTINCT FROM b2b.model_id) OR (color.id IS DISTINCT FROM b2b.color_id) OR (size.id IS DISTINCT FROM b2b.size_id) OR (dnp.deliverydate IS DISTINCT FROM b2b.delivery) OR (ind.season_id IS DISTINCT FROM b2b.season_id) OR ((sum(dnps.quantity)) IS DISTINCT FROM b2b.quantity)))
  • Rows Removed by Filter: 2650
3. 3.543 19,382.969 ↓ 9.7 2,650 1

HashAggregate (cost=675,075.57..675,078.29 rows=272 width=36) (actual time=19,382.359..19,382.969 rows=2,650 loops=1)

  • Group Key: COALESCE(cm_1.currentcustomerid, ind.customer_id), COALESCE(mm.currentmodelid, indp.model_id), color.id, size.id, dnp.deliverydate, ind.season_id
4. 0.981 19,379.426 ↓ 9.8 2,661 1

Nested Loop (cost=373,409.92..675,070.81 rows=272 width=36) (actual time=12,256.903..19,379.426 rows=2,661 loops=1)

5. 0.023 0.023 ↑ 1.0 1 1

Index Scan using customer_pkey on customer c (cost=0.28..8.30 rows=1 width=4) (actual time=0.018..0.023 rows=1 loops=1)

  • Index Cond: (id = 20584)
  • Filter: active
6. 105.090 19,378.422 ↓ 9.8 2,661 1

Hash Join (cost=373,409.64..675,059.79 rows=272 width=36) (actual time=12,256.877..19,378.422 rows=2,661 loops=1)

  • Hash Cond: (indp.indent_id = ind.id)
7. 349.133 19,024.853 ↓ 21.5 1,187,731 1

Hash Join (cost=358,181.85..659,621.81 rows=55,325 width=28) (actual time=12,007.511..19,024.853 rows=1,187,731 loops=1)

  • Hash Cond: ((indp.color_code)::text = (color.code)::text)
8. 398.025 18,675.403 ↓ 21.5 1,187,731 1

Hash Join (cost=358,144.54..658,754.77 rows=55,296 width=28) (actual time=12,007.175..18,675.403 rows=1,187,731 loops=1)

  • Hash Cond: ((indp.model_id = m2s.model_id) AND ((indps.size)::text = (size.size)::text))
9. 2,136.923 18,271.000 ↑ 2.6 1,187,731 1

Hash Join (cost=357,482.66..595,593.33 rows=3,097,330 width=26) (actual time=12,000.757..18,271.000 rows=1,187,731 loops=1)

  • Hash Cond: (indps.id = dnps.indentpositionsize_id)
10. 4,139.157 4,139.157 ↓ 1.0 5,677,242 1

Seq Scan on indentpositionsize indps (cost=0.00..172,805.69 rows=5,493,069 width=10) (actual time=0.007..4,139.157 rows=5,677,242 loops=1)

11. 443.754 11,994.920 ↑ 2.6 1,187,731 1

Hash (cost=318,766.04..318,766.04 rows=3,097,330 width=32) (actual time=11,994.920..11,994.920 rows=1,187,731 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 110223kB
12. 1,197.718 11,551.166 ↑ 2.6 1,187,731 1

Hash Join (cost=187,368.30..318,766.04 rows=3,097,330 width=32) (actual time=6,269.517..11,551.166 rows=1,187,731 loops=1)

  • Hash Cond: (dnps.deliverynoteposition_id = dnp.id)
13. 4,087.810 4,087.810 ↓ 1.0 3,996,203 1

Seq Scan on deliverynotepositionsize dnps (cost=0.00..80,932.96 rows=3,898,296 width=16) (actual time=1.087..4,087.810 rows=3,996,203 loops=1)

14. 214.304 6,265.638 ↑ 2.3 656,795 1

Hash (cost=168,396.06..168,396.06 rows=1,517,779 width=24) (actual time=6,265.638..6,265.638 rows=656,795 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 51460kB
15. 630.889 6,051.334 ↑ 2.3 656,795 1

Hash Join (cost=103,379.25..168,396.06 rows=1,517,779 width=24) (actual time=3,331.556..6,051.334 rows=656,795 loops=1)

  • Hash Cond: (dnp.indentposition_id = indp.id)
16. 2,093.400 2,093.400 ↓ 1.0 1,963,379 1

Seq Scan on deliverynoteposition dnp (cost=0.00..40,288.44 rows=1,910,116 width=12) (actual time=1.494..2,093.400 rows=1,963,379 loops=1)

  • Filter: (deliverydate >= '2010-01-01'::date)
17. 184.879 3,327.045 ↑ 2.4 731,484 1

Hash (cost=81,547.05..81,547.05 rows=1,746,576 width=20) (actual time=3,327.045..3,327.045 rows=731,484 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 52602kB
18. 2,574.904 3,142.166 ↑ 2.4 731,484 1

Hash Left Join (cost=1.02..81,547.05 rows=1,746,576 width=20) (actual time=0.095..3,142.166 rows=731,484 loops=1)

  • Hash Cond: (indp.model_id = mm.previousmodelid)
  • Filter: (COALESCE(mm.currentmodelid, indp.model_id) = ANY ('{51502,50476,50840,52142,50977,50822,50358,50849,51570,52642,52328,52332,52337,51156,50205,51220,52022,52359,51357,51757,51706,50440,50850,51008,51153,50432,51800,51918,50412,52725,52617,52708,52569,52884,52885,52552,52613,52503,52711,52717,52685,52710,53055,52512,52707,53054,52890,52824,52550,52728,52736,52506,52718,52558,50068,52568,52499,52327,51970,50622,52518,52026,51633,52474,51755,52258,51920,50856,52547,52170,52018,52280,52536,50633,52165,50671,52537,52310,52381,50406,51231,50439,51910,52014,52366,52193,15176,51590,50817,52319,51680,50364,52286,52326,52475,50789,52008,52542,51584,51742,51987,52131,52329,51919,51964,14225,52338,50195,52369,52245,52340,13678,50679,51634,51232,52300,52653,52356,51946,51990,50178,51137,52360,52254,52435,14603,52267,51965,52472,51735,51752,52616,52333,52667,14226,50083,52370,52640,52188,52068,14970,50866,50303,51154,52016,51957,52372,50852,50843,51587,51035,52353,52339,52384,52666,14072,51778,52378,52010,52172,52513,52554,52891,52693,52514,52603,52704,52594,52700,52682,52741,52672,52614,52823,52691,52690,52593,52721,52604,52553,52739,52497,53061,52689,52579,52709,52592,52596,52561,52610,53058,52822,52804,52889,52821,52744,52605,52729,52511,52699,52788,52701,52714,52737,52505,52595,52677,52727,52556,52742,52551,52703,52619,52622,52510,52599,52719,52620,52731,52698,52747,52683,52715,52724,52726,52572,52696,52598,52559,52702,52516,52998,52971,52546,52789,52549,52500,52501,52583,52602,52587,52573,52888,52618,52548,52972,52600,52621,14646,14287,51997,51425,50863,51947,50867,51968,52037,50835,51782,50848,52019,52126,51562,52248,52383,50435,52373,51407,51595,50621,52158,51234,51510,52312,53063,52020,52465,15066,50833,51994,52011,52491,52434,52379,51460,14957,51095,50829,51032,50860,52331,52162,51155,52351,52471,13679,51009,51740,51953,51038,51826,50834,52344,51911,51593,52400,52242,50308,52354,51759,14224,51809,52375,51589,51158,51509,52152,51207,52034,51807,52382,52371,52244,52363,52171,53064,52252,52264,51421,50858,50815,50813,50788,51099,52352,52005,52377,52364,52009,52334,52341,13782}'::integer[]))
  • Rows Removed by Filter: 1334125
19. 567.249 567.249 ↑ 1.0 2,065,609 1

Seq Scan on indentposition indp (cost=0.00..73,016.12 rows=2,130,209 width=16) (actual time=0.021..567.249 rows=2,065,609 loops=1)

  • Filter: ((ordertype)::text = ANY ('{T,T-L2C,T-LISA,""T-LISA Buyer"",S,E,X,K}'::text[]))
  • Rows Removed by Filter: 97857
20. 0.002 0.013 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on modelmerge mm (cost=0.00..1.01 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1)

22. 2.141 6.378 ↑ 1.0 14,406 1

Hash (cost=445.22..445.22 rows=14,444 width=12) (actual time=6.378..6.378 rows=14,406 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 733kB
23. 2.938 4.237 ↑ 1.0 14,406 1

Hash Join (cost=10.18..445.22 rows=14,444 width=12) (actual time=0.102..4.237 rows=14,406 loops=1)

  • Hash Cond: (m2s.size_id = size.id)
24. 1.213 1.213 ↑ 1.0 14,406 1

Seq Scan on model2size m2s (cost=0.00..236.44 rows=14,444 width=8) (actual time=0.006..1.213 rows=14,406 loops=1)

25. 0.035 0.086 ↑ 1.0 319 1

Hash (cost=6.19..6.19 rows=319 width=8) (actual time=0.086..0.086 rows=319 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
26. 0.051 0.051 ↑ 1.0 319 1

Seq Scan on size (cost=0.00..6.19 rows=319 width=8) (actual time=0.010..0.051 rows=319 loops=1)

27. 0.153 0.317 ↑ 1.0 1,169 1

Hash (cost=22.69..22.69 rows=1,169 width=8) (actual time=0.317..0.317 rows=1,169 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 63kB
28. 0.164 0.164 ↑ 1.0 1,169 1

Seq Scan on color (cost=0.00..22.69 rows=1,169 width=8) (actual time=0.011..0.164 rows=1,169 loops=1)

29. 0.284 248.479 ↑ 2.5 933 1

Hash (cost=15,198.55..15,198.55 rows=2,340 width=16) (actual time=248.479..248.479 rows=933 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 76kB
30. 49.534 248.195 ↑ 2.5 933 1

Hash Left Join (cost=2.21..15,198.55 rows=2,340 width=16) (actual time=0.238..248.195 rows=933 loops=1)

  • Hash Cond: (ind.customer_id = cm_1.previouscustomerid)
  • Filter: (COALESCE(cm_1.currentcustomerid, ind.customer_id) = 20584)
  • Rows Removed by Filter: 469186
31. 198.632 198.632 ↓ 1.0 470,119 1

Seq Scan on indent ind (cost=0.00..13,370.87 rows=468,040 width=12) (actual time=0.010..198.632 rows=470,119 loops=1)

  • Filter: (season_id IS NOT NULL)
  • Rows Removed by Filter: 8018
32. 0.004 0.029 ↓ 3.4 47 1

Hash (cost=2.04..2.04 rows=14 width=8) (actual time=0.029..0.029 rows=47 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
33. 0.025 0.025 ↓ 3.4 47 1

Seq Scan on customermerge cm_1 (cost=0.00..2.04 rows=14 width=8) (actual time=0.012..0.025 rows=47 loops=1)

  • Filter: (previouscustomerid <> ALL ('{3530,8131,10969,10970,791,1906,3584,6617,10801,6992,260,4761,1349,8868,9594,8150,9127,11037}'::integer[]))
  • Rows Removed by Filter: 15
34. 309.222 494.292 ↓ 1.0 1,091,283 1

Hash (cost=20,067.74..20,067.74 rows=1,075,974 width=28) (actual time=494.292..494.292 rows=1,091,283 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 80327kB
35. 185.070 185.070 ↓ 1.0 1,091,283 1

Seq Scan on autoreorderdeliveryfull b2b (cost=0.00..20,067.74 rows=1,075,974 width=28) (actual time=0.016..185.070 rows=1,091,283 loops=1)

36. 0.009 0.021 ↓ 1.9 62 1

Hash (cost=1.32..1.32 rows=32 width=8) (actual time=0.021..0.021 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
37. 0.012 0.012 ↓ 1.9 62 1

Seq Scan on customermerge cm (cost=0.00..1.32 rows=32 width=8) (actual time=0.005..0.012 rows=62 loops=1)

Planning time : 67.557 ms
Execution time : 20,467.600 ms