Result: wcQ

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
0.308	2037.002	↑ 3.8	236	1	→ Unique (cost=547753.97..547796.58 rows=897 width=135) (actual time=2036.653..2037.002 rows=236 loops=1)
0.419	2036.694	↑ 3.7	240	1	→ Sort (cost=547753.97..547756.22 rows=897 width=135) (actual time=2036.652..2036.694 rows=240 loops=1) Sort Key: c.quotationitemid, b.quotationid, b.customerid, b.orderdate, b.orderno, b.userid, a.activationdate, c.quantity, (COALESCE(c.quantity, 0) - COALESCE(d.shippedtodate, 0::bigint)), c.itempn, CASE WHEN (c.deliverywks >= 14) THEN true ELSE c.custom_mfg END, (a.activationdate + (((COALESCE(c.deliverywks, 0))::text \|\| ' weeks'::text))::interval), c.requested_mfg, max(ab.promisedby), c.confirmed_mfg, CASE WHEN ((max(ab.promisedby) IS NULL) OR (c.confirmed_mfg IS NULL)) THEN true ELSE (c.confirmed_mfg <= max(ab.promisedby)) END, CASE WHEN (c.confirmed_mfg IS NULL) THEN true ELSE CASE WHEN ((ab.entrydate + '7 days'::interval) >= ('now'::text)::date) THEN true ELSE CASE WHEN (((ab.entrydate + '28 days'::interval) >= ('now'::text)::date) AND (ab.productionreqdate IS NOT NULL)) THEN true ELSE (c.confirmed_mfg <= COALESCE(c.requested_mfg, (a.activationdate + (((c.deliverywks)::text \|\| ' weeks'::text))::interval))) END END END, CASE WHEN (c.deliverywks >= 14) THEN getorderstatus(c.quotationitemid) ELSE NULL::citext END
23.214	2036.275	↑ 3.7	240	1	→ GroupAggregate (cost=547604.58..547709.98 rows=897 width=135) (actual time=2013.582..2036.275 rows=240 loops=1)
0.510	2013.061	↑ 3.7	243	1	→ Sort (cost=547604.58..547606.83 rows=897 width=135) (actual time=2013.002..2013.061 rows=243 loops=1) Sort Key: c.requested_mfg, c.quotationitemid, b.quotationid, b.customerid, b.orderdate, b.orderno, b.userid, a.activationdate, c.quantity, (COALESCE(c.quantity, 0) - COALESCE(d.shippedtodate, 0::bigint)), c.itempn, CASE WHEN (c.deliverywks >= 14) THEN true ELSE c.custom_mfg END, c.deliverywks, c.confirmed_mfg, ab.entrydate, ab.productionreqdate
0.254	2012.551	↑ 3.7	243	1	→ Merge Left Join (cost=547122.20..547560.59 rows=897 width=135) (actual time=2001.043..2012.551 rows=243 loops=1) Merge Cond: (c.quotationitemid = o.quotationitemid)
1.513	2012.252	↑ 3.7	243	1	→ Merge Left Join (cost=547120.00..547551.48 rows=897 width=135) (actual time=2000.998..2012.252 rows=243 loops=1) Merge Cond: (c.quotationitemid = ab.quotationitemid)
8.807	2008.761	↑ 3.8	236	1	→ Merge Left Join (cost=547120.00..547363.02 rows=897 width=111) (actual time=2000.730..2008.761 rows=236 loops=1) Merge Cond: (c.quotationitemid = d.quotationitemid) Filter: ((COALESCE(c.quantity, 0) - COALESCE(d.shippedtodate, 0::bigint)) > 0)
0.480	1214.261	↑ 8.0	338	1	→ Sort (cost=206365.82..206372.55 rows=2690 width=103) (actual time=1214.188..1214.261 rows=338 loops=1) Sort Key: c.quotationitemid
6.613	1213.781	↑ 8.0	338	1	→ Hash Join (cost=204494.67..206212.58 rows=2690 width=103) (actual time=1121.992..1213.781 rows=338 loops=1) Hash Cond: (c.quotationid = b.quotationid)
106.908	106.908	↑ 1.4	25716	1	→ Seq Scan on quotationitems c (cost=0.00..1553.99 rows=36538 width=66) (actual time=0.039..106.908 rows=25716 loops=1) Filter: ((itempn !~~ '%shipping%'::citext) AND (quantity > 0))
0.082	1100.260	↑ 8.8	144	1	→ Hash (cost=204478.85..204478.85 rows=1266 width=41) (actual time=1100.260..1100.260 rows=144 loops=1)
3.829	1100.178	↑ 8.8	144	1	→ Merge Left Join (cost=204357.31..204478.85 rows=1266 width=41) (actual time=1096.694..1100.178 rows=144 loops=1) Merge Cond: (b.quotationid = a.quotationid)
0.193	8.936	↑ 7.2	144	1	→ Sort (cost=1234.86..1237.47 rows=1043 width=33) (actual time=8.907..8.936 rows=144 loops=1) Sort Key: b.quotationid
8.743	8.743	↑ 7.2	144	1	→ Seq Scan on quotations b (cost=0.00..1182.58 rows=1043 width=33) (actual time=0.110..8.743 rows=144 loops=1) Filter: (orderplaced AND (shipped <> ALL ('{2,5}'::integer[])))
8.277	1087.413	↑ 1.3	15783	1	→ Sort (cost=203122.45..203174.28 rows=20732 width=12) (actual time=1084.573..1087.413 rows=15783 loops=1) Sort Key: a.quotationid
6.402	1079.136	↑ 1.3	15783	1	→ Subquery Scan a (cost=200547.58..201636.01 rows=20732 width=12) (actual time=1039.970..1079.136 rows=15783 loops=1)
28.410	1072.734	↑ 1.3	15783	1	→ GroupAggregate (cost=200547.58..201428.69 rows=20732 width=42) (actual time=1039.968..1072.734 rows=15783 loops=1)
19.936	1044.324	↑ 1.0	20484	1	→ Sort (cost=200547.58..200599.41 rows=20732 width=42) (actual time=1039.954..1044.324 rows=20484 loops=1) Sort Key: a.quotationid, a.orderdate, "substring"(quotations.paymenttype, 2, (strpos((quotations.paymenttype)::text, 'o,'::text) - 2))
49.257	1024.388	↑ 1.0	20484	1	→ Hash Join (cost=195419.94..199061.14 rows=20732 width=42) (actual time=923.495..1024.388 rows=20484 loops=1) Hash Cond: (a.quotationid = quotations.quotationid) Join Filter: CASE WHEN (("substring"(quotations.paymenttype, 2, (strpos((quotations.paymenttype)::text, 'o,'::text) - 2)))::text = '0'::text) THEN true ELSE (b.paidtodate >= b.onorder_required) END
28.141	944.636	↓ 1.0	21638	1	→ Merge Left Join (cost=194067.26..196826.43 rows=20805 width=84) (actual time=892.955..944.636 rows=21638 loops=1) Merge Cond: (a.quotationid = b.quotationid)
17.049	17.049	↑ 1.0	16336	1	→ Index Scan using quotationid on quotations a (cost=0.00..2401.58 rows=16359 width=12) (actual time=0.021..17.049 rows=16336 loops=1) Filter: orderplaced
36.796	899.446	↑ 1.0	21248	1	→ Sort (cost=194067.26..194121.58 rows=21730 width=76) (actual time=892.926..899.446 rows=21248 loops=1) Sort Key: b.quotationid
15.313	862.650	↑ 1.0	21248	1	→ Subquery Scan b (cost=7523.55..192501.89 rows=21730 width=76) (actual time=246.725..862.650 rows=21248 loops=1)
300.101	847.337	↑ 1.0	21248	1	→ Hash Join (cost=7523.55..192284.59 rows=21730 width=187) (actual time=246.723..847.337 rows=21248 loops=1) Hash Cond: (a.quotationid = d.quotationid)
78.086	307.850	↑ 1.0	21248	1	→ Hash Left Join (cost=6170.12..7701.80 rows=21820 width=179) (actual time=219.686..307.850 rows=21248 loops=1) Hash Cond: (a.quotationid = quotations.quotationid) Filter: (((("substring"(quotations.paymenttype, 2, (strpos((quotations.paymenttype)::text, 'o,'::text) - 2)))::text <> '0'::text) AND (CASE WHEN (a.paymentway = 4) THEN a.chargedate ELSE a.actualpaymentdate END IS NOT NULL)) OR (("substring"(quotations.paymenttype, 2, (strpos((quotations.paymenttype)::text, 'o,'::text) - 2)))::text = '0'::text))
10.139	10.139	↑ 1.0	22039	1	→ Seq Scan on payments a (cost=0.00..545.39 rows=22039 width=125) (actual time=0.004..10.139 rows=22039 loops=1)
11.427	219.625	↑ 1.0	17012	1	→ Hash (cost=5957.29..5957.29 rows=17026 width=58) (actual time=219.625..219.625 rows=17012 loops=1)
23.172	208.198	↑ 1.0	17012	1	→ Merge Left Join (cost=3286.58..5957.29 rows=17026 width=58) (actual time=151.291..208.198 rows=17012 loops=1) Merge Cond: (quotations.quotationid = c.quotationid)
28.063	28.063	↑ 1.0	17012	1	→ Index Scan using quotationid on quotations (cost=0.00..2401.58 rows=17026 width=26) (actual time=0.021..28.063 rows=17012 loops=1) Filter: (paymenttype IS NOT NULL)
25.122	156.963	↓ 1.1	16542	1	→ Sort (cost=3286.58..3324.43 rows=15140 width=36) (actual time=151.261..156.963 rows=16542 loops=1) Sort Key: c.quotationid
10.707	131.841	↓ 1.1	16542	1	→ Subquery Scan c (cost=1667.66..2235.41 rows=15140 width=36) (actual time=107.539..131.841 rows=16542 loops=1)
102.359	121.134	↓ 1.1	16542	1	→ HashAggregate (cost=1667.66..2084.01 rows=15140 width=18) (actual time=107.536..121.134 rows=16542 loops=1) Filter: (sum(((quantity)::numeric * foreignprice)) <> 0::numeric)
18.775	18.775	↑ 1.0	45466	1	→ Seq Scan on quotationitems (cost=0.00..1326.66 rows=45466 width=18) (actual time=0.004..18.775 rows=45466 loops=1)
10.533	26.906	↑ 1.0	17068	1	→ Hash (cost=1139.86..1139.86 rows=17086 width=12) (actual time=26.906..26.906 rows=17068 loops=1)
16.373	16.373	↑ 1.0	17068	1	→ Seq Scan on quotations d (cost=0.00..1139.86 rows=17086 width=12) (actual time=0.004..16.373 rows=17068 loops=1)
					SubPlan (forHash Join)
