Result: fOyc

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
0.285	1468.146	↑ 3.8	236	1	→ Unique (cost=547753.97..547796.58 rows=897 width=135) (actual time=1467.818..1468.146 rows=236 loops=1)
0.350	1467.861	↑ 3.7	240	1	→ Sort (cost=547753.97..547756.22 rows=897 width=135) (actual time=1467.817..1467.861 rows=240 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, (a.activationdate + (((COALESCE(c.deliverywks, 0))::text \|\| ' weeks'::text))::interval), 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
22.749	1467.511	↑ 3.7	240	1	→ GroupAggregate (cost=547604.58..547709.98 rows=897 width=135) (actual time=1445.277..1467.511 rows=240 loops=1)
0.518	1444.762	↑ 3.7	243	1	→ Sort (cost=547604.58..547606.83 rows=897 width=135) (actual time=1444.691..1444.762 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.245	1444.244	↑ 3.7	243	1	→ Merge Left Join (cost=547122.20..547560.59 rows=897 width=135) (actual time=1432.908..1444.244 rows=243 loops=1) Merge Cond: (c.quotationitemid = o.quotationitemid)
1.468	1443.957	↑ 3.7	243	1	→ Merge Left Join (cost=547120.00..547551.48 rows=897 width=135) (actual time=1432.865..1443.957 rows=243 loops=1) Merge Cond: (c.quotationitemid = ab.quotationitemid)
8.701	1440.554	↑ 3.8	236	1	→ Merge Left Join (cost=547120.00..547363.02 rows=897 width=111) (actual time=1432.603..1440.554 rows=236 loops=1) Merge Cond: (c.quotationitemid = d.quotationitemid) Filter: ((COALESCE(c.quantity, 0) - COALESCE(d.shippedtodate, 0::bigint)) > 0)
0.413	874.460	↑ 8.0	338	1	→ Sort (cost=206365.82..206372.55 rows=2690 width=103) (actual time=874.375..874.460 rows=338 loops=1) Sort Key: c.quotationitemid
6.112	874.047	↑ 8.0	338	1	→ Hash Join (cost=204494.67..206212.58 rows=2690 width=103) (actual time=789.809..874.047 rows=338 loops=1) Hash Cond: (c.quotationid = b.quotationid)
100.008	100.008	↑ 1.4	25716	1	→ Seq Scan on quotationitems c (cost=0.00..1553.99 rows=36538 width=66) (actual time=0.028..100.008 rows=25716 loops=1) Filter: ((itempn !~~ '%shipping%'::citext) AND (quantity > 0))
0.078	767.927	↑ 8.8	144	1	→ Hash (cost=204478.85..204478.85 rows=1266 width=41) (actual time=767.927..767.927 rows=144 loops=1)
3.831	767.849	↑ 8.8	144	1	→ Merge Left Join (cost=204357.31..204478.85 rows=1266 width=41) (actual time=764.344..767.849 rows=144 loops=1) Merge Cond: (b.quotationid = a.quotationid)
0.149	6.204	↑ 7.2	144	1	→ Sort (cost=1234.86..1237.47 rows=1043 width=33) (actual time=6.176..6.204 rows=144 loops=1) Sort Key: b.quotationid
6.055	6.055	↑ 7.2	144	1	→ Seq Scan on quotations b (cost=0.00..1182.58 rows=1043 width=33) (actual time=0.074..6.055 rows=144 loops=1) Filter: (orderplaced AND (shipped <> ALL ('{2,5}'::integer[])))
8.522	757.814	↑ 1.3	15783	1	→ Sort (cost=203122.45..203174.28 rows=20732 width=12) (actual time=754.947..757.814 rows=15783 loops=1) Sort Key: a.quotationid
6.656	749.292	↑ 1.3	15783	1	→ Subquery Scan a (cost=200547.58..201636.01 rows=20732 width=12) (actual time=708.565..749.292 rows=15783 loops=1)
29.437	742.636	↑ 1.3	15783	1	→ GroupAggregate (cost=200547.58..201428.69 rows=20732 width=42) (actual time=708.563..742.636 rows=15783 loops=1)
18.708	713.199	↑ 1.0	20484	1	→ Sort (cost=200547.58..200599.41 rows=20732 width=42) (actual time=708.547..713.199 rows=20484 loops=1) Sort Key: a.quotationid, a.orderdate, "substring"(quotations.paymenttype, 2, (strpos((quotations.paymenttype)::text, 'o,'::text) - 2))
42.750	694.491	↑ 1.0	20484	1	→ Hash Join (cost=195419.94..199061.14 rows=20732 width=42) (actual time=604.221..694.491 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
24.730	631.752	↓ 1.0	21638	1	→ Merge Left Join (cost=194067.26..196826.43 rows=20805 width=84) (actual time=584.188..631.752 rows=21638 loops=1) Merge Cond: (a.quotationid = b.quotationid)
16.918	16.918	↑ 1.0	16336	1	→ Index Scan using quotationid on quotations a (cost=0.00..2401.58 rows=16359 width=12) (actual time=0.018..16.918 rows=16336 loops=1) Filter: orderplaced
25.347	590.104	↑ 1.0	21248	1	→ Sort (cost=194067.26..194121.58 rows=21730 width=76) (actual time=584.160..590.104 rows=21248 loops=1) Sort Key: b.quotationid
10.394	564.757	↑ 1.0	21248	1	→ Subquery Scan b (cost=7523.55..192501.89 rows=21730 width=76) (actual time=153.948..564.757 rows=21248 loops=1)
195.285	554.363	↑ 1.0	21248	1	→ Hash Join (cost=7523.55..192284.59 rows=21730 width=187) (actual time=153.946..554.363 rows=21248 loops=1) Hash Cond: (a.quotationid = d.quotationid)
49.651	192.750	↑ 1.0	21248	1	→ Hash Left Join (cost=6170.12..7701.80 rows=21820 width=179) (actual time=136.256..192.750 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))
6.892	6.892	↑ 1.0	22039	1	→ Seq Scan on payments a (cost=0.00..545.39 rows=22039 width=125) (actual time=0.005..6.892 rows=22039 loops=1)
7.876	136.207	↑ 1.0	17012	1	→ Hash (cost=5957.29..5957.29 rows=17026 width=58) (actual time=136.207..136.207 rows=17012 loops=1)
15.373	128.331	↑ 1.0	17012	1	→ Merge Left Join (cost=3286.58..5957.29 rows=17026 width=58) (actual time=90.024..128.331 rows=17012 loops=1) Merge Cond: (quotations.quotationid = c.quotationid)
18.943	18.943	↑ 1.0	17012	1	→ Index Scan using quotationid on quotations (cost=0.00..2401.58 rows=17026 width=26) (actual time=0.016..18.943 rows=17012 loops=1) Filter: (paymenttype IS NOT NULL)
15.521	94.015	↓ 1.1	16542	1	→ Sort (cost=3286.58..3324.43 rows=15140 width=36) (actual time=89.999..94.015 rows=16542 loops=1) Sort Key: c.quotationid
6.591	78.494	↓ 1.1	16542	1	→ Subquery Scan c (cost=1667.66..2235.41 rows=15140 width=36) (actual time=63.861..78.494 rows=16542 loops=1)
59.852	71.903	↓ 1.1	16542	1	→ HashAggregate (cost=1667.66..2084.01 rows=15140 width=18) (actual time=63.860..71.903 rows=16542 loops=1) Filter: (sum(((quantity)::numeric * foreignprice)) <> 0::numeric)
12.051	12.051	↑ 1.0	45466	1	→ Seq Scan on quotationitems (cost=0.00..1326.66 rows=45466 width=18) (actual time=0.004..12.051 rows=45466 loops=1)
6.436	17.592	↑ 1.0	17068	1	→ Hash (cost=1139.86..1139.86 rows=17086 width=12) (actual time=17.592..17.592 rows=17068 loops=1)
11.156	11.156	↑ 1.0	17068	1	→ Seq Scan on quotations d (cost=0.00..1139.86 rows=17086 width=12) (actual time=0.006..11.156 rows=17068 loops=1)
					SubPlan (forHash Join)
42.496	148.736	↑ 1.0	1	21248	→ Aggregate (cost=8.29..8.30 rows=1 width=11) (actual time=0.007..0.007 rows=1 loops=21248)
106.240	106.240	↓ 2.0	2	21248	→ Index Scan using p_quotationid on payments (cost=0.00..8.29 rows=1 width=11) (actual time=0.003..0.005 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)))
6.722	19.989	↑ 1.0	17012	1	→ Hash (cost=1139.86..1139.86 rows=17026 width=26) (actual time=19.989..19.989 rows=17012 loops=1)
13.267	13.267	↑ 1.0	17012	1	→ Seq Scan on quotations (cost=0.00..1139.86 rows=17026 width=26) (actual time=0.005..13.267 rows=17012 loops=1) Filter: (paymenttype IS NOT NULL)
30.395	557.393	↑ 1.1	35957	1	→ Sort (cost=340754.18..340851.66 rows=38994 width=12) (actual time=550.915..557.393 rows=35957 loops=1) Sort Key: d.quotationitemid
16.434	526.998	↑ 1.1	35957	1	→ Subquery Scan d (cost=336903.33..337780.70 rows=38994 width=12) (actual time=480.665..526.998 rows=35957 loops=1)
20.514	510.564	↑ 1.1	35957	1	→ Unique (cost=336903.33..337390.76 rows=38994 width=31) (actual time=480.663..510.564 rows=35957 loops=1)
43.697	490.050	↓ 1.0	39314	1	→ Sort (cost=336903.33..337000.82 rows=38994 width=31) (actual time=480.661..490.050 rows=39314 loops=1) Sort Key: a.quotationid, a.quotationitemid, a.quantity, (subplan)
65.068	446.353	↓ 1.0	39314	1	→ Hash Join (cost=7999.83..333929.85 rows=38994 width=31) (actual time=91.472..446.353 rows=39314 loops=1) Hash Cond: (b.quotationitemid = a.quotationitemid)
41.850	111.512	↓ 1.0	39318	1	→ Hash Join (cost=6104.85..7857.47 rows=38994 width=19) (actual time=57.530..111.512 rows=39318 loops=1) Hash Cond: (b.shipmentid = c.shipmentid)
12.158	12.158	↑ 1.0	39355	1	→ Seq Scan on shipmentitems b (cost=0.00..821.55 rows=39355 width=8) (actual time=0.006..12.158 rows=39355 loops=1)
7.744	57.504	↓ 1.0	19521	1	→ Hash (cost=5861.56..5861.56 rows=19463 width=19) (actual time=57.504..57.504 rows=19521 loops=1)
23.761	49.760	↓ 1.0	19521	1	→ Hash Join (cost=1353.43..5861.56 rows=19463 width=19) (actual time=15.327..49.760 rows=19521 loops=1) Hash Cond: (c.quotationid = d.quotationid)
10.688	10.688	↑ 1.0	19521	1	→ Seq Scan on shipments c (cost=0.00..4069.43 rows=19525 width=19) (actual time=0.003..10.688 rows=19521 loops=1) Filter: (actualshipmentdate IS NOT NULL)
6.210	15.311	↑ 1.0	17068	1	→ Hash (cost=1139.86..1139.86 rows=17086 width=8) (actual time=15.311..15.311 rows=17068 loops=1)
9.101	9.101	↑ 1.0	17068	1	→ Seq Scan on quotations d (cost=0.00..1139.86 rows=17086 width=8) (actual time=0.005..9.101 rows=17068 loops=1)
17.426	33.889	↑ 1.0	45466	1	→ Hash (cost=1326.66..1326.66 rows=45466 width=12) (actual time=33.889..33.889 rows=45466 loops=1)
16.463	16.463	↑ 1.0	45466	1	→ Seq Scan on quotationitems a (cost=0.00..1326.66 rows=45466 width=12) (actual time=0.005..16.463 rows=45466 loops=1)
					SubPlan (forHash Join)
