Thread: Query performance issue
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."
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."
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
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." > > > >
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."
> 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."
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."
>
> 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."
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."
> >
> > 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."
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?ThanksVenkatOn 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."
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
>
> 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."
> 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."
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
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.
Order by ...upper(xyz), do you have functional index on these ?
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
"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
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.
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
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."
>
> 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."
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.