Re: Query performance issue

Here goes....I think it might be difficult to go through all these
definitions..
PRGMEMACCMST

Table "public.prgmemaccmst"
Column | Type | Modifiers
--------------+-----------------------------+-----------
cmpcod | character varying(5) | not null
prgcod | character varying(5) | not null
memshpnum | character varying(30) | not null
accsta | character varying(1) | not null
accstachgdat | timestamp without time zone | not null
expdat | timestamp without time zone |
tircod | character varying(5) |
tirexpdat | timestamp without time zone |
crdexpdat | timestamp without time zone |
tiraltdat | timestamp without time zone |
crdlmtalwflg | boolean |
lstactdat | timestamp without time zone |
enrsrc | character varying(1) | not null
enrsrccod | character varying(15) |
enrdat | timestamp without time zone | not null
acrpntflg | boolean |
usrcod | character varying(25) |
upddat | timestamp without time zone |
erlrgn | character varying(20) |
susflg | character varying(1) |
fstactdat | timestamp without time zone |
fstacractnum | character varying(12) |
acccrtdat | timestamp without time zone | not null
lsttirprcdat | timestamp without time zone |
enrtircod | character varying(5) |
Indexes:
"prgmemaccmst_pkey" PRIMARY KEY, btree (cmpcod, prgcod, memshpnum)
"prgmemaccmst_accsta_idx" btree (accsta)
"prgmemaccmst_enrdat_idx" btree (enrdat)
"prgmemaccmst_tircod_idx" btree (tircod)
"prgmemaccmst_tirexpdat_ind" btree (tirexpdat)

EAIMEMPFLMST
View "public.eaimempflmst"
Column | Type | Modifiers | Storage |
Description
-----------+-----------------------------+-----------+----------+-------------
cmpcod | character varying(5) | | extended |
memshpnum | character varying(30) | | extended |
memshptyp | character varying(1) | | extended |
memshpsta | character varying(1) | | extended |
pin | character varying(50) | | extended |
sctqst | character varying(200) | | extended |
sctans | character varying(200) | | extended |
rtoclmcnt | smallint | | plain |
usrcod | character varying(25) | | extended |
upddat | timestamp without time zone | | plain |
cusnum | character varying(11) | | extended |
View definition:
SELECT memmst.cmpcod, memmst.memshpnum, memmst.memshptyp, memmst.memshpsta,
memmst.pin, memmst.sctqst, memmst.sctans, memmst.rtoclmcnt, memmst.usrcod,
memmst.upddat, memmst.cusnum
FROM memmst;

memmst
Table "public.memmst"
Column | Type | Modifiers
-----------+-----------------------------+-----------
cmpcod | character varying(5) | not null
memshpnum | character varying(30) | not null
memshptyp | character varying(1) | not null
memshpsta | character varying(1) | not null
pin | character varying(50) | not null
sctqst | character varying(200) |
sctans | character varying(200) |
rtoclmcnt | smallint |
usrcod | character varying(25) |
upddat | timestamp without time zone |
cusnum | character varying(11) |
weblgn | boolean |
rsncod | character varying(1) |
lgntrycnt | smallint |
lgntrytim | timestamp without time zone |
rempinchg | boolean |
Indexes:
"memmst_pkey" PRIMARY KEY, btree (cmpcod, memshpnum)
"memmst_idx" UNIQUE, btree (cusnum, memshpnum, cmpcod)
"memmst_upddat_idx" btree (upddat)

View "public.eaicuspflcntinf"
Column | Type | Modifiers | Storage |
Description
-----------+-----------------------------+-----------+----------+-------------
cmpcod | character varying(5) | | extended |
cusnum | character varying(11) | | extended |
adrtyp | character varying(1) | | extended |
adrlinone | character varying(150) | | extended |
adrlintwo | character varying(150) | | extended |
cty | character varying(100) | | extended |
stt | character varying(100) | | extended |
ctr | character varying(5) | | extended |
zipcod | character varying(30) | | extended |
emladr | character varying(100) | | extended |
phnnum | character varying(50) | | extended |
celisdcod | character varying(5) | | extended |
celaracod | character varying(5) | | extended |
celnum | character varying(50) | | extended |
fax | character varying(50) | | extended |
skypid | character varying(25) | | extended |
upddat | timestamp without time zone | | plain |
pstinvflg | boolean | | plain |
emlinvflg | boolean | | plain |
View definition:
SELECT cuscntinf.cmpcod, cuscntinf.cusnum, cuscntinf.adrtyp,
cuscntinf.adrlinone, cuscntinf.adrlintwo, cuscntinf.cty, cuscntinf.stt,
cuscntinf.ctr, cuscntinf.zipcod, cuscntinf.emladr, cuscntinf.phnnum,
cuscntinf.celisdcod, cuscntinf.celaracod, cuscntinf.celnum, cuscntinf.fax,
cuscntinf.skypid, cuscntinf.upddat, cuscntinf.pstinvflg, cuscntinf.emlinvflg
FROM cuscntinf;

