am connecting three tables in query. one table have 73000 records
another two tables have 138000 records.
but its take 12 sec for show 12402 rows in tables
Tables Structure:
Items Table
CREATE TABLE "C_SAM_Master".items
(
itemno integer NOT NULL,
itemname character varying(250) NOT NULL,
itemcode character varying(250) NOT NULL,
shortname character varying(20) NOT NULL,
aliasname character varying(250) NOT NULL,
aliasnamelanguage character varying(250) NOT NULL,
masteritemno integer NOT NULL,
groupno1 smallint NOT NULL,
groupno2 smallint NOT NULL,
groupno3 smallint NOT NULL,
commodityno smallint NOT NULL,
unitno smallint NOT NULL,
weighttype character(1) NOT NULL,
altunitno smallint NOT NULL,
weight double precision NOT NULL,
reqmrp character(1) NOT NULL,
reqbatch character(1) NOT NULL,
reqmfrdate character(1) NOT NULL,
mfrdateformat character varying(20) NOT NULL,
reqexpdate character(1) NOT NULL,
expdateformat character varying(20) NOT NULL,
expdays1 smallint NOT NULL,
expdays2 character(1) NOT NULL,
expinfodays smallint NOT NULL,
stdsaleratemethod smallint NOT NULL,
salesrateper smallint NOT NULL,
stdprofit1 double precision NOT NULL,
stdprofit2 character(1) NOT NULL,
includestockrep character(1) NOT NULL,
minstock double precision NOT NULL,
minstockunit smallint NOT NULL,
minsaleqtynos double precision NOT NULL,
minsaleqtyunit smallint NOT NULL,
minsaleqty double precision NOT NULL,
description text NOT NULL,
remarks character varying(250) NOT NULL,
actpurchaseorder character(1) NOT NULL,
actpurchase character(1) NOT NULL,
actpurchasereturn character(1) NOT NULL,
actsalesorder character(1) NOT NULL,
actsales character(1) NOT NULL,
actsalesreturn character(1) NOT NULL,
actreceiptnote character(1) NOT NULL,
actdeliverynote character(1) NOT NULL,
actconsumption character(1) NOT NULL,
actproduction character(1) NOT NULL,
actestimate character(1) NOT NULL,
notifypurchaseorder character varying(250) NOT NULL,
notifypurchase character varying(250) NOT NULL,
notifypurchasereturn character varying(250) NOT NULL,
notifysalesorder character varying(250) NOT NULL,
notifysales character varying(250) NOT NULL,
notifysalesreturn character varying(250) NOT NULL,
notifyreceiptnote character varying(250) NOT NULL,
notifydeliverynote character varying(250) NOT NULL,
notifyconsumption character varying(250) NOT NULL,
notifyproduction character varying(250) NOT NULL,
notifyestimate character varying(250) NOT NULL,
act boolean NOT NULL,
recordowner smallint NOT NULL,
lastmodified smallint NOT NULL,
crdate timestamp without time zone NOT NULL,
stdmaxprofit double precision NOT NULL,
commodityname character varying(100) NOT NULL,
lst double precision NOT NULL,
unittype character(1) NOT NULL,
unit1 character varying(15) NOT NULL,
unit2 character varying(15) NOT NULL,
units integer NOT NULL,
unitname character varying(50) NOT NULL,
decimals smallint NOT NULL,
groupname1 character varying(50) NOT NULL,
groupname2 character varying(50) NOT NULL,
groupname3 character varying(50) NOT NULL,
repgroupname character varying(160) NOT NULL,
masteritemname character varying(100) NOT NULL,
altunit1 character varying(15) NOT NULL,
altunit2 character varying(15) NOT NULL,
altunits integer NOT NULL,
altunitname character varying(50) NOT NULL,
altunitdecimals smallint NOT NULL,
CONSTRAINT items_itemno_pk PRIMARY KEY (itemno),
CONSTRAINT items_altunitno_fk FOREIGN KEY (altunitno)
REFERENCES "C_SAM_Master".measureunits (unitno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT items_commodityno_fk FOREIGN KEY (commodityno)
REFERENCES "C_SAM_Master".commodity (commodityno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT items_groupno1_fk FOREIGN KEY (groupno1)
REFERENCES "C_SAM_Master".itemgroup1 (groupno1) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT items_groupno2_fk FOREIGN KEY (groupno2)
REFERENCES "C_SAM_Master".itemgroup2 (groupno2) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT items_groupno3_fk FOREIGN KEY (groupno3)
REFERENCES "C_SAM_Master".itemgroup3 (groupno3) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT items_lastmodified_fk FOREIGN KEY (lastmodified)
REFERENCES appsetup.user1 (userno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT items_masteritemno_fk FOREIGN KEY (masteritemno)
REFERENCES "C_SAM_Master".masteritems (masteritemno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT items_recordowner_fk FOREIGN KEY (recordowner)
REFERENCES appsetup.user1 (userno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT items_unitno_fk FOREIGN KEY (unitno)
REFERENCES "C_SAM_Master".measureunits (unitno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT items_actconsumption_ck CHECK (actconsumption::text <>
''::text),
CONSTRAINT items_actdeliverynote_ck CHECK (actdeliverynote::text <>
''::text),
CONSTRAINT items_actestimate_ck CHECK (actestimate::text <> ''::text),
CONSTRAINT items_actproduction_ck CHECK (actproduction::text <>
''::text),
CONSTRAINT items_actpurchase_ck CHECK (actpurchase::text <> ''::text),
CONSTRAINT items_actpurchaseorder_ck CHECK (actpurchaseorder::text <>
''::text),
CONSTRAINT items_actpurchasereturn_ck CHECK (actpurchasereturn::text <>
''::text),
CONSTRAINT items_actreceiptnote_ck CHECK (actreceiptnote::text <>
''::text),
CONSTRAINT items_actsales_ck CHECK (actsales::text <> ''::text),
CONSTRAINT items_actsalesorder_ck CHECK (actsalesorder::text <>
''::text),
CONSTRAINT items_actsalesreturn_ck CHECK (actsalesreturn::text <>
''::text),
CONSTRAINT items_aliasname_ck CHECK (aliasname::text <> ''::text),
CONSTRAINT items_altunitdecimals_ck CHECK (altunitdecimals >= 0 AND
altunitdecimals <= 3),
CONSTRAINT items_altunits_ck CHECK (altunits >= 0),
CONSTRAINT items_commodityname_ck CHECK (commodityname::text <>
''::text),
CONSTRAINT items_decimals_ck CHECK (decimals >= 0 AND decimals <= 3),
CONSTRAINT items_expdays1_ck CHECK (expdays1 >= 0),
CONSTRAINT items_expinfodays_ck CHECK (expinfodays >= 0),
CONSTRAINT items_includestockrep_ck CHECK (includestockrep::text <>
''::text),
CONSTRAINT items_itemcode_ck CHECK (itemcode::text <> ''::text),
CONSTRAINT items_itemname_ck CHECK (itemname::text <> ''::text),
CONSTRAINT items_itemno_ck CHECK (itemno > 0),
CONSTRAINT items_lst_ck CHECK (lst >= 0::double precision),
CONSTRAINT items_minsaleqty_ck CHECK (minsaleqty >= 0::double precision),
CONSTRAINT items_minsaleqtynos_ck CHECK (minsaleqtynos >= 0::double
precision),
CONSTRAINT items_minsaleqtyunit_ck CHECK (minsaleqtyunit >= 0 AND
minsaleqtyunit <= 2),
CONSTRAINT items_minstock_ck CHECK (minstock >= 0::double precision),
CONSTRAINT items_minstockunit_ck CHECK (minstockunit >= 0 AND minstockunit
<= 2),
CONSTRAINT items_reqbatch_ck CHECK (reqbatch::text <> ''::text),
CONSTRAINT items_reqexpdate_ck CHECK (reqexpdate::text <> ''::text),
CONSTRAINT items_reqmfrdate_ck CHECK (reqmfrdate::text <> ''::text),
CONSTRAINT items_reqmrp_ck CHECK (reqmrp::text <> ''::text),
CONSTRAINT items_salesrateper_ck CHECK (salesrateper >= 0 AND salesrateper
<= 4),
CONSTRAINT items_stdsaleratemethod_ck CHECK (stdsaleratemethod >= 0 AND
stdsaleratemethod <= 2),
CONSTRAINT items_units_ck CHECK (units >= 0),
CONSTRAINT items_unittype_ck CHECK (unittype::text <> ''::text),
CONSTRAINT items_weight_ck CHECK (weight >= 0::double precision),
CONSTRAINT items_weighttype_ck CHECK (weighttype::text <> ''::text)
)
WITH (
OIDS=FALSE
)
TABLESPACE "gpro2_SAM";
ALTER TABLE "C_SAM_Master".items
OWNER TO gpro2user;
-- Index: "C_SAM_Master".items_itemname_uq
-- DROP INDEX "C_SAM_Master".items_itemname_uq;
CREATE UNIQUE INDEX items_itemname_uq
ON "C_SAM_Master".items
USING btree
(lower(itemname::text) COLLATE pg_catalog."default");
-- Rule: rule_del_items ON "C_SAM_Master".items
-- DROP RULE rule_del_items ON "C_SAM_Master".items;
CREATE OR REPLACE RULE rule_del_items AS
ON DELETE TO "C_SAM_Master".items DO ( DELETE FROM
"C_SAM_Master".itembarcode
WHERE itembarcode.itemno = old.itemno;
DELETE FROM "C_SAM_Master".pricelist
WHERE pricelist.itemno = old.itemno;
DELETE FROM "C_SAM_Master".pricelistreview
WHERE pricelistreview.itemno = old.itemno;
);
-- Rule: rule_del_items_c_sam_2014_2015 ON "C_SAM_Master".items
-- DROP RULE rule_del_items_c_sam_2014_2015 ON "C_SAM_Master".items;
CREATE OR REPLACE RULE rule_del_items_c_sam_2014_2015 AS
ON DELETE TO "C_SAM_Master".items DO ( DELETE FROM
"C_SAM_2014-2015".openingstock
WHERE openingstock.itemno = old.itemno;
DELETE FROM "C_SAM_2014-2015".stock
WHERE stock.itemno = old.itemno;
DELETE FROM "C_SAM_2014-2015".packingsetup
WHERE packingsetup.primeitemno = old.itemno;
DELETE FROM "C_SAM_2014-2015".packingsetup
WHERE packingsetup.packingitemno = old.itemno;
DELETE FROM "C_SAM_2014-2015".itemsuppliers
WHERE itemsuppliers.itemno = old.itemno;
DELETE FROM "C_SAM_2014-2015".partyopeningstock
WHERE partyopeningstock.itemno = old.itemno;
DELETE FROM "C_SAM_2014-2015".partystock
WHERE partystock.itemno = old.itemno;
);
Sales 1 Table
CREATE TABLE "C_KA_2014-2015".sales1
(
vtno smallint NOT NULL,
prefix character varying(5) NOT NULL,
idno integer NOT NULL,
suffix character varying(5) NOT NULL,
txno character varying(20) NOT NULL,
txdate timestamp without time zone NOT NULL,
dracno integer NOT NULL,
partyname character varying(100) NOT NULL,
address1 character varying(100) NOT NULL,
address2 character varying(100) NOT NULL,
city character varying(50) NOT NULL,
partytin character varying(30) NOT NULL,
partycstno character varying(30) NOT NULL,
mobileno character varying(15) NOT NULL,
ponos character varying NOT NULL,
pricelevelno smallint NOT NULL,
invno character varying(20) NOT NULL,
duedays smallint NOT NULL,
duedate timestamp without time zone NOT NULL,
paymentmode character varying(10) NOT NULL,
bankrefno character varying(30) NOT NULL,
bankrefdate character varying(10) NOT NULL,
bankfavourname character varying(100) NOT NULL,
bankcrossref character(1) NOT NULL,
bankremarks character varying(100) NOT NULL,
bankdate character varying(10) NOT NULL,
bankstatus character(1) NOT NULL,
bankreconcildate character varying(10) NOT NULL,
stockpointno smallint NOT NULL,
nettotal double precision NOT NULL,
grosswt integer NOT NULL,
tarewt integer NOT NULL,
actualwt double precision NOT NULL,
againstform character varying(15) NOT NULL,
formseriesno character varying(15) NOT NULL,
formno character varying(15) NOT NULL,
formdate character varying(10) NOT NULL,
totalqty double precision NOT NULL,
totalqtyunit character varying(15) NOT NULL,
totalfreeqty double precision NOT NULL,
totalfreeqtyunit character varying(15) NOT NULL,
totalaltqty double precision NOT NULL,
totalaltqtyunit character varying(15) NOT NULL,
orderby smallint NOT NULL,
collectionby smallint NOT NULL,
deliveredby1 character varying(30) NOT NULL,
deliveredby2 character varying(50) NOT NULL,
deliveredrefno character varying(30) NOT NULL,
deliveredrefdate character varying(10) NOT NULL,
goodsdelivered character(1) NOT NULL,
deliveredto1 character varying(50) NOT NULL,
deliveredto2 character varying(50) NOT NULL,
cashrcvd double precision NOT NULL,
remarks character varying(250) NOT NULL,
totalstockvalue double precision NOT NULL,
profit1 double precision NOT NULL,
act boolean NOT NULL,
totalassesvalue double precision NOT NULL,
totaltax double precision NOT NULL,
recordowner smallint NOT NULL,
lastmodified smallint NOT NULL,
crdate timestamp without time zone NOT NULL,
lessadv double precision NOT NULL,
lessadvpartyacno integer NOT NULL,
rateadj double precision NOT NULL,
jobcardtxno character varying(40) NOT NULL,
txtime character varying(8) NOT NULL,
CONSTRAINT sales1_txno_pk PRIMARY KEY (txno),
CONSTRAINT sales1_collectionby_fk FOREIGN KEY (collectionby)
REFERENCES "G_KUMARANGROUPS_Master".employee (empno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT sales1_dracno_fk FOREIGN KEY (dracno)
REFERENCES "C_KA_AcMaster".acledger (acno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT sales1_lastmodified_fk FOREIGN KEY (lastmodified)
REFERENCES appsetup.user1 (userno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT sales1_orderby_fk FOREIGN KEY (orderby)
REFERENCES "G_KUMARANGROUPS_Master".employee (empno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT sales1_pricelevelno_fk FOREIGN KEY (pricelevelno)
REFERENCES "C_KA_AcMaster".acpricelevel (pricelevelno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT sales1_recordowner_fk FOREIGN KEY (recordowner)
REFERENCES appsetup.user1 (userno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT sales1_vto_fk FOREIGN KEY (vtno)
REFERENCES "C_KA_2014-2015".acvouchertype (vtno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT sales1_vtnoprefixidnosuffix_uq UNIQUE (vtno, prefix, idno,
suffix),
CONSTRAINT sales1_duedays_ck CHECK (duedays >= 0),
CONSTRAINT sales1_idno_ck CHECK (idno > 0),
CONSTRAINT sales1_lessadv_ck CHECK (lessadv >= 0::double precision),
CONSTRAINT sales1_lessadvpartyacno_ck CHECK (lessadvpartyacno >= 0),
CONSTRAINT sales1_partyname_ck CHECK (partyname::text <> ''::text),
CONSTRAINT sales1_paymentmode_ck CHECK (paymentmode::text <> ''::text),
CONSTRAINT sales1_stockpointno_ck CHECK (stockpointno >= 0)
)
WITH (
OIDS=FALSE
)
TABLESPACE "gpro2_KA";
ALTER TABLE "C_KA_2014-2015".sales1
OWNER TO gpro2user;
-- Index: "C_KA_2014-2015".sales1_acno
-- DROP INDEX "C_KA_2014-2015".sales1_acno;
CREATE INDEX sales1_acno
ON "C_KA_2014-2015".sales1
USING btree
(dracno);
-- Index: "C_KA_2014-2015".sales1_txdate
-- DROP INDEX "C_KA_2014-2015".sales1_txdate;
CREATE INDEX sales1_txdate
ON "C_KA_2014-2015".sales1
USING btree
(txdate);
-- Rule: rule_del_sales ON "C_KA_2014-2015".sales1
-- DROP RULE rule_del_sales ON "C_KA_2014-2015".sales1;
CREATE OR REPLACE RULE rule_del_sales AS
ON DELETE TO "C_KA_2014-2015".sales1 DO ( DELETE FROM
"C_KA_2014-2015".packingitemsautopost
WHERE packingitemsautopost.transtype::text = 'Sales'::text AND
packingitemsautopost.txno::text = old.txno::text;
DELETE FROM "C_KA_2014-2015".sales6
WHERE sales6.txno::text = old.txno::text;
DELETE FROM "C_KA_2014-2015".sales5
WHERE sales5.txno::text = old.txno::text;
DELETE FROM "C_KA_2014-2015".sales4
WHERE sales4.txno::text = old.txno::text;
DELETE FROM "C_KA_2014-2015".sales3
WHERE sales3.txno::text = old.txno::text;
DELETE FROM "C_KA_2014-2015".sales2
WHERE sales2.txno::text = old.txno::text;
);
-- Trigger: trg_sales1 on "C_KA_2014-2015".sales1
-- DROP TRIGGER trg_sales1 ON "C_KA_2014-2015".sales1;
CREATE TRIGGER trg_sales1
AFTER UPDATE OF act
ON "C_KA_2014-2015".sales1
FOR EACH ROW
EXECUTE PROCEDURE fn_trg_sales('sales1');
-- Trigger: trg_sales1acpost on "C_KA_2014-2015".sales1
-- DROP TRIGGER trg_sales1acpost ON "C_KA_2014-2015".sales1;
CREATE TRIGGER trg_sales1acpost
AFTER INSERT OR UPDATE OF txdate OR DELETE
ON "C_KA_2014-2015".sales1
FOR EACH ROW
EXECUTE PROCEDURE fn_trg_sales1acpost();
Sales 2 Table
CREATE TABLE "C_KA_2014-2015".sales2
(
txno character varying(20) NOT NULL,
slno smallint NOT NULL,
itemno integer NOT NULL,
rowkey smallint NOT NULL,
mrp double precision NOT NULL,
batchno character varying(20) NOT NULL,
expdate character varying(10) NOT NULL,
qty1 double precision NOT NULL,
qty2 double precision NOT NULL,
freeqty1 double precision NOT NULL,
freeqty2 double precision NOT NULL,
altqty1 double precision NOT NULL,
altqty2 double precision NOT NULL,
rate double precision NOT NULL,
rateper smallint NOT NULL,
basedvalue double precision NOT NULL,
tradedis1 double precision NOT NULL,
tradedis2 double precision NOT NULL,
totaltradis double precision NOT NULL,
adnldis1 double precision NOT NULL,
adnldis2 double precision NOT NULL,
totaladnldis double precision NOT NULL,
adnlcostbeforevat double precision NOT NULL,
assesvalue double precision NOT NULL,
cst1 double precision NOT NULL,
cst2 double precision NOT NULL,
lst1 double precision NOT NULL,
lst2 double precision NOT NULL,
amount double precision NOT NULL,
itemdescription text NOT NULL,
adnlcostafterevat double precision NOT NULL,
nsr double precision NOT NULL,
totalqty double precision NOT NULL,
totalfreeqty double precision NOT NULL,
totalaltqty double precision NOT NULL,
primaryacno integer NOT NULL,
taxacno integer NOT NULL,
itemstockvalue double precision NOT NULL,
itemprofit1 double precision NOT NULL,
cliamscheme character(1) NOT NULL,
netrate double precision NOT NULL,
pricelistrate double precision NOT NULL,
CONSTRAINT sales2_txnoslno_pk PRIMARY KEY (txno, slno),
CONSTRAINT sales2_itemno_fk FOREIGN KEY (itemno)
REFERENCES "G_KUMARANGROUPS_Master".items (itemno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT sales2_txno_fk FOREIGN KEY (txno)
REFERENCES "C_KA_2014-2015".sales1 (txno) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE RESTRICT,
CONSTRAINT sales2_rowkey_uq UNIQUE (rowkey, txno),
CONSTRAINT sales2_cst1_ck CHECK (cst1 >= 0::double precision),
CONSTRAINT sales2_lst1_ck CHECK (lst1 >= 0::double precision),
CONSTRAINT sales2_mrp_ck CHECK (mrp >= 0::double precision),
CONSTRAINT sales2_netrate_ck CHECK (netrate >= 0::double precision),
CONSTRAINT sales2_nsr_ck CHECK (nsr >= 0::double precision),
CONSTRAINT sales2_pricelistrate_ck CHECK (pricelistrate >= 0::double
precision),
CONSTRAINT sales2_primaryacno_ck CHECK (primaryacno >= 0),
CONSTRAINT sales2_rate_ck CHECK (rate >= 0::double precision),
CONSTRAINT sales2_rateper_ck CHECK (rateper >= 0 AND rateper <= 4),
CONSTRAINT sales2_rowkey_ck CHECK (rowkey > 0),
CONSTRAINT sales2_slno_ck CHECK (slno > 0),
CONSTRAINT sales2_taxacno_ck CHECK (taxacno >= 0),
CONSTRAINT sales2_totalfreeqty_ck CHECK ((totalqty + totalfreeqty) <>
0::double precision),
CONSTRAINT sales2_totalqty_ck CHECK ((totalqty + totalfreeqty) <>
0::double precision)
)
WITH (
OIDS=FALSE
)
TABLESPACE "gpro2_KA";
ALTER TABLE "C_KA_2014-2015".sales2
OWNER TO gpro2user;
-- Index: "C_KA_2014-2015".sales2_itemno
-- DROP INDEX "C_KA_2014-2015".sales2_itemno;
CREATE INDEX sales2_itemno
ON "C_KA_2014-2015".sales2
USING btree
(itemno);
-- Index: "C_KA_2014-2015".sales2_txno
-- DROP INDEX "C_KA_2014-2015".sales2_txno;
CREATE INDEX sales2_txno
ON "C_KA_2014-2015".sales2
USING btree
(txno COLLATE pg_catalog."default");
-- Trigger: trg_sales2 on "C_KA_2014-2015".sales2
-- DROP TRIGGER trg_sales2 ON "C_KA_2014-2015".sales2;
CREATE TRIGGER trg_sales2
AFTER INSERT OR DELETE
ON "C_KA_2014-2015".sales2
FOR EACH ROW
EXECUTE PROCEDURE fn_trg_sales2();
Query:
select grp,disp,alisdisp,ord,'' as adnlorder,'' as calcorder,sum(case when
ord =3 then qty end) as qty,sum(case when ord=3 then freeqty end) as
freeqty,max(case when ord=3 then unit1 end) as unit1,sum(altqty) as
altqty,max(altunit1) as altunit1,sum(discount) as discount,sum(amount) as
amount,sum(itemprofit) as itemprofit,0.00 as profitper,sum(itemstockvalue)
as itemstockvalue from (select
unnest(array[repgroupname,repgroupname||'-'||masteritemname,repgroupname||'-'||masteritemname||'-'||itemname])
as grp,unnest(array[case when repgroupname ='' then 'UnGrouped' else
repgroupname end,masteritemname,itemname]) as
disp,unnest(array['','',aliasnamelanguage]) as alisdisp,unnest(array[1,2,3])
as ord,cast(case when units > 1 then cast(case when sum(qty) > 0 then
floor(sum(qty)/units) else ceil(sum(qty)/units) end as text) when
(mod(cast(sum(qty) as integer),units))<>0 then '.' ||
abs(cast(mod(cast(sum(qty) as integer),units) as integer)) else
cast(sum(qty) as text) end as double precision) as qty,cast(case when units
> 1 then cast(case when sum(freeqty) > 0 then floor(sum(freeqty)/units) else
ceil(sum(freeqty)/units) end as text) when (mod(cast(sum(freeqty) as
integer),units))<>0 then '.' || abs(cast(mod(cast(sum(freeqty) as
integer),units) as integer)) else cast(sum(freeqty) as text) end as double
precision) as freeqty,unit1,cast(case when altunits > 1 then cast(case when
sum(altqty) > 0 then floor(sum(altqty)/altunits) else
ceil(sum(altqty)/altunits) end as text) when (mod(cast(sum(altqty) as
integer),altunits))<>0 then '.' || abs(cast(mod(cast(sum(altqty) as
integer),altunits) as integer)) else cast(sum(altqty) as text) end as double
precision) as altqty,altunit1,sum(discount) as discount,sum(itemstockvalue)
as itemstockvalue,sum(itemprofit) as itemprofit,sum(amount) as
amount,shortname from (select
i.repgroupname,i.aliasnamelanguage,i.masteritemname,i.itemname,i.groupname1,i.groupname2,i.groupname3,i.units,i.unit1,i.unit2,i.altunit1,i.altunit2,i.altunits,sum(s2.totalqty)
as qty,sum(s2.totalfreeqty) as freeqty,sum(s2.totalaltqty) as
altqty,sum(s2.totaltradis + totaladnldis) as discount,sum(itemstockvalue) as
itemstockvalue,sum(itemprofit1) as itemprofit,sum(s2.amount) as
amount,'KA'::text as shortname from "C_KA_2014-2015".sales1 s1 inner join
"C_KA_2014-2015".sales2 s2 on s1.txno=s2.txno inner join
"G_KUMARANGROUPS_Master".items i on i.itemno=s2.itemno where s1.act='t' and
s1.txdate >= '01/04/2014' and s1.txdate <= '30/01/2015'group by
i.repgroupname,i.aliasnamelanguage,i.groupname1,i.groupname2,i.groupname3,i.units,i.unit1,i.unit2,i.altunit1,i.altunit2,i.altunits,i.itemname,i.masteritemname
) as tt group by
grp,disp,alisdisp,units,altunits,ord,shortname,unit1,altunit1 order by
grp,disp ) as tab where disp <> ''
group by grp,disp,alisdisp,ord order by grp,disp
Explain Analysis and Buffers
"GroupAggregate (cost=3586024.69..3617755.12 rows=72944 width=160) (actual
time=11819.837..11884.868 rows=12064 loops=1)"
" Buffers: shared hit=4462 read=9825, temp read=6381 written=6361"
" -> Sort (cost=3586024.69..3587848.28 rows=729435 width=160) (actual
time=11819.780..11831.894 rows=12068 loops=1)"
" Sort Key: tab.grp, tab.disp, tab.alisdisp, tab.ord"
" Sort Method: external sort Disk: 1336kB"
" Buffers: shared hit=4462 read=9825, temp read=6381 written=6361"
" -> Subquery Scan on tab (cost=2742202.68..3342958.78 rows=729435
width=160) (actual time=11424.007..11727.170 rows=12068 loops=1)"
" Filter: ((tab.disp)::text <> ''::text)"
" Rows Removed by Filter: 7"
" Buffers: shared hit=4462 read=9825, temp read=6214
written=6194"
" -> GroupAggregate (cost=2742202.68..3333795.03 rows=733100
width=115) (actual time=11424.001..11703.904 rows=12075 loops=1)"
" Buffers: shared hit=4462 read=9825, temp read=6214
written=6194"
" -> Sort (cost=2742202.68..2760528.43 rows=7330300
width=115) (actual time=11423.951..11543.478 rows=36183 loops=1)"
" Sort Key: (unnest(ARRAY[tt.repgroupname,
((((tt.repgroupname)::text || '-'::text) ||
(tt.masteritemname)::text))::character varying, ((((((tt.repgroupname)::text
|| '-'::text) || (tt.masteritemname)::text) || '-'::text) || (tt.ite (...)"
" Sort Method: external merge Disk: 3552kB"
" Buffers: shared hit=4462 read=9825, temp
read=6214 written=6194"
" -> Subquery Scan on tt
(cost=56047.61..102407.06 rows=7330300 width=115) (actual
time=8877.785..11023.746 rows=36183 loops=1)"
" Buffers: shared hit=4462 read=9825, temp
read=5768 written=5748"
" -> GroupAggregate
(cost=56047.61..63373.22 rows=73303 width=96) (actual
time=8877.762..10906.503 rows=12061 loops=1)"
" Buffers: shared hit=4462 read=9825,
temp read=5768 written=5748"
" -> Sort (cost=56047.61..56347.27
rows=119865 width=96) (actual time=8877.576..10555.267 rows=119714
loops=1)"
" Sort Key: i.repgroupname,
i.aliasnamelanguage, i.groupname1, i.groupname2, i.groupname3, i.units,
i.unit1, i.unit2, i.altunit1, i.altunit2, i.altunits, i.itemname,
i.masteritemname"
" Sort Method: external merge
Disk: 12432kB"
" Buffers: shared hit=4462
read=9825, temp read=5768 written=5748"
" -> Hash Join
(cost=13948.80..33644.37 rows=119865 width=96) (actual
time=617.917..1756.039 rows=119714 loops=1)"
" Hash Cond: (s2.itemno =
i.itemno)"
" Buffers: shared hit=4462
read=9825, temp read=3098 written=3078"
" -> Hash Join
(cost=8849.48..23064.41 rows=119865 width=68) (actual time=339.948..1054.380
rows=119714 loops=1)"
" Hash Cond:
((s2.txno)::text = (s1.txno)::text)"
" Buffers: shared
hit=1585 read=9825, temp read=1539 written=1533"
" -> Seq Scan on
sales2 s2 (cost=0.00..7490.64 rows=144964 width=76) (actual
time=0.023..262.043 rows=144964 loops=1)"
" Buffers:
shared hit=814 read=5227"
" -> Hash
(cost=7196.35..7196.35 rows=100731 width=8) (actual time=339.873..339.873
rows=100850 loops=1)"
" Buckets: 4096
Batches: 4 Memory Usage: 803kB"
" Buffers:
shared hit=771 read=4598, temp written=257"
" -> Seq Scan
on sales1 s1 (cost=0.00..7196.35 rows=100731 width=8) (actual
time=0.029..230.250 rows=100850 loops=1)"
" Filter:
(act AND (txdate >= '2014-04-01 00:00:00'::timestamp without time zone) AND
(txdate <= '2015-01-30 00:00:00'::timestamp without time zone))"
" Rows
Removed by Filter: 20973"
"
Buffers: shared hit=771 read=4598"
" -> Hash
(cost=3610.03..3610.03 rows=73303 width=36) (actual time=277.327..277.327
rows=73303 loops=1)"
" Buckets: 2048
Batches: 8 Memory Usage: 593kB"
" Buffers: shared
hit=2877, temp written=475"
" -> Seq Scan on
items i (cost=0.00..3610.03 rows=73303 width=36) (actual
time=0.007..153.900 rows=73303 loops=1)"
" Buffers:
shared hit=2877"
"Total runtime: 11897.250 ms"
My Hardware is
InterCore
CPU 3.10 CHZ
2.89 GB RAM
