Thread: Query performance issue

Query performance issue

From
Jayadevan M
Date:
Hello all,
I have a query which takes about 20 minutes to execute and retrieves 2000-odd records. The explain for the query is pasted here
http://explain.depesz.com/s/52f
The same query, with similar data structures/indexes and data comes back in 50 seconds in Oracle. We just ported the product to PostgreSQL and are testing it. Any input on what to look for?

Possible relevant parameters are
shared_buffers = 4GB                    
temp_buffers = 8MB                    
work_mem = 96MB                      
maintenance_work_mem = 1GB            
effective_cache_size = 8GB            
default_statistics_target = 50        

It is a machine with 16 GB RAM.
Regards,
Jayadevan





DISCLAIMER:


"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."





Re: Query performance issue

From
Heikki Linnakangas
Date:
On 31.08.2011 12:00, Jayadevan M wrote:
> Hello all,
> I have a query which takes about 20 minutes to execute and retrieves
> 2000-odd records. The explain for the query is pasted here
> http://explain.depesz.com/s/52f
> The same query, with similar data structures/indexes and data comes back
> in 50 seconds in Oracle. We just ported the product to PostgreSQL and are
> testing it. Any input on what to look for?
>
> Possible relevant parameters are
> shared_buffers = 4GB
> temp_buffers = 8MB
> work_mem = 96MB
> maintenance_work_mem = 1GB
> effective_cache_size = 8GB
> default_statistics_target = 50
>
> It is a machine with 16 GB RAM.