42.496	212.480	↑ 1.0	1	21248	→ Aggregate (cost=8.29..8.30 rows=1 width=11) (actual time=0.010..0.010 rows=1 loops=21248)
169.984	169.984	↓ 2.0	2	21248	→ Index Scan using p_quotationid on payments (cost=0.00..8.29 rows=1 width=11) (actual time=0.005..0.008 rows=2 loops=21248) Index Cond: (quotationid = $0) Filter: ((CASE WHEN (paymentway = 4) THEN chargedate ELSE actualpaymentdate END < CASE WHEN ($1 = 4) THEN $2 ELSE $3 END) OR ((CASE WHEN (paymentway = 4) THEN chargedate ELSE actualpaymentdate END = CASE WHEN ($1 = 4) THEN $2 ELSE $3 END) AND (paymentid <= $4)))
10.775	30.495	↑ 1.0	17012	1	→ Hash (cost=1139.86..1139.86 rows=17026 width=26) (actual time=30.495..30.495 rows=17012 loops=1)
19.720	19.720	↑ 1.0	17012	1	→ Seq Scan on quotations (cost=0.00..1139.86 rows=17026 width=26) (actual time=0.005..19.720 rows=17012 loops=1) Filter: (paymenttype IS NOT NULL)
29.513	785.693	↑ 1.1	35957	1	→ Sort (cost=340754.18..340851.66 rows=38994 width=12) (actual time=779.131..785.693 rows=35957 loops=1) Sort Key: d.quotationitemid
15.661	756.180	↑ 1.1	35957	1	→ Subquery Scan d (cost=336903.33..337780.70 rows=38994 width=12) (actual time=712.932..756.180 rows=35957 loops=1)
18.978	740.519	↑ 1.1	35957	1	→ Unique (cost=336903.33..337390.76 rows=38994 width=31) (actual time=712.930..740.519 rows=35957 loops=1)
50.040	721.541	↓ 1.0	39314	1	→ Sort (cost=336903.33..337000.82 rows=38994 width=31) (actual time=712.927..721.541 rows=39314 loops=1) Sort Key: a.quotationid, a.quotationitemid, a.quantity, (subplan)
102.715	671.501	↓ 1.0	39314	1	→ Hash Join (cost=7999.83..333929.85 rows=38994 width=31) (actual time=132.515..671.501 rows=39314 loops=1) Hash Cond: (b.quotationitemid = a.quotationitemid)
64.871	167.985	↓ 1.0	39318	1	→ Hash Join (cost=6104.85..7857.47 rows=38994 width=19) (actual time=85.439..167.985 rows=39318 loops=1) Hash Cond: (b.shipmentid = c.shipmentid)
17.704	17.704	↑ 1.0	39355	1	→ Seq Scan on shipmentitems b (cost=0.00..821.55 rows=39355 width=8) (actual time=0.006..17.704 rows=39355 loops=1)
12.533	85.410	↓ 1.0	19521	1	→ Hash (cost=5861.56..5861.56 rows=19463 width=19) (actual time=85.410..85.410 rows=19521 loops=1)
36.499	72.877	↓ 1.0	19521	1	→ Hash Join (cost=1353.43..5861.56 rows=19463 width=19) (actual time=20.154..72.877 rows=19521 loops=1) Hash Cond: (c.quotationid = d.quotationid)
16.239	16.239	↑ 1.0	19521	1	→ Seq Scan on shipments c (cost=0.00..4069.43 rows=19525 width=19) (actual time=0.003..16.239 rows=19521 loops=1) Filter: (actualshipmentdate IS NOT NULL)
8.391	20.139	↑ 1.0	17068	1	→ Hash (cost=1139.86..1139.86 rows=17086 width=8) (actual time=20.139..20.139 rows=17068 loops=1)
11.748	11.748	↑ 1.0	17068	1	→ Seq Scan on quotations d (cost=0.00..1139.86 rows=17086 width=8) (actual time=0.004..11.748 rows=17068 loops=1)
24.430	46.975	↑ 1.0	45466	1	→ Hash (cost=1326.66..1326.66 rows=45466 width=12) (actual time=46.975..46.975 rows=45466 loops=1)
22.545	22.545	↑ 1.0	45466	1	→ Seq Scan on quotationitems a (cost=0.00..1326.66 rows=45466 width=12) (actual time=0.004..22.545 rows=45466 loops=1)
					SubPlan (forHash Join)