78.628	235.884	↑ 1.0	1	39314	→ Aggregate (cost=8.28..8.29 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=39314)
42.356	157.256	↑ 1.0	1	39314	→ Result (cost=0.01..8.28 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=39314) One-Time Filter: (($6 = 2) OR ($7 = 'Invoice'::citext) OR ($7 = 'Packing List'::citext))
114.900	114.900	↑ 1.0	1	38300	→ Index Scan using si_quotationitemid on shipmentitems (cost=0.01..8.28 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=38300) Index Cond: (quotationitemid = $5)
1.935	1.935	↑ 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.019..1.935 rows=4687 loops=1)
0.026	0.042	↑ 1.0	34	1	→ Sort (cost=2.20..2.29 rows=34 width=4) (actual time=0.036..0.042 rows=34 loops=1) Sort Key: o.quotationitemid
0.016	0.016	↑ 1.0	34	1	→ Seq Scan on productionrequest o (cost=0.00..1.34 rows=34 width=4) (actual time=0.004..0.016 rows=34 loops=1)

"Unique  (cost=547753.97..547796.58 rows=897 width=135) (actual time=1467.818..1468.146 rows=236 loops=1)"
"  ->  Sort  (cost=547753.97..547756.22 rows=897 width=135) (actual time=1467.817..1467.861 rows=240 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, (a.activationdate + (((COALESCE(c.deliverywks, 0))::text || ' weeks'::text))::interval), 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=1445.277..1467.511 rows=240 loops=1)"
"              ->  Sort  (cost=547604.58..547606.83 rows=897 width=135) (actual time=1444.691..1444.762 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=1432.908..1444.244 rows=243 loops=1)"
"                          Merge Cond: (c.quotationitemid = o.quotationitemid)"
"                          ->  Merge Left Join  (cost=547120.00..547551.48 rows=897 width=135) (actual time=1432.865..1443.957 rows=243 loops=1)"
"                                Merge Cond: (c.quotationitemid = ab.quotationitemid)"
"                                ->  Merge Left Join  (cost=547120.00..547363.02 rows=897 width=111) (actual time=1432.603..1440.554 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=874.375..874.460 rows=338 loops=1)"
"                                            Sort Key: c.quotationitemid"
"                                            ->  Hash Join  (cost=204494.67..206212.58 rows=2690 width=103) (actual time=789.809..874.047 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.028..100.008 rows=25716 loops=1)"
"                                                        Filter: ((itempn !~~ '%shipping%'::citext) AND (quantity > 0))"
"                                                  ->  Hash  (cost=204478.85..204478.85 rows=1266 width=41) (actual time=767.927..767.927 rows=144 loops=1)"
"                                                        ->  Merge Left Join  (cost=204357.31..204478.85 rows=1266 width=41) (actual time=764.344..767.849 rows=144 loops=1)"
"                                                              Merge Cond: (b.quotationid = a.quotationid)"
"                                                              ->  Sort  (cost=1234.86..1237.47 rows=1043 width=33) (actual time=6.176..6.204 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.074..6.055 rows=144 loops=1)"
"                                                                          Filter: (orderplaced AND (shipped <> ALL ('{2,5}'::integer[])))"
"                                                              ->  Sort  (cost=203122.45..203174.28 rows=20732 width=12) (actual time=754.947..757.814 rows=15783 loops=1)"
"                                                                    Sort Key: a.quotationid"
"                                                                    ->  Subquery Scan a  (cost=200547.58..201636.01 rows=20732 width=12) (actual time=708.565..749.292 rows=15783 loops=1)"
"                                                                          ->  GroupAggregate  (cost=200547.58..201428.69 rows=20732 width=42) (actual time=708.563..742.636 rows=15783 loops=1)"
"                                                                                ->  Sort  (cost=200547.58..200599.41 rows=20732 width=42) (actual time=708.547..713.199 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=604.221..694.491 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=584.188..631.752 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.018..16.918 rows=16336 loops=1)"
"                                                                                                        Filter: orderplaced"
"                                                                                                  ->  Sort  (cost=194067.26..194121.58 rows=21730 width=76) (actual time=584.160..590.104 rows=21248 loops=1)"
"                                                                                                        Sort Key: b.quotationid"
"                                                                                                        ->  Subquery Scan b  (cost=7523.55..192501.89 rows=21730 width=76) (actual time=153.948..564.757 rows=21248 loops=1)"
"                                                                                                              ->  Hash Join  (cost=7523.55..192284.59 rows=21730 width=187) (actual time=153.946..554.363 rows=21248 loops=1)"
"                                                                                                                    Hash Cond: (a.quotationid = d.quotationid)"
"                                                                                                                    ->  Hash Left Join  (cost=6170.12..7701.80 rows=21820 width=179) (actual time=136.256..192.750 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.005..6.892 rows=22039 loops=1)"
"                                                                                                                          ->  Hash  (cost=5957.29..5957.29 rows=17026 width=58) (actual time=136.207..136.207 rows=17012 loops=1)"
"                                                                                                                                ->  Merge Left Join  (cost=3286.58..5957.29 rows=17026 width=58) (actual time=90.024..128.331 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.016..18.943 rows=17012 loops=1)"
"                                                                                                                                            Filter: (paymenttype IS NOT NULL)"
"                                                                                                                                      ->  Sort  (cost=3286.58..3324.43 rows=15140 width=36) (actual time=89.999..94.015 rows=16542 loops=1)"
"                                                                                                                                            Sort Key: c.quotationid"
"                                                                                                                                            ->  Subquery Scan c  (cost=1667.66..2235.41 rows=15140 width=36) (actual time=63.861..78.494 rows=16542 loops=1)"
"                                                                                                                                                  ->  HashAggregate  (cost=1667.66..2084.01 rows=15140 width=18) (actual time=63.860..71.903 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..12.051 rows=45466 loops=1)"
"                                                                                                                    ->  Hash  (cost=1139.86..1139.86 rows=17086 width=12) (actual time=17.592..17.592 rows=17068 loops=1)"
"                                                                                                                          ->  Seq Scan on quotations d  (cost=0.00..1139.86 rows=17086 width=12) (actual time=0.006..11.156 rows=17068 loops=1)"
"                                                                                                                    SubPlan"
"                                                                                                                      ->  Aggregate  (cost=8.29..8.30 rows=1 width=11) (actual time=0.007..0.007 rows=1 loops=21248)"
"                                                                                                                            ->  Index Scan using p_quotationid on payments  (cost=0.00..8.29 rows=1 width=11) (actual time=0.003..0.005 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=19.989..19.989 rows=17012 loops=1)"
"                                                                                                  ->  Seq Scan on quotations  (cost=0.00..1139.86 rows=17026 width=26) (actual time=0.005..13.267 rows=17012 loops=1)"
"                                                                                                        Filter: (paymenttype IS NOT NULL)"
"                                      ->  Sort  (cost=340754.18..340851.66 rows=38994 width=12) (actual time=550.915..557.393 rows=35957 loops=1)"
"                                            Sort Key: d.quotationitemid"
"                                            ->  Subquery Scan d  (cost=336903.33..337780.70 rows=38994 width=12) (actual time=480.665..526.998 rows=35957 loops=1)"
"                                                  ->  Unique  (cost=336903.33..337390.76 rows=38994 width=31) (actual time=480.663..510.564 rows=35957 loops=1)"
"                                                        ->  Sort  (cost=336903.33..337000.82 rows=38994 width=31) (actual time=480.661..490.050 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=91.472..446.353 rows=39314 loops=1)"
"                                                                    Hash Cond: (b.quotationitemid = a.quotationitemid)"
"                                                                    ->  Hash Join  (cost=6104.85..7857.47 rows=38994 width=19) (actual time=57.530..111.512 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..12.158 rows=39355 loops=1)"
"                                                                          ->  Hash  (cost=5861.56..5861.56 rows=19463 width=19) (actual time=57.504..57.504 rows=19521 loops=1)"
"                                                                                ->  Hash Join  (cost=1353.43..5861.56 rows=19463 width=19) (actual time=15.327..49.760 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..10.688 rows=19521 loops=1)"
"                                                                                            Filter: (actualshipmentdate IS NOT NULL)"
"                                                                                      ->  Hash  (cost=1139.86..1139.86 rows=17086 width=8) (actual time=15.311..15.311 rows=17068 loops=1)"
"                                                                                            ->  Seq Scan on quotations d  (cost=0.00..1139.86 rows=17086 width=8) (actual time=0.005..9.101 rows=17068 loops=1)"
"                                                                    ->  Hash  (cost=1326.66..1326.66 rows=45466 width=12) (actual time=33.889..33.889 rows=45466 loops=1)"
"                                                                          ->  Seq Scan on quotationitems a  (cost=0.00..1326.66 rows=45466 width=12) (actual time=0.005..16.463 rows=45466 loops=1)"
"                                                                    SubPlan"
"                                                                      ->  Aggregate  (cost=8.28..8.29 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=39314)"
"                                                                            ->  Result  (cost=0.01..8.28 rows=1 width=4) (actual time=0.003..0.004 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.002..0.003 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.019..1.935 rows=4687 loops=1)"
"                          ->  Sort  (cost=2.20..2.29 rows=34 width=4) (actual time=0.036..0.042 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.004..0.016 rows=34 loops=1)"
"Total runtime: 1469.334 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	121.124 ms	8.3 %
GroupAggregate	2	52.186 ms	3.6 %
Hash	7	52.492 ms	3.6 %
Hash Join	6	374.826 ms	25.5 %
Hash Left Join	1	49.651 ms	3.4 %
HashAggregate	1	59.852 ms	4.1 %
Index Scan	5	258.936 ms	17.6 %
Merge Left Join	6	54.348 ms	3.7 %
Result	1	42.356 ms	2.9 %
Seq Scan	11	197.855 ms	13.5 %
Sort	11	143.646 ms	9.8 %
Subquery Scan a	1	6.656 ms	0.5 %
Subquery Scan b	1	10.394 ms	0.7 %
Subquery Scan c	1	6.591 ms	0.4 %
Subquery Scan d	1	16.434 ms	1.1 %
Unique	2	20.799 ms	1.4 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
assembliesbatch	1	1.935 ms	0.1 %
Index Scan	1	1.935 ms	100.0 %
payments	2	113.132 ms	7.7 %
Index Scan	1	106.240 ms	93.9 %
Seq Scan	1	6.892 ms	6.1 %
productionrequest	1	0.016 ms	0.0 %
Seq Scan	1	0.016 ms	100.0 %
quotationitems	3	128.522 ms	8.8 %
Seq Scan	3	128.522 ms	100.0 %
quotations	6	75.440 ms	5.1 %
Index Scan	2	35.861 ms	47.5 %
Seq Scan	4	39.579 ms	52.5 %
shipmentitems	2	127.058 ms	8.7 %
Index Scan	1	114.900 ms	90.4 %
Seq Scan	1	12.158 ms	9.6 %
shipments	1	10.688 ms	0.7 %
Seq Scan	1	10.688 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: fOyc

Colorize:

Columns visibility:

Per node type stats

Per table stats