Re: Query performance issue - Mailing list pgsql-performance

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.