117.942	353.826	↑ 1.0	1	39314	→ Aggregate (cost=8.28..8.29 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=39314)
82.684	235.884	↑ 1.0	1	39314	→ Result (cost=0.01..8.28 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=39314) One-Time Filter: (($6 = 2) OR ($7 = 'Invoice'::citext) OR ($7 = 'Packing List'::citext))
153.200	153.200	↑ 1.0	1	38300	→ Index Scan using si_quotationitemid on shipmentitems (cost=0.01..8.28 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=38300) Index Cond: (quotationitemid = $5)
1.978	1.978	↑ 1.0	4687	1	→ Index Scan using idx_assembliesbatch_qi on assembliesbatch ab (cost=0.00..565.35 rows=4687 width=28) (actual time=0.020..1.978 rows=4687 loops=1)
0.032	0.045	↑ 1.0	34	1	→ Sort (cost=2.20..2.29 rows=34 width=4) (actual time=0.038..0.045 rows=34 loops=1) Sort Key: o.quotationitemid
0.013	0.013	↑ 1.0	34	1	→ Seq Scan on productionrequest o (cost=0.00..1.34 rows=34 width=4) (actual time=0.003..0.013 rows=34 loops=1)

"Unique  (cost=547753.97..547796.58 rows=897 width=135) (actual time=2036.653..2037.002 rows=236 loops=1)"
"  ->  Sort  (cost=547753.97..547756.22 rows=897 width=135) (actual time=2036.652..2036.694 rows=240 loops=1)"
"        Sort Key: c.quotationitemid, b.quotationid, b.customerid, b.orderdate, b.orderno, b.userid, a.activationdate, c.quantity, (COALESCE(c.quantity, 0) - COALESCE(d.shippedtodate, 0::bigint)), c.itempn, CASE WHEN (c.deliverywks >= 14) THEN true ELSE c.custom_mfg END, (a.activationdate + (((COALESCE(c.deliverywks, 0))::text || ' weeks'::text))::interval), c.requested_mfg, max(ab.promisedby), c.confirmed_mfg, CASE WHEN ((max(ab.promisedby) IS NULL) OR (c.confirmed_mfg IS NULL)) THEN true ELSE (c.confirmed_mfg <= max(ab.promisedby)) END, CASE WHEN (c.confirmed_mfg IS NULL) THEN true ELSE CASE WHEN ((ab.entrydate + '7 days'::interval) >= ('now'::text)::date) THEN true ELSE CASE WHEN (((ab.entrydate + '28 days'::interval) >= ('now'::text)::date) AND (ab.productionreqdate IS NOT NULL)) THEN true ELSE (c.confirmed_mfg <= COALESCE(c.requested_mfg, (a.activationdate + (((c.deliverywks)::text || ' weeks'::text))::interval))) END END END, CASE WHEN (c.deliverywks >= 14) THEN getorderstatus(c.quotationitemid) ELSE NULL::citext END"
"        ->  GroupAggregate  (cost=547604.58..547709.98 rows=897 width=135) (actual time=2013.582..2036.275 rows=240 loops=1)"
"              ->  Sort  (cost=547604.58..547606.83 rows=897 width=135) (actual time=2013.002..2013.061 rows=243 loops=1)"
"                    Sort Key: c.requested_mfg, c.quotationitemid, b.quotationid, b.customerid, b.orderdate, b.orderno, b.userid, a.activationdate, c.quantity, (COALESCE(c.quantity, 0) - COALESCE(d.shippedtodate, 0::bigint)), c.itempn, CASE WHEN (c.deliverywks >= 14) THEN true ELSE c.custom_mfg END, c.deliverywks, c.confirmed_mfg, ab.entrydate, ab.productionreqdate"
"                    ->  Merge Left Join  (cost=547122.20..547560.59 rows=897 width=135) (actual time=2001.043..2012.551 rows=243 loops=1)"
"                          Merge Cond: (c.quotationitemid = o.quotationitemid)"
"                          ->  Merge Left Join  (cost=547120.00..547551.48 rows=897 width=135) (actual time=2000.998..2012.252 rows=243 loops=1)"
"                                Merge Cond: (c.quotationitemid = ab.quotationitemid)"
"                                ->  Merge Left Join  (cost=547120.00..547363.02 rows=897 width=111) (actual time=2000.730..2008.761 rows=236 loops=1)"
"                                      Merge Cond: (c.quotationitemid = d.quotationitemid)"
"                                      Filter: ((COALESCE(c.quantity, 0) - COALESCE(d.shippedtodate, 0::bigint)) > 0)"
"                                      ->  Sort  (cost=206365.82..206372.55 rows=2690 width=103) (actual time=1214.188..1214.261 rows=338 loops=1)"
"                                            Sort Key: c.quotationitemid"
"                                            ->  Hash Join  (cost=204494.67..206212.58 rows=2690 width=103) (actual time=1121.992..1213.781 rows=338 loops=1)"
"                                                  Hash Cond: (c.quotationid = b.quotationid)"
"                                                  ->  Seq Scan on quotationitems c  (cost=0.00..1553.99 rows=36538 width=66) (actual time=0.039..106.908 rows=25716 loops=1)"
"                                                        Filter: ((itempn !~~ '%shipping%'::citext) AND (quantity > 0))"
"                                                  ->  Hash  (cost=204478.85..204478.85 rows=1266 width=41) (actual time=1100.260..1100.260 rows=144 loops=1)"
"                                                        ->  Merge Left Join  (cost=204357.31..204478.85 rows=1266 width=41) (actual time=1096.694..1100.178 rows=144 loops=1)"
"                                                              Merge Cond: (b.quotationid = a.quotationid)"
"                                                              ->  Sort  (cost=1234.86..1237.47 rows=1043 width=33) (actual time=8.907..8.936 rows=144 loops=1)"
"                                                                    Sort Key: b.quotationid"
"                                                                    ->  Seq Scan on quotations b  (cost=0.00..1182.58 rows=1043 width=33) (actual time=0.110..8.743 rows=144 loops=1)"
"                                                                          Filter: (orderplaced AND (shipped <> ALL ('{2,5}'::integer[])))"
"                                                              ->  Sort  (cost=203122.45..203174.28 rows=20732 width=12) (actual time=1084.573..1087.413 rows=15783 loops=1)"
"                                                                    Sort Key: a.quotationid"
"                                                                    ->  Subquery Scan a  (cost=200547.58..201636.01 rows=20732 width=12) (actual time=1039.970..1079.136 rows=15783 loops=1)"
"                                                                          ->  GroupAggregate  (cost=200547.58..201428.69 rows=20732 width=42) (actual time=1039.968..1072.734 rows=15783 loops=1)"
"                                                                                ->  Sort  (cost=200547.58..200599.41 rows=20732 width=42) (actual time=1039.954..1044.324 rows=20484 loops=1)"
"                                                                                      Sort Key: a.quotationid, a.orderdate, "substring"(quotations.paymenttype, 2, (strpos((quotations.paymenttype)::text, 'o,'::text) - 2))"
"                                                                                      ->  Hash Join  (cost=195419.94..199061.14 rows=20732 width=42) (actual time=923.495..1024.388 rows=20484 loops=1)"
"                                                                                            Hash Cond: (a.quotationid = quotations.quotationid)"
"                                                                                            Join Filter: CASE WHEN (("substring"(quotations.paymenttype, 2, (strpos((quotations.paymenttype)::text, 'o,'::text) - 2)))::text = '0'::text) THEN true ELSE (b.paidtodate >= b.onorder_required) END"
"                                                                                            ->  Merge Left Join  (cost=194067.26..196826.43 rows=20805 width=84) (actual time=892.955..944.636 rows=21638 loops=1)"
"                                                                                                  Merge Cond: (a.quotationid = b.quotationid)"
"                                                                                                  ->  Index Scan using quotationid on quotations a  (cost=0.00..2401.58 rows=16359 width=12) (actual time=0.021..17.049 rows=16336 loops=1)"
"                                                                                                        Filter: orderplaced"
"                                                                                                  ->  Sort  (cost=194067.26..194121.58 rows=21730 width=76) (actual time=892.926..899.446 rows=21248 loops=1)"
"                                                                                                        Sort Key: b.quotationid"
"                                                                                                        ->  Subquery Scan b  (cost=7523.55..192501.89 rows=21730 width=76) (actual time=246.725..862.650 rows=21248 loops=1)"
"                                                                                                              ->  Hash Join  (cost=7523.55..192284.59 rows=21730 width=187) (actual time=246.723..847.337 rows=21248 loops=1)"
"                                                                                                                    Hash Cond: (a.quotationid = d.quotationid)"
"                                                                                                                    ->  Hash Left Join  (cost=6170.12..7701.80 rows=21820 width=179) (actual time=219.686..307.850 rows=21248 loops=1)"
"                                                                                                                          Hash Cond: (a.quotationid = quotations.quotationid)"
"                                                                                                                          Filter: (((("substring"(quotations.paymenttype, 2, (strpos((quotations.paymenttype)::text, 'o,'::text) - 2)))::text <> '0'::text) AND (CASE WHEN (a.paymentway = 4) THEN a.chargedate ELSE a.actualpaymentdate END IS NOT NULL)) OR (("substring"(quotations.paymenttype, 2, (strpos((quotations.paymenttype)::text, 'o,'::text) - 2)))::text = '0'::text))"
"                                                                                                                          ->  Seq Scan on payments a  (cost=0.00..545.39 rows=22039 width=125) (actual time=0.004..10.139 rows=22039 loops=1)"
"                                                                                                                          ->  Hash  (cost=5957.29..5957.29 rows=17026 width=58) (actual time=219.625..219.625 rows=17012 loops=1)"
"                                                                                                                                ->  Merge Left Join  (cost=3286.58..5957.29 rows=17026 width=58) (actual time=151.291..208.198 rows=17012 loops=1)"
"                                                                                                                                      Merge Cond: (quotations.quotationid = c.quotationid)"
"                                                                                                                                      ->  Index Scan using quotationid on quotations  (cost=0.00..2401.58 rows=17026 width=26) (actual time=0.021..28.063 rows=17012 loops=1)"
"                                                                                                                                            Filter: (paymenttype IS NOT NULL)"
"                                                                                                                                      ->  Sort  (cost=3286.58..3324.43 rows=15140 width=36) (actual time=151.261..156.963 rows=16542 loops=1)"
"                                                                                                                                            Sort Key: c.quotationid"
"                                                                                                                                            ->  Subquery Scan c  (cost=1667.66..2235.41 rows=15140 width=36) (actual time=107.539..131.841 rows=16542 loops=1)"
"                                                                                                                                                  ->  HashAggregate  (cost=1667.66..2084.01 rows=15140 width=18) (actual time=107.536..121.134 rows=16542 loops=1)"
"                                                                                                                                                        Filter: (sum(((quantity)::numeric * foreignprice)) <> 0::numeric)"
"                                                                                                                                                        ->  Seq Scan on quotationitems  (cost=0.00..1326.66 rows=45466 width=18) (actual time=0.004..18.775 rows=45466 loops=1)"
"                                                                                                                    ->  Hash  (cost=1139.86..1139.86 rows=17086 width=12) (actual time=26.906..26.906 rows=17068 loops=1)"
"                                                                                                                          ->  Seq Scan on quotations d  (cost=0.00..1139.86 rows=17086 width=12) (actual time=0.004..16.373 rows=17068 loops=1)"
"                                                                                                                    SubPlan"
"                                                                                                                      ->  Aggregate  (cost=8.29..8.30 rows=1 width=11) (actual time=0.010..0.010 rows=1 loops=21248)"
"                                                                                                                            ->  Index Scan using p_quotationid on payments  (cost=0.00..8.29 rows=1 width=11) (actual time=0.005..0.008 rows=2 loops=21248)"
"                                                                                                                                  Index Cond: (quotationid = $0)"
"                                                                                                                                  Filter: ((CASE WHEN (paymentway = 4) THEN chargedate ELSE actualpaymentdate END < CASE WHEN ($1 = 4) THEN $2 ELSE $3 END) OR ((CASE WHEN (paymentway = 4) THEN chargedate ELSE actualpaymentdate END = CASE WHEN ($1 = 4) THEN $2 ELSE $3 END) AND (paymentid <= $4)))"
"                                                                                            ->  Hash  (cost=1139.86..1139.86 rows=17026 width=26) (actual time=30.495..30.495 rows=17012 loops=1)"
"                                                                                                  ->  Seq Scan on quotations  (cost=0.00..1139.86 rows=17026 width=26) (actual time=0.005..19.720 rows=17012 loops=1)"
"                                                                                                        Filter: (paymenttype IS NOT NULL)"
"                                      ->  Sort  (cost=340754.18..340851.66 rows=38994 width=12) (actual time=779.131..785.693 rows=35957 loops=1)"
"                                            Sort Key: d.quotationitemid"
"                                            ->  Subquery Scan d  (cost=336903.33..337780.70 rows=38994 width=12) (actual time=712.932..756.180 rows=35957 loops=1)"
"                                                  ->  Unique  (cost=336903.33..337390.76 rows=38994 width=31) (actual time=712.930..740.519 rows=35957 loops=1)"
"                                                        ->  Sort  (cost=336903.33..337000.82 rows=38994 width=31) (actual time=712.927..721.541 rows=39314 loops=1)"
"                                                              Sort Key: a.quotationid, a.quotationitemid, a.quantity, (subplan)"
"                                                              ->  Hash Join  (cost=7999.83..333929.85 rows=38994 width=31) (actual time=132.515..671.501 rows=39314 loops=1)"
"                                                                    Hash Cond: (b.quotationitemid = a.quotationitemid)"
"                                                                    ->  Hash Join  (cost=6104.85..7857.47 rows=38994 width=19) (actual time=85.439..167.985 rows=39318 loops=1)"
"                                                                          Hash Cond: (b.shipmentid = c.shipmentid)"
"                                                                          ->  Seq Scan on shipmentitems b  (cost=0.00..821.55 rows=39355 width=8) (actual time=0.006..17.704 rows=39355 loops=1)"
"                                                                          ->  Hash  (cost=5861.56..5861.56 rows=19463 width=19) (actual time=85.410..85.410 rows=19521 loops=1)"
"                                                                                ->  Hash Join  (cost=1353.43..5861.56 rows=19463 width=19) (actual time=20.154..72.877 rows=19521 loops=1)"
"                                                                                      Hash Cond: (c.quotationid = d.quotationid)"
"                                                                                      ->  Seq Scan on shipments c  (cost=0.00..4069.43 rows=19525 width=19) (actual time=0.003..16.239 rows=19521 loops=1)"
"                                                                                            Filter: (actualshipmentdate IS NOT NULL)"
"                                                                                      ->  Hash  (cost=1139.86..1139.86 rows=17086 width=8) (actual time=20.139..20.139 rows=17068 loops=1)"
"                                                                                            ->  Seq Scan on quotations d  (cost=0.00..1139.86 rows=17086 width=8) (actual time=0.004..11.748 rows=17068 loops=1)"
"                                                                    ->  Hash  (cost=1326.66..1326.66 rows=45466 width=12) (actual time=46.975..46.975 rows=45466 loops=1)"
"                                                                          ->  Seq Scan on quotationitems a  (cost=0.00..1326.66 rows=45466 width=12) (actual time=0.004..22.545 rows=45466 loops=1)"
"                                                                    SubPlan"
"                                                                      ->  Aggregate  (cost=8.28..8.29 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=39314)"
"                                                                            ->  Result  (cost=0.01..8.28 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=39314)"
"                                                                                  One-Time Filter: (($6 = 2) OR ($7 = 'Invoice'::citext) OR ($7 = 'Packing List'::citext))"
"                                                                                  ->  Index Scan using si_quotationitemid on shipmentitems  (cost=0.01..8.28 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=38300)"
"                                                                                        Index Cond: (quotationitemid = $5)"
"                                ->  Index Scan using idx_assembliesbatch_qi on assembliesbatch ab  (cost=0.00..565.35 rows=4687 width=28) (actual time=0.020..1.978 rows=4687 loops=1)"
"                          ->  Sort  (cost=2.20..2.29 rows=34 width=4) (actual time=0.038..0.045 rows=34 loops=1)"
"                                Sort Key: o.quotationitemid"
"                                ->  Seq Scan on productionrequest o  (cost=0.00..1.34 rows=34 width=4) (actual time=0.003..0.013 rows=34 loops=1)"
"Total runtime: 2038.238 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	160.438 ms	7.9 %
GroupAggregate	2	51.624 ms	2.5 %
Hash	7	78.171 ms	3.8 %
Hash Join	6	560.056 ms	27.5 %
Hash Left Join	1	78.086 ms	3.8 %
HashAggregate	1	102.359 ms	5.0 %
Index Scan	5	370.274 ms	18.2 %
Merge Left Join	6	65.716 ms	3.2 %
Result	1	82.684 ms	4.1 %
Seq Scan	11	248.907 ms	12.2 %
Sort	11	171.318 ms	8.4 %
Subquery Scan a	1	6.402 ms	0.3 %
Subquery Scan b	1	15.313 ms	0.8 %
Subquery Scan c	1	10.707 ms	0.5 %
Subquery Scan d	1	15.661 ms	0.8 %
Unique	2	19.286 ms	0.9 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
assembliesbatch	1	1.978 ms	0.1 %
Index Scan	1	1.978 ms	100.0 %
payments	2	180.123 ms	8.8 %
Index Scan	1	169.984 ms	94.4 %
Seq Scan	1	10.139 ms	5.6 %
productionrequest	1	0.013 ms	0.0 %
Seq Scan	1	0.013 ms	100.0 %
quotationitems	3	148.228 ms	7.3 %
Seq Scan	3	148.228 ms	100.0 %
quotations	6	101.696 ms	5.0 %
Index Scan	2	45.112 ms	44.4 %
Seq Scan	4	56.584 ms	55.6 %
shipmentitems	2	170.904 ms	8.4 %
Index Scan	1	153.200 ms	89.6 %
Seq Scan	1	17.704 ms	10.4 %
shipments	1	16.239 ms	0.8 %
Seq Scan	1	16.239 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: wcQ

Colorize:

Columns visibility:

Per node type stats

Per table stats