cuscntinf
Table "public.cuscntinf"
Column | Type | Modifiers
--------------+-----------------------------+-----------
cmpcod | character varying(5) | not null
cusnum | character varying(11) | not null
adrtyp | character varying(1) | not null
adrlinone | character varying(150) |
adrlintwo | character varying(150) |
cty | character varying(100) |
stt | character varying(100) |
ctr | character varying(5) |
zipcod | character varying(30) |
emladr | character varying(100) |
phnisdcod | character varying(5) |
phnaracod | character varying(5) |
phnnum | character varying(50) |
celisdcod | character varying(5) |
celaracod | character varying(5) |
celnum | character varying(50) |
faxisdcod | character varying(5) |
faxaracod | character varying(5) |
fax | character varying(50) |
skypid | character varying(25) |
upddat | timestamp without time zone | not null
emlinvflg | boolean |
pstinvflg | boolean |
pstbnccnt | smallint |
emlhrdbnccnt | smallint | default 0
emlmdmbnccnt | smallint | default 0
emlsftbnccnt | smallint | default 0
lstemlbncdat | timestamp without time zone |
smsnotsnd | boolean |
Indexes:
"cuscntinf_pkey" PRIMARY KEY, btree (cmpcod, cusnum, adrtyp)
"cuscntinf_celaracod_idx" btree (celaracod, cusnum, cmpcod)
"cuscntinf_celisdcod_idx" btree (celisdcod, cusnum, cmpcod)
"cuscntinf_celnum_idx" btree (celnum, cusnum, cmpcod)
"cuscntinf_emladr_idx" btree (upper(emladr::text))
"cuscntinf_upddat_idx" btree (upddat)

COMONETIM
Table "public.comonetim"
Column | Type | Modifiers
--------+-----------------------------+-----------
cmpcod | character varying(5) | not null
fldcod | character varying(50) | not null
fldval | character varying(100) | not null
flddes | character varying(100) |
usrcod | character varying(25) |
seqnum | smallint |
upddat | timestamp without time zone |
prvcod | character varying(10) |
Indexes:
"comonetim_pkey" PRIMARY KEY, btree (cmpcod, fldcod, fldval)

COMONETIM
Table "public.comonetim"
Column | Type | Modifiers
--------+-----------------------------+-----------
cmpcod | character varying(5) | not null
fldcod | character varying(50) | not null
fldval | character varying(100) | not null
flddes | character varying(100) |
usrcod | character varying(25) |
seqnum | smallint |
upddat | timestamp without time zone |
prvcod | character varying(10) |
Indexes:
"comonetim_pkey" PRIMARY KEY, btree (cmpcod, fldcod, fldval)

EAICUSPFLINDINF
View "public.eaicuspflindinf"
Column | Type | Modifiers | Storage | Description
--------+-----------------------------+-----------+----------+-------------
cmpcod | character varying(5) | | extended |
cusnum | character varying(11) | | extended |
prflng | character varying(5) | | extended |
prfadr | character varying(1) | | extended |
memtle | character varying(5) | | extended |
gvnnam | character varying(80) | | extended |
famnam | character varying(80) | | extended |
initls | character varying(80) | | extended |
dspnam | character varying(170) | | extended |
memgnd | character varying(1) | | extended |
mrlsta | character varying(1) | | extended |
memdob | timestamp without time zone | | plain |
idrnum | character varying(18) | | extended |
pstnum | character varying(30) | | extended |
cntres | character varying(5) | | extended |
stfidn | character varying(15) | | extended |
cmpnam | character varying(80) | | extended |
dsg | character varying(80) | | extended |
idttyp | character varying(1) | | extended |
incbnd | character varying(2) | | extended |
memnly | character varying(20) | | extended |
upddat | timestamp without time zone | | plain |
View definition:
SELECT cusindinf.cmpcod, cusindinf.cusnum, cusindinf.prflng,
cusindinf.prfadr, cusindinf.memtle, cusindinf.gvnnam, cusindinf.famnam,
cusindinf.initls, cusindinf.dspnam, cusindinf.memgnd, cusindinf.mrlsta,
cusindinf.memdob, cusindinf.idrnum, cusindinf.pstnum, cusindinf.cntres,
cusindinf.stfidn, cusindinf.cmpnam, cusindinf.dsg, cusindinf.idttyp,
cusindinf.incbnd, cusindinf.memnly, cusindinf.upddat
FROM cusindinf;

