QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Sort (cost=139977.28..139988.20 rows=4371 width=445) (actual time=45313.210..45342.883 rows=4851 loops=1) Sort Key: periodof, name, name, recno, purchaseordhdrrecno -> Unique (cost=138459.17..139180.39 rows=4371 width=445) (actual time=44610.454..45113.344 rows=4851 loops=1) -> Sort (cost=138459.17..138470.10 rows=4371 width=445) (actual time=44610.438..44645.031 rows=4851 loops=1) Sort Key: name, name, periodof, recno, purchaseordhdrrecno, recno, recno, recno, replacementcost, requiredqty, recno, productrecno, supplierrecno, replacementcost, preferredsupplier, supplierreference, rrp, vatrecno, stockqty, leadtime, stockstatus, stockupdatedate, stockupdatetime, activeflag, effectivedate, redundantdate, fixprice, ptypelookupcodesrecno, recno, servicecobranchrecno, manufacturerreference, l1productleveldetrecno, l2productleveldetrecno, l3productleveldetrecno, effectivedate, inuseflag, redundantdate, manufacturerbarcode, defaultproductflag, pcatlookupcodesrecno, ismarkuppable, vatrecno, recno, servicecorecno, code, description, rate, effectivedate, redundantdate, orderedqty, recno, servicecobranchrecno, foreigntablename, foreignrecno, servicetyperecno, percentage, delegatedauthorityflag, delegatedauthoritylimit, partialvatflag, inspectioncharge1, inspectioncharge1limit, inspectioncharge2, collectioncharge, inspectionvatrecno, collectionvatrecno -> Append (cost=49434.60..137662.28 rows=4371 width=445) (actual time=31592.443..44089.392 rows=4851 loops=1) -> Subquery Scan "*SELECT* 1" (cost=49434.60..97672.96 rows=4370 width=445) (actual time=31592.433..43673.893 rows=4840 loops=1) -> Hash Join (cost=49434.60..97629.26 rows=4370 width=445) (actual time=31592.346..43225.407 rows=4840 loops=1) Hash Cond: ("outer".recno = "inner".sourcedetupgrrecno) -> Hash Join (cost=48991.71..97002.78 rows=12896 width=427) (actual time=31170.958..41626.550 rows=12874 loops=1) Hash Cond: ("outer".supplierproductrecno = "inner".recno) -> Hash Join (cost=59.06..746.57 rows=12895 width=195) (actual time=33.120..401.284 rows=12874 loops=1) Hash Cond: ("outer".sourcedetrecno = "inner".foreignrecno) -> Seq Scan on sourcedetupgr srcu (cost=0.00..429.18 rows=12938 width=32) (actual time=0.081..90.306 rows=12874 loops=1) Filter: (linestatus <> 'V'::bpchar) -> Hash (cost=57.79..57.79 rows=507 width=171) (actual time=32.945..32.945 rows=0 loops=1) -> Hash Join (cost=27.80..57.79 rows=507 width=171) (actual time=7.464..29.913 rows=507 loops=1) Hash Cond: ("outer".foreignrecno = "inner".recno) -> Hash Join (cost=3.92..26.31 rows=507 width=167) (actual time=1.195..14.050 rows=507 loops=1) Hash Cond: ("outer".ownerforeignrecno = "inner".recno) -> Seq Scan on sourcedetextref srce (cost=0.00..14.79 rows=507 width=8) (actual time=0.058..3.490 rows=507 loops=1) Filter: (((tablename)::text = 'sourcedet'::text) AND ((ownerforeigntablename)::text = 'clientbranch'::text)) -> Hash (cost=3.89..3.89 rows=14 width=163) (actual time=1.066..1.066 rows=0 loops=1) -> Hash Join (cost=2.30..3.89 rows=14 width=163) (actual time=0.538..0.973 rows=14 loops=1) Hash Cond: ("outer".recno = "inner".clientrecno) -> Hash Join (cost=1.12..2.40 rows=10 width=136) (actual time=0.256..0.477 rows=10 loops=1) Hash Cond: ("outer".foreignrecno = "inner".recno) -> Seq Scan on markup mrkup (cost=0.00..1.12 rows=10 width=109) (actual time=0.022..0.083 rows=10 loops=1) Filter: ((foreigntablename)::text = 'client'::text) -> Hash (cost=1.10..1.10 rows=10 width=27) (actual time=0.171..0.171 rows=0 loops=1) -> Seq Scan on client clnt (cost=0.00..1.10 rows=10 width=27) (actual time=0.041..0.096 rows=10 loops=1) -> Hash (cost=1.14..1.14 rows=14 width=31) (actual time=0.232..0.232 rows=0 loops=1) -> Seq Scan on clientbranch clntb (cost=0.00..1.14 rows=14 width=31) (actual time=0.074..0.158 rows=14 loops=1) -> Hash (cost=22.61..22.61 rows=507 width=4) (actual time=6.213..6.213 rows=0 loops=1) -> Seq Scan on sourcedet srcd (cost=0.00..22.61 rows=507 width=4) (actual time=0.105..3.614 rows=507 loops=1) Filter: ((actionstatus <> 'V'::bpchar) AND (servicecobranchrecno = 2)) -> Hash (cost=40069.62..40069.62 rows=255210 width=236) (actual time=31104.791..31104.791 rows=0 loops=1) -> Hash Join (cost=10429.15..40069.62 rows=255210 width=236) (actual time=3275.401..28727.195 rows=255209 loops=1) Hash Cond: ("outer".vatrecno = "inner".recno) -> Hash Join (cost=10428.11..36240.44 rows=255210 width=189) (actual time=3275.193..20894.624 rows=255209 loops=1) Hash Cond: ("outer".productrecno = "inner".recno) -> Seq Scan on supplierproduct sprod (cost=0.00..7343.09 rows=255209 width=111) (actual time=0.076..3242.104 rows=255209 loops=1) -> Hash (cost=6425.09..6425.09 rows=255209 width=78) (actual time=3269.618..3269.618 rows=0 loops=1) -> Seq Scan on product prod (cost=0.00..6425.09 rows=255209 width=78) (actual time=0.070..1653.955 rows=255209 loops=1) -> Hash (cost=1.03..1.03 rows=3 width=47) (actual time=0.121..0.121 rows=0 loops=1) -> Seq Scan on vat (cost=0.00..1.03 rows=3 width=47) (actual time=0.078..0.094 rows=3 loops=1) -> Hash (cost=431.36..431.36 rows=4613 width=22) (actual time=177.456..177.456 rows=0 loops=1) -> Hash Join (cost=8.28..431.36 rows=4613 width=22) (actual time=2.405..153.564 rows=4840 loops=1) Hash Cond: ("outer".purchaseordhdrrecno = "inner".recno) -> Seq Scan on purchaseorddet podet (cost=0.00..321.00 rows=11190 width=18) (actual time=0.076..68.128 rows=11167 loops=1) Filter: (linestatus <> 'V'::bpchar) -> Hash (cost=7.87..7.87 rows=162 width=8) (actual time=2.166..2.166 rows=0 loops=1) -> Index Scan using purchaseordhdr_3 on purchaseordhdr pohdr (cost=0.00..7.87 rows=162 width=8) (actual time=0.190..1.319 rows=167 loops=1) Index Cond: ((orderdate >= '2005-12-01'::date) AND (orderdate <= '2005-12-21'::date)) -> Subquery Scan "*SELECT* 2" (cost=3.92..39989.32 rows=1 width=431) (actual time=33.184..366.293 rows=11 loops=1) -> Nested Loop (cost=3.92..39989.31 rows=1 width=431) (actual time=33.076..365.280 rows=11 loops=1) Join Filter: ("inner".foreignrecno = "outer".sourcedetrecno) -> Nested Loop (cost=0.00..39956.66 rows=1 width=272) (actual time=30.378..178.705 rows=11 loops=1) -> Nested Loop (cost=0.00..39952.02 rows=1 width=194) (actual time=30.235..177.362 rows=11 loops=1) Join Filter: ("outer".vatrecno = "inner".recno) -> Nested Loop (cost=0.00..39950.96 rows=1 width=147) (actual time=30.101..176.263 rows=11 loops=1) -> Nested Loop (cost=0.00..39944.94 rows=1 width=40) (actual time=30.039..175.207 rows=11 loops=1) -> Index Scan using sourcedetupgr_2 on sourcedetupgr srcu (cost=0.00..760.84 rows=386 width=32) (actual time=0.441..28.226 rows=331 loops=1) Filter: (linestatus = 'S'::bpchar) -> Index Scan using sourcedet_cpk on sourcedet srcd (cost=0.00..101.50 rows=1 width=8) (actual time=0.432..0.432 rows=0 loops=331) Index Cond: ("outer".sourcedetrecno = srcd.recno) Filter: ((entereddate >= '2005-12-01'::date) AND (entereddate <= '2005-12-21'::date) AND (servicecobranchrecno = 2) AND (actionstatus <> 'V'::bpchar) AND ((subplan) = (subplan))) SubPlan -> Aggregate (cost=48.93..48.93 rows=1 width=0) (actual time=0.697..0.701 rows=1 loops=144) -> Index Scan using sourcedetupgr_2 on sourcedetupgr srcu2 (cost=0.00..48.84 rows=32 width=0) (actual time=0.019..0.393 rows=59 loops=144) Index Cond: (sourcedetrecno = $0) Filter: (linestatus <> 'V'::bpchar) -> Aggregate (cost=48.93..48.93 rows=1 width=0) (actual time=0.203..0.208 rows=1 loops=144) -> Index Scan using sourcedetupgr_2 on sourcedetupgr srcu2 (cost=0.00..48.93 rows=1 width=0) (actual time=0.052..0.173 rows=3 loops=144) Index Cond: (sourcedetrecno = $0) Filter: ((linestatus = 'S'::bpchar) OR (linestatus = 'T'::bpchar)) -> Index Scan using supplierproduct_cpk on supplierproduct sprod (cost=0.00..6.00 rows=1 width=111) (actual time=0.045..0.052 rows=1 loops=11) Index Cond: ("outer".supplierproductrecno = sprod.recno) -> Seq Scan on vat (cost=0.00..1.03 rows=3 width=47) (actual time=0.015..0.034 rows=3 loops=11) -> Index Scan using product_cpk on product prod (cost=0.00..4.62 rows=1 width=78) (actual time=0.049..0.061 rows=1 loops=11) Index Cond: ("outer".productrecno = prod.recno) -> Hash Join (cost=3.92..26.31 rows=507 width=167) (actual time=0.145..13.221 rows=507 loops=11) Hash Cond: ("outer".ownerforeignrecno = "inner".recno) -> Seq Scan on sourcedetextref srce (cost=0.00..14.79 rows=507 width=8) (actual time=0.020..3.311 rows=507 loops=11) Filter: (((tablename)::text = 'sourcedet'::text) AND ((ownerforeigntablename)::text = 'clientbranch'::text)) -> Hash (cost=3.89..3.89 rows=14 width=163) (actual time=1.006..1.006 rows=0 loops=1) -> Hash Join (cost=2.30..3.89 rows=14 width=163) (actual time=0.471..0.920 rows=14 loops=1) Hash Cond: ("outer".recno = "inner".clientrecno) -> Hash Join (cost=1.12..2.40 rows=10 width=136) (actual time=0.220..0.463 rows=10 loops=1) Hash Cond: ("outer".foreignrecno = "inner".recno) -> Seq Scan on markup mrkup (cost=0.00..1.12 rows=10 width=109) (actual time=0.019..0.082 rows=10 loops=1) Filter: ((foreigntablename)::text = 'client'::text) -> Hash (cost=1.10..1.10 rows=10 width=27) (actual time=0.156..0.156 rows=0 loops=1) -> Seq Scan on client clnt (cost=0.00..1.10 rows=10 width=27) (actual time=0.043..0.096 rows=10 loops=1) -> Hash (cost=1.14..1.14 rows=14 width=31) (actual time=0.211..0.211 rows=0 loops=1) -> Seq Scan on clientbranch clntb (cost=0.00..1.14 rows=14 width=31) (actual time=0.051..0.130 rows=14 loops=1) Total runtime: 45370.264 ms (97 rows)