Please run EXPLAIN ANALYZE on the query and post that, it's hard to say
what's wrong from just the query plan, without knowing where the time is
actually spent. And the schema of the tables involved, and any indexes
on them. (see also http://wiki.postgresql.org/wiki/SlowQueryQuestions)

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: Query performance issue

From
Sushant Sinha
Date:
Where is the query? And also paste the \d to show the tables and
indexes.

-Sushant.

On Wed, 2011-08-31 at 14:30 +0530, Jayadevan M wrote:
> Hello all,
> I have a query which takes about 20 minutes to execute and retrieves
> 2000-odd records. The explain for the query is pasted here
> http://explain.depesz.com/s/52f
> The same query, with similar data structures/indexes and data comes
> back in 50 seconds in Oracle. We just ported the product to PostgreSQL
> and are testing it. Any input on what to look for?
>
> Possible relevant parameters are
> shared_buffers = 4GB
> temp_buffers = 8MB
> work_mem = 96MB
> maintenance_work_mem = 1GB
> effective_cache_size = 8GB
> default_statistics_target = 50
>
> It is a machine with 16 GB RAM.
> Regards,
> Jayadevan
>
>
>
>
>
> DISCLAIMER:
>
> "The information in this e-mail and any attachment is intended only
> for the person to whom it is addressed and may contain confidential
> and/or privileged material. If you have received this e-mail in error,
> kindly contact the sender and destroy all copies of the original
> communication. IBS makes no warranty, express or implied, nor
> guarantees the accuracy, adequacy or completeness of the information
> contained in this email or any attachment and is not liable for any
> errors, defects, omissions, viruses or for resultant loss or damage,
> if any, direct or indirect."
>
>
>
>



Re: Query performance issue

From
Jayadevan M
Date:
Hello,

> Please run EXPLAIN ANALYZE on the query and post that, it's hard to say
> what's wrong from just the query plan, without knowing where the time is
> actually spent. And the schema of the tables involved, and any indexes
> on them. (see also
http://wiki.postgresql.org/wiki/SlowQueryQuestions)
The details of the tables and indexes may take a bit of effort to explain. Will do that.

I remembered that a similar query took about 90 seconds to run a few days ago. Now that is also taking a few minutes to run. In between, we made some changes to a few tables (the tables are about 9-10 GB each). This was to fix some issue in conversion from CHARACTER VARYING to BOOLEAN on PostgreSQL (some columns in Oracle were of type VARCHAR, to store BOOLEAN values. We changed that to BOOLEAN in PostgreSQL to resolve some issues at the jdbc level). The alters were of similar type -

ALTER TABLE cusdynatr ALTER tstflg TYPE boolean USING CASE WHEN tstflg = '1' THEN true WHEN tstflg = '0' then FALSE END;

Do such alters result in fragmentation at storage level?

Regards,
Jayadevan





DISCLAIMER:


"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."





Re: Query performance issue

From
Jayadevan M
Date:
Hello,
>
> Please run EXPLAIN ANALYZE on the query and post that, it's hard to say
> what's wrong from just the query plan, without knowing where the time is
> actually spent.

Here is the explain analyze
http://explain.depesz.com/s/MY1
Regards,
Jayadevan





DISCLAIMER:


"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."





Re: Query performance issue

From
Jayadevan M
Date:
Hello,

> >
> > Please run EXPLAIN ANALYZE on the query and post that, it's hard to say
> > what's wrong from just the query plan, without knowing where the time is
> > actually spent.
> Here is the explain analyze
>
http://explain.depesz.com/s/MY1
Going through the url tells me that statistics may be off. I will try analyzing the tables. That should help?

Regards,
Jayadevan





DISCLAIMER:


"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."





Re: Query performance issue

From
Venkat Balaji
Date:
Missed out looping in community...

On Wed, Aug 31, 2011 at 5:01 PM, Venkat Balaji <venkat.balaji@verse.in> wrote:
Could you help us know the tables and columns on which Indexes are built ?

Query is performing sorting based on key upper(column) and that is where i believe the cost is high.

The 'upper'  function is used up in the where clause?

Thanks
Venkat


On Wed, Aug 31, 2011 at 4:49 PM, Jayadevan M <Jayadevan.Maymala@ibsplc.com> wrote:
Hello,

> >
> > Please run EXPLAIN ANALYZE on the query and post that, it's hard to say
> > what's wrong from just the query plan, without knowing where the time is
> > actually spent.
> Here is the explain analyze
>
http://explain.depesz.com/s/MY1

Going through the url tells me that statistics may be off. I will try analyzing the tables. That should help?

Regards,
Jayadevan





DISCLAIMER:


"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."







Re: Query performance issue

From
"Tomas Vondra"
Date:
On 31 Srpen 2011, 13:19, Jayadevan M wrote:
> Hello,
>
>> >
>> > Please run EXPLAIN ANALYZE on the query and post that, it's hard to
> say
>> > what's wrong from just the query plan, without knowing where the time
> is
>> > actually spent.
>> Here is the explain analyze
>> http://explain.depesz.com/s/MY1
> Going through the url tells me that statistics may be off. I will try
> analyzing the tables. That should help?
> Regards,
> Jayadevan

That could help, but not necessarily.

A really interesting part is the sort near the bottom -

->  Sort  (cost=1895.95..1896.49 rows=215 width=61) (actual
time=25.926..711784.723 rows=2673340321 loops=1)
    Sort Key: memmst.memshpsta
    Sort Method:  quicksort  Memory: 206kB
    ->  Nested Loop  (cost=0.01..1887.62 rows=215 width=61) (actual
time=0.088..23.445 rows=1121 loops=1)

How can a sort ge 1121 rows at the input and return 2673340321 rows at the
output? Not sure where this comes from.

BTW what PostgreSQL version is this?

Tomas


Re: Query performance issue

From
Jayadevan M
Date:
>
> A really interesting part is the sort near the bottom -
>
> ->  Sort  (cost=1895.95..1896.49 rows=215 width=61) (actual
> time=25.926..711784.723 rows=2673340321 loops=1)
>     Sort Key: memmst.memshpsta
>     Sort Method:  quicksort  Memory: 206kB
>     ->  Nested Loop  (cost=0.01..1887.62 rows=215 width=61) (actual
> time=0.088..23.445 rows=1121 loops=1)
>
> How can a sort ge 1121 rows at the input and return 2673340321 rows at the
> output? Not sure where this comes from.
>
> BTW what PostgreSQL version is this?
PostgreSQL 9.0.4 on x86_64-pc-solaris2.10


Regards,
Jayadevan





DISCLAIMER:


"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."





Re: Query performance issue

From
"Kevin Grittner"
Date:
Jayadevan M  wrote:

>> And the schema of the tables involved, and any indexes on them.

> The details of the tables and indexes may take a bit of effort to
> explain.  Will do that.

In psql you can do \d to get a decent summary.

Without seeing the query and the table definitions, it's hard to give
advice; especially when a sort step increases the number of rows.
I'm guessing there is incorrect usage of some set-returning function.

-Kevin

Re: Query performance issue

From
Jayadevan
Date:
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.

Re: Query performance issue

From
Grzegorz Jaśkiewicz
Date:
Order by ...upper(xyz), do you have functional index on these ?

Re: Query performance issue

From
"Kevin Grittner"
Date:
Jayadevan M  wrote:

> Here is the explain analyze
> http://explain.depesz.com/s/MY1

> PostgreSQL 9.0.4 on x86_64-pc-solaris2.10

> work_mem = 96MB

Thanks for posting the query and related schema.  I tried working
through it, but I keep coming back to this sort, and wondering how a
sort can have 1121 rows as input and 2673340321 rows as output.  Does
anyone have any ideas on what could cause that?

  ->  Sort  (cost=1895.95..1896.49 rows=215 width=61)
            (actual time=25.926..711784.723
             rows=2673340321 loops=1)
        Sort Key: memmst.memshpsta
        Sort Method:  quicksort  Memory: 206kB
        ->  Nested Loop  (cost=0.01..1887.62 rows=215 width=61)
                         (actual time=0.088..23.445
                          rows=1121 loops=1)

-Kevin

Re: Query performance issue

From
Tom Lane
Date:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Thanks for posting the query and related schema.  I tried working
> through it, but I keep coming back to this sort, and wondering how a
> sort can have 1121 rows as input and 2673340321 rows as output.  Does
> anyone have any ideas on what could cause that?

Mergejoin rescan.  There really are only 1121 rows in the data, but
the parent merge join is pulling them over and over again --- evidently
there are a lot of equal keys in the data.  The EXPLAIN ANALYZE
machinery counts each fetch as a new row, even after a mark/restore.

The planner does know about that effect and will penalize merge joins
when it realizes there are a lot of duplicate keys in the input.  In
this case I'm thinking that the drastic underestimate of the size of the
other side of the join results in not penalizing the merge enough.

(On the other hand, hash joins don't like equal keys that much either...)

            regards, tom lane

Re: Query performance issue

From
Jayadevan
Date:
I don't think I understood all that. Anyway, is there a way to fix this -
either by rewriting the query or by creating an index? The output does match
what I am expecting.  It does take more than 10 times the time taken by
Oracle for the same result, with PostgreSQL taking more than 20 minutes. I
am sort of stuck on this since this query does get executed often. By the
way, changing the filter from FAMNAM to GIVENNAME fetches results in 90
seconds. Probably there is a difference in the cardinality of values in
these 2 columns.

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

Re: Query performance issue

From
"Tomas Vondra"
Date:
On 4 Září 2011, 20:06, Jayadevan wrote:
> I don't think I understood all that. Anyway, is there a way to fix this -
> either by rewriting the query or by creating an index? The output does
> match
> what I am expecting.  It does take more than 10 times the time taken by
> Oracle for the same result, with PostgreSQL taking more than 20 minutes. I
> am sort of stuck on this since this query does get executed often. By the
> way, changing the filter from FAMNAM to GIVENNAME fetches results in 90
> seconds. Probably there is a difference in the cardinality of values in
> these 2 columns.

Tom Lane explained why sort produces more rows (2673340321) than it gets
on the input (1121), or why it seems like that - it's a bit complicated
because of the merge join.

I'd try to increase statistics target - it's probably 100, change it to
1000, run ANALYZE and try the query (it may improve the plan without the
need to mess with the query).

If that does not help, you'll have to change the query probably. The
problem is the explain analyze you've provided
(http://explain.depesz.com/s/MY1) does not match the query from your
yesterday's post so we can't really help with it. I do have some ideas of
how to change the query, but it's really wild guessing without the query
plan.

Tomas


Re: Query performance issue

From
Jayadevan M
Date:
Hello,
>
> If that does not help, you'll have to change the query probably. The
> problem is the explain analyze you've provided
> (
http://explain.depesz.com/s/MY1) does not match the query from your
> yesterday's post so we can't really help with it.

Thanks for the pointers. I think I posted the same plan, may be the variable values changed. Anyway, I changed the query and now it comes back in 2 seconds. Here is the plan
http://explain.depesz.com/s/n9S
Interesting observation - PostgreSQL takes from 2 seconds to 20 minutes fetch the same data set of 2212 records, with slightly modified queries. Oracle is consistent (taking under 1 minute in both cases), though not consistently faster. The modified query is
SELECT PFLMST.MEMSHPNUM,
   PFLMST.MEMSHPTYP,
   ACCMST.PRGCOD,
   CNTINF.EMLADR,
   CNTINF.CELISDCOD,
   CNTINF.CELARACOD,
   CNTINF.CELNUM,
   CNTINF.ADRLINONE ,
   CNTINF.ZIPCOD,
   CNTINF.ADRTYP,
   (select ONE.FLDDES from COMONETIM ONE
 WHERE ONE.CMPCOD =ACCMST.CMPCOD
 AND ONE.FLDCOD='program.member.accountStatus'
 AND ONE.FLDVAL=ACCMST.ACCSTA)ACCSTA,
   (SELECT ONE1.FLDDES FROM COMONETIM ONE1
 WHERE ONE1.CMPCOD =ACCMST.CMPCOD
 AND ONE1.FLDCOD='common.member.membershipStatus'
 AND ONE1.FLDVAL=PFLMST.MEMSHPSTA )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
 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)

Regards,
Jayadevan





DISCLAIMER:


"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."





Re: Query performance issue

From
Jayadevan
Date:
Based on my initial hunch that something resulting from all the ALTERS was
making PostgreSQL planner end up with bad plans, I tried a pg_dump and
pg_restore. Now the 'bad' query comes back in 70 seconds (compared to 20
minutes earlier) and the rewritten query still comes back in 2 seconds. So
we will stick with the re-written query.

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