cusindinf
Table "public.cusindinf"
Column | Type | Modifiers
--------+-----------------------------+-----------
cmpcod | character varying(5) | not null
cusnum | character varying(11) | not null
prflng | character varying(5) | not null
prfadr | character varying(1) | not null
memtle | character varying(5) | not null
gvnnam | character varying(80) | not null
famnam | character varying(80) | not null
initls | character varying(80) |
dspnam | character varying(170) |
memgnd | character varying(1) | not null
mrlsta | character varying(1) |
memdob | timestamp without time zone |
pstnum | character varying(30) |
cntres | character varying(5) | not null
stfidn | character varying(15) |
cmpnam | character varying(80) |
dsg | character varying(80) |
idttyp | character varying(1) |
incbnd | character varying(2) |
memnly | character varying(20) |
idrnum | character varying(18) |
upddat | timestamp without time zone | not null
Indexes:
"cusindinf_pkey" PRIMARY KEY, btree (cmpcod, cusnum)
"cusindinf_idrnum_idx" btree (idrnum, cusnum, cmpcod)
"cusindinf_idx1" btree (upper(gvnnam::text))
"cusindinf_idx2" btree (upper(famnam::text))
"cusindinf_idx3" btree (upper(cmpnam::text))
"cusindinf_idx4" btree (upper((gvnnam::text || ' '::text) ||
famnam::text))
"cusindinf_upddat_idx" btree (upddat)

Query -
SELECT PFLMST.MEMSHPNUM,
PFLMST.MEMSHPTYP,
ACCMST.PRGCOD,
CNTINF.EMLADR,
CNTINF.CELISDCOD,
CNTINF.CELARACOD,
CNTINF.CELNUM,
CNTINF.ADRLINONE ,
CNTINF.ZIPCOD,
CNTINF.ADRTYP,
ONE.FLDDES ACCSTA,
ONE1.FLDDES MEMSHPSTA,
INDINF.CMPNAM EMPNAM,
INDINF.PRFADR,
INDINF.GVNNAM GVNNAM,
INDINF.FAMNAM FAMNAM,
INDINF.MEMDOB MEMDOB
FROM PRGMEMACCMST ACCMST
JOIN EAIMEMPFLMST PFLMST
ON ACCMST.CMPCOD = PFLMST.CMPCOD
AND ACCMST.MEMSHPNUM = PFLMST.MEMSHPNUM
JOIN EAICUSPFLCNTINF CNTINF
ON CNTINF.CMPCOD = PFLMST.CMPCOD
AND CNTINF.CUSNUM = PFLMST.CUSNUM
JOIN COMONETIM ONE
ON ONE.CMPCOD =ACCMST.CMPCOD
AND ONE.FLDCOD='program.member.accountStatus'
AND ONE.FLDVAL=ACCMST.ACCSTA
JOIN COMONETIM ONE1
ON ONE1.CMPCOD =ACCMST.CMPCOD
AND ONE1.FLDCOD='common.member.membershipStatus'
AND ONE1.FLDVAL=PFLMST.MEMSHPSTA
LEFT JOIN EAICUSPFLINDINF INDINF
ON INDINF.CMPCOD = PFLMST.CMPCOD
AND INDINF.CUSNUM = PFLMST.CUSNUM
WHERE ACCMST.CMPCOD= 'SA'
AND UPPER(INDINF.FAMNAM) LIKE 'PRICE'
|| '%'
ORDER BY UPPER(INDINF.GVNNAM),
UPPER(INDINF.FAMNAM),
UPPER(INDINF.CMPNAM)

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Query-performance-issue-tp4753453p4764725.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.