agora inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedQuery performance issue
57+ messages / 27 participants
[nested] [flat]
* Query performance issue
@ 2007-07-24 07:48 Jonathan Gray <jgray@streamy.com>
0 siblings, 1 reply; 57+ messages in thread
From: Jonathan Gray @ 2007-07-24 07:48 UTC (permalink / raw)
To: pgsql-performance
We're experiencing a query performance problem related to the planner and
its ability to perform a specific type of merge.
We have created a test case (as attached, or here:
http://www3.streamy.com/postgres/indextest.sql) which involves a
hypothetical customer ordering system, with customers, orders, and customer
groups.
If we want to retrieve a single customers 10 most recent orders, sorted by
date, we can use a double index on (customer,date); Postgres's query planner
will use the double index with a backwards index scan on the second indexed
column (date).
However, if we want to retrieve a "customer class's" 10 most recent orders,
sorted by date, we are not able to get Postgres to use double indexes.
We have come to the conclusion that the fastest way to accomplish this type
of query is to merge, in sorted order, each customers set of orders (for
which we can use the double index). Using a heap to merge these ordered
lists (until we reach the limit) seems the most algorithmically efficient
way we are able to find. This is implemented in the attachment as a
pl/pythonu function.
Another less algorithmically efficient solution, but faster in practice for
many cases, is to fetch the full limit of orders from each customer, sort
these by date, and return up to the limit.
We are no masters of reading query plans, but for straight SQL queries the
planner seems to yield two different types of plan. They are fast in
certain cases but breakdown in our typical use cases, where the number of
orders per customer is sparse compared to the total number of orders across
the date range.
We are interested in whether a mechanism internal to Postgres can accomplish
this type of merging of indexed columns in sorted order.
If this cannot currently be accomplished (or if there is something we are
missing about why it shouldn't be) we would appreciate any pointers to be
able to translate our python heap approach into C functions integrated more
closely with Postgres. The python function incurs large constant costs
because of type conversions and repeated queries to the database.
Thanks for any help or direction.
Jonathan Gray / Miguel Simon
Attachments:
[application/octet-stream] indextest.sql (20.8K, ../../0f1701c7cdc6$f9cc1a30$ed644e90$@com/3-indextest.sql)
download
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2007-07-24 08:44 Chris <dmagick@gmail.com>
parent: Jonathan Gray <jgray@streamy.com>
0 siblings, 1 reply; 57+ messages in thread
From: Chris @ 2007-07-24 08:44 UTC (permalink / raw)
To: Jonathan Gray <jgray@streamy.com>; +Cc: pgsql-performance
Jonathan Gray wrote:
> We’re experiencing a query performance problem related to the planner
> and its ability to perform a specific type of merge.
>
>
>
> We have created a test case (as attached, or here:
> http://www3.streamy.com/postgres/indextest.sql) which involves a
> hypothetical customer ordering system, with customers, orders, and
> customer groups.
>
>
>
> If we want to retrieve a single customers 10 most recent orders, sorted
> by date, we can use a double index on (customer,date); Postgres’s query
> planner will use the double index with a backwards index scan on the
> second indexed column (date).
>
>
>
> However, if we want to retrieve a “customer class’s” 10 most recent
> orders, sorted by date, we are not able to get Postgres to use double
> indexes.
You don't have any indexes on the 'customerclass' table.
Creating a foreign key doesn't create an index, you need to do that
separately.
Try
create index cc_customerid_class on indextest.customerclass(classid,
customerid);
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2007-07-24 08:50 Chris <dmagick@gmail.com>
parent: Chris <dmagick@gmail.com>
0 siblings, 1 reply; 57+ messages in thread
From: Chris @ 2007-07-24 08:50 UTC (permalink / raw)
To: Jonathan Gray <jgray@streamy.com>; +Cc: pgsql-performance
Chris wrote:
> Jonathan Gray wrote:
>> We’re experiencing a query performance problem related to the planner
>> and its ability to perform a specific type of merge.
>>
>>
>>
>> We have created a test case (as attached, or here:
>> http://www3.streamy.com/postgres/indextest.sql) which involves a
>> hypothetical customer ordering system, with customers, orders, and
>> customer groups.
>>
>>
>>
>> If we want to retrieve a single customers 10 most recent orders,
>> sorted by date, we can use a double index on (customer,date);
>> Postgres’s query planner will use the double index with a backwards
>> index scan on the second indexed column (date).
>>
>>
>>
>> However, if we want to retrieve a “customer class’s” 10 most recent
>> orders, sorted by date, we are not able to get Postgres to use double
>> indexes.
>
> You don't have any indexes on the 'customerclass' table.
>
> Creating a foreign key doesn't create an index, you need to do that
> separately.
>
> Try
>
> create index cc_customerid_class on indextest.customerclass(classid,
> customerid);
>
It could also be that since you don't have very much data (10,000) rows
- postgres is ignoring the indexes because it'll be quicker to scan the
tables.
If you bump it up to say 100k rows, what happens?
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2007-07-24 09:18 Jonathan Gray <jgray@streamy.com>
parent: Chris <dmagick@gmail.com>
0 siblings, 1 reply; 57+ messages in thread
From: Jonathan Gray @ 2007-07-24 09:18 UTC (permalink / raw)
To: 'Chris' <dmagick@gmail.com>; +Cc: pgsql-performance
Chris,
Creating indexes on the customerclass table does speed up the queries but
still does not create the plan we are looking for (using the double index
with a backward index scan on the orders table).
The plans we now get, with times on par or slightly better than with the
plpgsql hack, are:
EXPLAIN ANALYZE
SELECT o.orderid,o.orderstamp FROM indextest.orders o
INNER JOIN indextest.customerclass cc ON (cc.classid = 2)
WHERE o.customerid = cc.customerid ORDER BY o.orderstamp DESC LIMIT 5;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------
Limit (cost=0.00..176.65 rows=5 width=12) (actual time=0.930..3.675 rows=5
loops=1)
-> Nested Loop (cost=0.00..46388.80 rows=1313 width=12) (actual
time=0.927..3.664 rows=5 loops=1)
-> Index Scan Backward using orders_orderstamp_idx on orders o
(cost=0.00..6225.26 rows=141001 width=16) (actual time=0.015..0.957 rows=433
loops=1)
-> Index Scan using customerclass_customerid_idx on customerclass
cc (cost=0.00..0.27 rows=1 width=4) (actual time=0.004..0.004 rows=0
loops=433)
Index Cond: (o.customerid = cc.customerid)
Filter: (classid = 2)
And
EXPLAIN ANALYZE
SELECT o.orderid,o.orderstamp FROM indextest.orders o
INNER JOIN indextest.customerclass cc ON (cc.classid = 2)
WHERE o.customerid = cc.customerid ORDER BY o.orderstamp DESC LIMIT 100;
QUERY
PLAN
----------------------------------------------------------------------------
---------------------------------------------------------------------------
Limit (cost=1978.80..1979.05 rows=100 width=12) (actual time=6.167..6.448
rows=100 loops=1)
-> Sort (cost=1978.80..1982.09 rows=1313 width=12) (actual
time=6.165..6.268 rows=100 loops=1)
Sort Key: o.orderstamp
-> Nested Loop (cost=3.99..1910.80 rows=1313 width=12) (actual
time=0.059..4.576 rows=939 loops=1)
-> Bitmap Heap Scan on customerclass cc (cost=3.99..55.16
rows=95 width=4) (actual time=0.045..0.194 rows=95 loops=1)
Recheck Cond: (classid = 2)
-> Bitmap Index Scan on customerclass_classid_idx
(cost=0.00..3.96 rows=95 width=0) (actual time=0.032..0.032 rows=95 loops=1)
Index Cond: (classid = 2)
-> Index Scan using orders_customerid_idx on orders o
(cost=0.00..19.35 rows=15 width=16) (actual time=0.006..0.025 rows=10
loops=95)
Index Cond: (o.customerid = cc.customerid)
As I said, this is a hypothetical test case we have arrived at that
describes our situation as best as we can given a simple case. We're
interested in potential issues with the approach, why postgres would not
attempt something like it, and how we might go about implementing it
ourselves at a lower level than we currently have (in SPI, libpq, etc).
If it could be generalized then we could use it in cases where we aren't
pulling from just one table (the orders table) but rather trying to merge,
in sorted order, results from different conditions on different tables.
Right now we use something like the plpgsql or plpythonu functions in the
example and they outperform our regular SQL queries by a fairly significant
margin.
An example might be:
SELECT * FROM (
(SELECT orderid,stamp FROM indextest.orders_usa WHERE customerid = <setof
customerids> ORDER BY stamp DESC LIMIT 5) UNION
(SELECT orderid,stamp FROM indextest.orders_can WHERE customerid = <setoff
customerids> ORDER BY stamp DESC LIMIT 5)
) as x ORDER BY x.stamp DESC
Again, that's a general example but some of my queries contain between 5 and
10 different sorted joins of this kind and it would be helpful to have
something internal in postgres to efficiently handle it (do something just
like the above query but not have to do the full LIMIT 5 for each set, some
kind of in order merge/heap join?)
Jonathan Gray
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Chris
Sent: Tuesday, July 24, 2007 1:51 AM
To: Jonathan Gray
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query performance issue
Chris wrote:
> Jonathan Gray wrote:
>> We're experiencing a query performance problem related to the planner
>> and its ability to perform a specific type of merge.
>>
>>
>>
>> We have created a test case (as attached, or here:
>> http://www3.streamy.com/postgres/indextest.sql) which involves a
>> hypothetical customer ordering system, with customers, orders, and
>> customer groups.
>>
>>
>>
>> If we want to retrieve a single customers 10 most recent orders,
>> sorted by date, we can use a double index on (customer,date);
>> Postgres's query planner will use the double index with a backwards
>> index scan on the second indexed column (date).
>>
>>
>>
>> However, if we want to retrieve a "customer class's" 10 most recent
>> orders, sorted by date, we are not able to get Postgres to use double
>> indexes.
>
> You don't have any indexes on the 'customerclass' table.
>
> Creating a foreign key doesn't create an index, you need to do that
> separately.
>
> Try
>
> create index cc_customerid_class on indextest.customerclass(classid,
> customerid);
>
It could also be that since you don't have very much data (10,000) rows
- postgres is ignoring the indexes because it'll be quicker to scan the
tables.
If you bump it up to say 100k rows, what happens?
--
Postgresql & php tutorials
http://www.designmagick.com/
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2007-07-24 09:36 Chris <dmagick@gmail.com>
parent: Jonathan Gray <jgray@streamy.com>
0 siblings, 1 reply; 57+ messages in thread
From: Chris @ 2007-07-24 09:36 UTC (permalink / raw)
To: Jonathan Gray <jgray@streamy.com>; +Cc: pgsql-performance
Jonathan Gray wrote:
> Chris,
>
> Creating indexes on the customerclass table does speed up the queries but
> still does not create the plan we are looking for (using the double index
> with a backward index scan on the orders table).
Stupid question - why is that particular plan your "goal" plan?
> The plans we now get, with times on par or slightly better than with the
> plpgsql hack, are:
>
> EXPLAIN ANALYZE
> SELECT o.orderid,o.orderstamp FROM indextest.orders o
> INNER JOIN indextest.customerclass cc ON (cc.classid = 2)
> WHERE o.customerid = cc.customerid ORDER BY o.orderstamp DESC LIMIT 5;
Didn't notice this before...
Shouldn't this be:
INNER JOIN indextest.customerclass cc ON (o.customerid = cc.customerid)
WHERE cc.classid = 2
ie join on the common field not the classid one which doesn't appear in
the 2nd table?
> As I said, this is a hypothetical test case we have arrived at that
> describes our situation as best as we can given a simple case. We're
> interested in potential issues with the approach, why postgres would not
> attempt something like it, and how we might go about implementing it
> ourselves at a lower level than we currently have (in SPI, libpq, etc).
>
> If it could be generalized then we could use it in cases where we aren't
> pulling from just one table (the orders table) but rather trying to merge,
> in sorted order, results from different conditions on different tables.
> Right now we use something like the plpgsql or plpythonu functions in the
> example and they outperform our regular SQL queries by a fairly significant
> margin.
I'm sure if you posted the queries you are running with relevant info
you'd get some help ;)
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2007-07-24 09:50 Jonathan Gray <jgray@streamy.com>
parent: Chris <dmagick@gmail.com>
0 siblings, 0 replies; 57+ messages in thread
From: Jonathan Gray @ 2007-07-24 09:50 UTC (permalink / raw)
To: 'Chris' <dmagick@gmail.com>; +Cc: pgsql-performance
That particular plan is our goal because we've "hacked" it together to
perform better than the normal sql plans. Analytically it makes sense to
approach this particular problem in this way because it is relatively
invariant to the distributions and sizes of the tables (with only having to
deal with increased index size).
Also, changing around the query doesn't change the query plan at all. The
planner is intelligent enough to figure out what it really needs to join on
despite my poor query writing. I originally had it this way to ensure my
(customerid,orderstamp) conditions were in the correct order but again
appears to not matter.
I will try to get a more complex/sophisticated test case running. I'm not
able to post my actual structure or queries but I'll try to produce a better
example of the other (multiple table) case tomorrow.
Thanks.
Jonathan Gray
-----Original Message-----
From: Chris [mailto:dmagick@gmail.com]
Sent: Tuesday, July 24, 2007 2:36 AM
To: Jonathan Gray
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query performance issue
Jonathan Gray wrote:
> Chris,
>
> Creating indexes on the customerclass table does speed up the queries but
> still does not create the plan we are looking for (using the double index
> with a backward index scan on the orders table).
Stupid question - why is that particular plan your "goal" plan?
> The plans we now get, with times on par or slightly better than with the
> plpgsql hack, are:
>
> EXPLAIN ANALYZE
> SELECT o.orderid,o.orderstamp FROM indextest.orders o
> INNER JOIN indextest.customerclass cc ON (cc.classid = 2)
> WHERE o.customerid = cc.customerid ORDER BY o.orderstamp DESC LIMIT 5;
Didn't notice this before...
Shouldn't this be:
INNER JOIN indextest.customerclass cc ON (o.customerid = cc.customerid)
WHERE cc.classid = 2
ie join on the common field not the classid one which doesn't appear in
the 2nd table?
> As I said, this is a hypothetical test case we have arrived at that
> describes our situation as best as we can given a simple case. We're
> interested in potential issues with the approach, why postgres would not
> attempt something like it, and how we might go about implementing it
> ourselves at a lower level than we currently have (in SPI, libpq, etc).
>
> If it could be generalized then we could use it in cases where we aren't
> pulling from just one table (the orders table) but rather trying to merge,
> in sorted order, results from different conditions on different tables.
> Right now we use something like the plpgsql or plpythonu functions in the
> example and they outperform our regular SQL queries by a fairly
significant
> margin.
I'm sure if you posted the queries you are running with relevant info
you'd get some help ;)
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 57+ messages in thread
* Query performance issue
@ 2011-08-31 09:00 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
0 siblings, 4 replies; 57+ messages in thread
From: Jayadevan M @ 2011-08-31 09:00 UTC (permalink / raw)
To: pgsql-performance
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."
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-08-31 09:34 Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
3 siblings, 2 replies; 57+ messages in thread
From: Heikki Linnakangas @ 2011-08-31 09:34 UTC (permalink / raw)
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-08-31 09:37 Sushant Sinha <sushant354@gmail.com>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
3 siblings, 0 replies; 57+ messages in thread
From: Sushant Sinha @ 2011-08-31 09:37 UTC (permalink / raw)
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>; +Cc: pgsql-performance
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."
>
>
>
>
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-08-31 10:07 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
parent: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
1 sibling, 0 replies; 57+ messages in thread
From: Jayadevan M @ 2011-08-31 10:07 UTC (permalink / raw)
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
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."
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-08-31 10:51 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
parent: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
1 sibling, 1 reply; 57+ messages in thread
From: Jayadevan M @ 2011-08-31 10:51 UTC (permalink / raw)
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
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."
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-08-31 11:19 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
0 siblings, 2 replies; 57+ messages in thread
From: Jayadevan M @ 2011-08-31 11:19 UTC (permalink / raw)
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
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."
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-08-31 11:32 Venkat Balaji <venkat.balaji@verse.in>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
1 sibling, 0 replies; 57+ messages in thread
From: Venkat Balaji @ 2011-08-31 11:32 UTC (permalink / raw)
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
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."
>>
>>
>>
>>
>>
>
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-08-31 11:41 Tomas Vondra <tv@fuzzy.cz>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
1 sibling, 1 reply; 57+ messages in thread
From: Tomas Vondra @ 2011-08-31 11:41 UTC (permalink / raw)
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>; +Cc: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>; pgsql-performance; pgsql-performance-owner@postgresql.org
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
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-08-31 11:57 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 57+ messages in thread
From: Jayadevan M @ 2011-08-31 11:57 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
>
> 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."
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-08-31 12:40 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
3 siblings, 1 reply; 57+ messages in thread
From: Kevin Grittner @ 2011-08-31 12:40 UTC (permalink / raw)
To: heikki.linnakangas@enterprisedb.com; Jayadevan.Maymala@ibsplc.com; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
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
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-09-03 04:48 Jayadevan <Jayadevan.Maymala@ibsplc.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 57+ messages in thread
From: Jayadevan @ 2011-09-03 04:48 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-09-04 10:38 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Jayadevan <Jayadevan.Maymala@ibsplc.com>
0 siblings, 0 replies; 57+ messages in thread
From: Grzegorz Jaśkiewicz @ 2011-09-04 10:38 UTC (permalink / raw)
To: Jayadevan <Jayadevan.Maymala@ibsplc.com>; +Cc: pgsql-performance
Order by ...upper(xyz), do you have functional index on these ?
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-09-04 14:30 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
3 siblings, 1 reply; 57+ messages in thread
From: Kevin Grittner @ 2011-09-04 14:30 UTC (permalink / raw)
To: heikki.linnakangas@enterprisedb.com; Jayadevan.Maymala@ibsplc.com; +Cc: pgsql-performance; pgsql-performance-owner@postgresql.org
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
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-09-04 15:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 57+ messages in thread
From: Tom Lane @ 2011-09-04 15:18 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: heikki.linnakangas@enterprisedb.com; Jayadevan.Maymala@ibsplc.com; pgsql-performance; pgsql-performance-owner@postgresql.org
"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
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-09-04 18:06 Jayadevan <Jayadevan.Maymala@ibsplc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 57+ messages in thread
From: Jayadevan @ 2011-09-04 18:06 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-09-04 20:18 Tomas Vondra <tv@fuzzy.cz>
parent: Jayadevan <Jayadevan.Maymala@ibsplc.com>
0 siblings, 1 reply; 57+ messages in thread
From: Tomas Vondra @ 2011-09-04 20:18 UTC (permalink / raw)
To: Jayadevan <Jayadevan.Maymala@ibsplc.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-09-05 04:19 Jayadevan M <Jayadevan.Maymala@ibsplc.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 57+ messages in thread
From: Jayadevan M @ 2011-09-05 04:19 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance
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."
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2011-09-06 03:30 Jayadevan <Jayadevan.Maymala@ibsplc.com>
parent: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
0 siblings, 0 replies; 57+ messages in thread
From: Jayadevan @ 2011-09-06 03:30 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 57+ messages in thread
* query performance issue
@ 2017-11-15 09:33 Samir Magar <samirmagar8@gmail.com>
0 siblings, 2 replies; 57+ messages in thread
From: Samir Magar @ 2017-11-15 09:33 UTC (permalink / raw)
To: pgsql-performance
Hello,
I am having performance issues with one of the query.
The query is taking 39 min to fetch 3.5 mil records.
I want to reduce that time to 15 mins.
could you please suggest something to its performance?
server configuration:
CPUs = 4
memory = 16 GM
shared_buffers = 3 GB
work_mem = 100MB
effective_cache_size = 12 GB
we are doing the vacuum/analyze regularly on the database.
attached is the query with its explain plan.
Thanks,
Samir Magar
query:
SELECT
DISTINCT
DLR_QLFY.DLR_QLFY_ID as DLR_QLFY_ID, NMQ_REQ.GRACE_PRD as GRACE_PRD, NMQ_REQ.HIDE_PRG_FLG as HIDE_PRG_FLG, NMQ_REQ.NTFY_DLR_FLG as NTFY_DLR_FLG, DLR_LOC.ACCT_NUM as ACCT_NUM, NMQ_REQ.NMQ_REQ_ID as NMQ_REQ_ID, NEW_MDL.PI_MDL_ID as PI_MDL_ID
FROM test.DLR_QLFY INNER JOIN
(SELECT DLR_GRP.DLR_GRP_ID AS LOC_GRP_ID,LEAD_DLR_LOC_ID,DLR_LOC.ACCT_NUM AS LOC_ACCT_NUM FROM test.DLR_GRP, test.DLR_GRP_DLR_XREF, test.DLR_LOC WHERE DLR_GRP.DLR_GRP_ID=DLR_GRP_DLR_XREF.DLR_GRP_ID AND DLR_GRP_DLR_XREF.DLR_LOC_ID=DLR_LOC.DLR_LOC_ID AND (DLR_GRP.DLR_GRP_TYP='LOC' OR DLR_GRP.DLR_GRP_TYP='COG') AND DLR_LOC.IS_ACTV='Y' ) LOC_GRP
ON DLR_QLFY.QLFY_GRP_ID=LOC_GRP.LOC_GRP_ID
INNER JOIN (SELECT DLR_GRP.DLR_GRP_ID AS COG_GRP_ID,LEAD_DLR_LOC_ID,DLR_LOC.ACCT_NUM AS COG_ACCT_NUM FROM test.DLR_GRP,test.DLR_GRP_DLR_XREF,test.DLR_LOC WHERE DLR_GRP.DLR_GRP_ID=DLR_GRP_DLR_XREF.DLR_GRP_ID AND DLR_GRP_DLR_XREF.DLR_LOC_ID=DLR_LOC.DLR_LOC_ID AND DLR_GRP.DLR_GRP_TYP='COG' AND DLR_LOC.IS_ACTV='Y' ) COG_GRP
ON DLR_QLFY.COG_GRP_ID=COG_GRP.COG_GRP_ID
INNER JOIN test.DLR_LOC
ON DLR_LOC.ACCT_NUM=LOC_GRP.LOC_ACCT_NUM
AND DLR_LOC.ACCT_NUM=COG_GRP.COG_ACCT_NUM
INNER JOIN test.DLR_LOC LEAD_LOC
ON LEAD_LOC.DLR_LOC_ID=COG_GRP.LEAD_DLR_LOC_ID
AND LEAD_LOC.ACCT_NUM=LEAD_LOC.COG_PARNT_ACCT
INNER JOIN test.DLR_LOC COG_LEAD
ON COG_LEAD.DLR_LOC_ID=COG_GRP.LEAD_DLR_LOC_ID
INNER JOIN test.NMQ_REQ
ON DLR_QLFY.NMQ_REQ_ID=NMQ_REQ.NMQ_REQ_ID
INNER JOIN test.NEW_MDL
ON NMQ_REQ.NEW_MDL_ID = NEW_MDL.NEW_MDL_ID
INNER JOIN test.STG_ACFLX_NMQ_DLRS
ON COG_LEAD.ACCT_NUM=STG_ACFLX_NMQ_DLRS.RLTNP_LEAD_ACCT
AND STG_ACFLX_NMQ_DLRS.ACCT_ID=DLR_LOC.ACCT_NUM
WHERE
DLR_LOC.IS_ACTV='Y'
AND DLR_QLFY.QLF_FLG='N'
AND NMQ_REQ.PGM_DSBL_FLG != 'Y'
AND (NMQ_REQ.PGM_START_DT <= CURRENT_DATE
AND NMQ_REQ.PGM_END_DT > CURRENT_DATE)
AND DLR_QLFY.DLR_QLFY_ID NOT IN (SELECT DLR_QLFY.DLR_QLFY_ID FROM test.DLR_QLFY WHERE QLF_FLG='Y' AND DLR_QLFY.NMQ_REQ_ID=NMQ_REQ.NMQ_REQ_ID);
---------------------------------------------------------------------------
access plan
"HashAggregate (cost=4538.33..4538.34 rows=1 width=27)"
" Group Key: dlr_qlfy.dlr_qlfy_id, nmq_req.grace_prd, nmq_req.hide_prg_flg, nmq_req.ntfy_dlr_flg, dlr_loc.acct_num, nmq_req.nmq_req_id, new_mdl.pi_mdl_id"
" -> Nested Loop (cost=3.59..4538.31 rows=1 width=27)"
" -> Nested Loop (cost=3.31..4537.94 rows=1 width=27)"
" -> Nested Loop (cost=3.03..4530.16 rows=1 width=15)"
" Join Filter: (lead_loc.dlr_loc_id = dlr_grp_1.lead_dlr_loc_id)"
" -> Nested Loop (cost=0.58..1438.27 rows=263 width=15)"
" -> Nested Loop (cost=0.29..1306.78 rows=169 width=15)"
" -> Seq Scan on dlr_loc lead_loc (cost=0.00..757.12 rows=169 width=4)"
" Filter: (acct_num = cog_parnt_acct)"
" -> Index Only Scan using "IDX_101" on dlr_loc cog_lead (cost=0.29..3.24 rows=1 width=11)"
" Index Cond: (dlr_loc_id = lead_loc.dlr_loc_id)"
" -> Index Scan using idx_14 on stg_acflx_nmq_dlrs (cost=0.29..0.63 rows=15 width=14)"
" Index Cond: (rltnp_lead_acct = cog_lead.acct_num)"
" -> Nested Loop (cost=2.45..11.74 rows=1 width=33)"
" -> Index Only Scan using idx3 on dlr_grp dlr_grp_1 (cost=0.29..0.32 rows=1 width=8)"
" Index Cond: ((lead_dlr_loc_id = cog_lead.dlr_loc_id) AND (dlr_grp_typ = 'COG'::bpchar))"
" -> Nested Loop (cost=2.17..11.41 rows=1 width=37)"
" Join Filter: (dlr_loc_2.acct_num = dlr_loc.acct_num)"
" -> Nested Loop (cost=0.58..0.77 rows=1 width=11)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_2 (cost=0.29..0.32 rows=1 width=11)"
" Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref dlr_grp_dlr_xref_1 (cost=0.29..0.44 rows=1 width=8)"
" Index Cond: ((dlr_loc_id = dlr_loc_2.dlr_loc_id) AND (dlr_grp_id = dlr_grp_1.dlr_grp_id))"
" -> Nested Loop (cost=1.58..10.63 rows=1 width=26)"
" -> Index Only Scan using idx_102 on dlr_loc (cost=0.29..0.32 rows=1 width=7)"
" Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))"
" -> Nested Loop (cost=1.29..10.30 rows=1 width=19)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_1 (cost=0.29..0.34 rows=1 width=11)"
" Index Cond: ((acct_num = dlr_loc.acct_num) AND (is_actv = 'Y'::bpchar))"
" -> Nested Loop (cost=1.00..9.95 rows=1 width=16)"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref (cost=0.29..0.35 rows=2 width=8)"
" Index Cond: (dlr_loc_id = dlr_loc_1.dlr_loc_id)"
" -> Nested Loop (cost=0.71..4.79 rows=1 width=20)"
" -> Index Scan using idxdg3 on dlr_grp (cost=0.29..0.33 rows=1 width=4)"
" Index Cond: (dlr_grp_id = dlr_grp_dlr_xref.dlr_grp_id)"
" Filter: ((dlr_grp_typ = 'LOC'::bpchar) OR (dlr_grp_typ = 'COG'::bpchar))"
" -> Index Only Scan using idxdq7 on dlr_qlfy (cost=0.43..4.45 rows=1 width=16)"
" Index Cond: ((qlfy_grp_id = dlr_grp.dlr_grp_id) AND (qlf_flg = 'N'::bpchar) AND (cog_grp_id = dlr_grp_dlr_xref_1.dlr_grp_id))"
" -> Index Scan using p_key_29 on nmq_req (cost=0.28..7.77 rows=1 width=16)"
" Index Cond: (nmq_req_id = dlr_qlfy.nmq_req_id)"
" Filter: ((pgm_dsbl_flg <> 'Y'::bpchar) AND (pgm_start_dt <= ('now'::cstring)::date) AND (pgm_end_dt > ('now'::cstring)::date) AND (NOT (SubPlan 1)))"
" SubPlan 1"
" -> Index Only Scan using idx11 on dlr_qlfy dlr_qlfy_1 (cost=0.43..13.81 rows=269 width=4)"
" Index Cond: ((nmq_req_id = nmq_req.nmq_req_id) AND (qlf_flg = 'Y'::bpchar))"
" -> Index Scan using idx1 on new_mdl (cost=0.28..0.37 rows=1 width=8)"
" Index Cond: (new_mdl_id = nmq_req.new_mdl_id)"
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
[text/plain] query with access plan.txt (6.9K, ../../CAA=to3gXpr94TqSAYXT9zwxYwY_xKrLFnu8ZJNgJKP-kWza2CA@mail.gmail.com/3-query%20with%20access%20plan.txt)
download | inline:
query:
SELECT
DISTINCT
DLR_QLFY.DLR_QLFY_ID as DLR_QLFY_ID, NMQ_REQ.GRACE_PRD as GRACE_PRD, NMQ_REQ.HIDE_PRG_FLG as HIDE_PRG_FLG, NMQ_REQ.NTFY_DLR_FLG as NTFY_DLR_FLG, DLR_LOC.ACCT_NUM as ACCT_NUM, NMQ_REQ.NMQ_REQ_ID as NMQ_REQ_ID, NEW_MDL.PI_MDL_ID as PI_MDL_ID
FROM test.DLR_QLFY INNER JOIN
(SELECT DLR_GRP.DLR_GRP_ID AS LOC_GRP_ID,LEAD_DLR_LOC_ID,DLR_LOC.ACCT_NUM AS LOC_ACCT_NUM FROM test.DLR_GRP, test.DLR_GRP_DLR_XREF, test.DLR_LOC WHERE DLR_GRP.DLR_GRP_ID=DLR_GRP_DLR_XREF.DLR_GRP_ID AND DLR_GRP_DLR_XREF.DLR_LOC_ID=DLR_LOC.DLR_LOC_ID AND (DLR_GRP.DLR_GRP_TYP='LOC' OR DLR_GRP.DLR_GRP_TYP='COG') AND DLR_LOC.IS_ACTV='Y' ) LOC_GRP
ON DLR_QLFY.QLFY_GRP_ID=LOC_GRP.LOC_GRP_ID
INNER JOIN (SELECT DLR_GRP.DLR_GRP_ID AS COG_GRP_ID,LEAD_DLR_LOC_ID,DLR_LOC.ACCT_NUM AS COG_ACCT_NUM FROM test.DLR_GRP,test.DLR_GRP_DLR_XREF,test.DLR_LOC WHERE DLR_GRP.DLR_GRP_ID=DLR_GRP_DLR_XREF.DLR_GRP_ID AND DLR_GRP_DLR_XREF.DLR_LOC_ID=DLR_LOC.DLR_LOC_ID AND DLR_GRP.DLR_GRP_TYP='COG' AND DLR_LOC.IS_ACTV='Y' ) COG_GRP
ON DLR_QLFY.COG_GRP_ID=COG_GRP.COG_GRP_ID
INNER JOIN test.DLR_LOC
ON DLR_LOC.ACCT_NUM=LOC_GRP.LOC_ACCT_NUM
AND DLR_LOC.ACCT_NUM=COG_GRP.COG_ACCT_NUM
INNER JOIN test.DLR_LOC LEAD_LOC
ON LEAD_LOC.DLR_LOC_ID=COG_GRP.LEAD_DLR_LOC_ID
AND LEAD_LOC.ACCT_NUM=LEAD_LOC.COG_PARNT_ACCT
INNER JOIN test.DLR_LOC COG_LEAD
ON COG_LEAD.DLR_LOC_ID=COG_GRP.LEAD_DLR_LOC_ID
INNER JOIN test.NMQ_REQ
ON DLR_QLFY.NMQ_REQ_ID=NMQ_REQ.NMQ_REQ_ID
INNER JOIN test.NEW_MDL
ON NMQ_REQ.NEW_MDL_ID = NEW_MDL.NEW_MDL_ID
INNER JOIN test.STG_ACFLX_NMQ_DLRS
ON COG_LEAD.ACCT_NUM=STG_ACFLX_NMQ_DLRS.RLTNP_LEAD_ACCT
AND STG_ACFLX_NMQ_DLRS.ACCT_ID=DLR_LOC.ACCT_NUM
WHERE
DLR_LOC.IS_ACTV='Y'
AND DLR_QLFY.QLF_FLG='N'
AND NMQ_REQ.PGM_DSBL_FLG != 'Y'
AND (NMQ_REQ.PGM_START_DT <= CURRENT_DATE
AND NMQ_REQ.PGM_END_DT > CURRENT_DATE)
AND DLR_QLFY.DLR_QLFY_ID NOT IN (SELECT DLR_QLFY.DLR_QLFY_ID FROM test.DLR_QLFY WHERE QLF_FLG='Y' AND DLR_QLFY.NMQ_REQ_ID=NMQ_REQ.NMQ_REQ_ID);
---------------------------------------------------------------------------
access plan
"HashAggregate (cost=4538.33..4538.34 rows=1 width=27)"
" Group Key: dlr_qlfy.dlr_qlfy_id, nmq_req.grace_prd, nmq_req.hide_prg_flg, nmq_req.ntfy_dlr_flg, dlr_loc.acct_num, nmq_req.nmq_req_id, new_mdl.pi_mdl_id"
" -> Nested Loop (cost=3.59..4538.31 rows=1 width=27)"
" -> Nested Loop (cost=3.31..4537.94 rows=1 width=27)"
" -> Nested Loop (cost=3.03..4530.16 rows=1 width=15)"
" Join Filter: (lead_loc.dlr_loc_id = dlr_grp_1.lead_dlr_loc_id)"
" -> Nested Loop (cost=0.58..1438.27 rows=263 width=15)"
" -> Nested Loop (cost=0.29..1306.78 rows=169 width=15)"
" -> Seq Scan on dlr_loc lead_loc (cost=0.00..757.12 rows=169 width=4)"
" Filter: (acct_num = cog_parnt_acct)"
" -> Index Only Scan using "IDX_101" on dlr_loc cog_lead (cost=0.29..3.24 rows=1 width=11)"
" Index Cond: (dlr_loc_id = lead_loc.dlr_loc_id)"
" -> Index Scan using idx_14 on stg_acflx_nmq_dlrs (cost=0.29..0.63 rows=15 width=14)"
" Index Cond: (rltnp_lead_acct = cog_lead.acct_num)"
" -> Nested Loop (cost=2.45..11.74 rows=1 width=33)"
" -> Index Only Scan using idx3 on dlr_grp dlr_grp_1 (cost=0.29..0.32 rows=1 width=8)"
" Index Cond: ((lead_dlr_loc_id = cog_lead.dlr_loc_id) AND (dlr_grp_typ = 'COG'::bpchar))"
" -> Nested Loop (cost=2.17..11.41 rows=1 width=37)"
" Join Filter: (dlr_loc_2.acct_num = dlr_loc.acct_num)"
" -> Nested Loop (cost=0.58..0.77 rows=1 width=11)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_2 (cost=0.29..0.32 rows=1 width=11)"
" Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref dlr_grp_dlr_xref_1 (cost=0.29..0.44 rows=1 width=8)"
" Index Cond: ((dlr_loc_id = dlr_loc_2.dlr_loc_id) AND (dlr_grp_id = dlr_grp_1.dlr_grp_id))"
" -> Nested Loop (cost=1.58..10.63 rows=1 width=26)"
" -> Index Only Scan using idx_102 on dlr_loc (cost=0.29..0.32 rows=1 width=7)"
" Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))"
" -> Nested Loop (cost=1.29..10.30 rows=1 width=19)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_1 (cost=0.29..0.34 rows=1 width=11)"
" Index Cond: ((acct_num = dlr_loc.acct_num) AND (is_actv = 'Y'::bpchar))"
" -> Nested Loop (cost=1.00..9.95 rows=1 width=16)"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref (cost=0.29..0.35 rows=2 width=8)"
" Index Cond: (dlr_loc_id = dlr_loc_1.dlr_loc_id)"
" -> Nested Loop (cost=0.71..4.79 rows=1 width=20)"
" -> Index Scan using idxdg3 on dlr_grp (cost=0.29..0.33 rows=1 width=4)"
" Index Cond: (dlr_grp_id = dlr_grp_dlr_xref.dlr_grp_id)"
" Filter: ((dlr_grp_typ = 'LOC'::bpchar) OR (dlr_grp_typ = 'COG'::bpchar))"
" -> Index Only Scan using idxdq7 on dlr_qlfy (cost=0.43..4.45 rows=1 width=16)"
" Index Cond: ((qlfy_grp_id = dlr_grp.dlr_grp_id) AND (qlf_flg = 'N'::bpchar) AND (cog_grp_id = dlr_grp_dlr_xref_1.dlr_grp_id))"
" -> Index Scan using p_key_29 on nmq_req (cost=0.28..7.77 rows=1 width=16)"
" Index Cond: (nmq_req_id = dlr_qlfy.nmq_req_id)"
" Filter: ((pgm_dsbl_flg <> 'Y'::bpchar) AND (pgm_start_dt <= ('now'::cstring)::date) AND (pgm_end_dt > ('now'::cstring)::date) AND (NOT (SubPlan 1)))"
" SubPlan 1"
" -> Index Only Scan using idx11 on dlr_qlfy dlr_qlfy_1 (cost=0.43..13.81 rows=269 width=4)"
" Index Cond: ((nmq_req_id = nmq_req.nmq_req_id) AND (qlf_flg = 'Y'::bpchar))"
" -> Index Scan using idx1 on new_mdl (cost=0.28..0.37 rows=1 width=8)"
" Index Cond: (new_mdl_id = nmq_req.new_mdl_id)"
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: query performance issue
@ 2017-11-15 09:43 Pavel Stehule <pavel.stehule@gmail.com>
parent: Samir Magar <samirmagar8@gmail.com>
1 sibling, 1 reply; 57+ messages in thread
From: Pavel Stehule @ 2017-11-15 09:43 UTC (permalink / raw)
To: Samir Magar <samirmagar8@gmail.com>; +Cc: pgsql-performance
Hi
please send EXPLAIN ANALYZE output.
Regards
Pavel
2017-11-15 10:33 GMT+01:00 Samir Magar <samirmagar8@gmail.com>:
> Hello,
> I am having performance issues with one of the query.
> The query is taking 39 min to fetch 3.5 mil records.
>
> I want to reduce that time to 15 mins.
> could you please suggest something to its performance?
>
> server configuration:
> CPUs = 4
> memory = 16 GM
> shared_buffers = 3 GB
> work_mem = 100MB
> effective_cache_size = 12 GB
>
> we are doing the vacuum/analyze regularly on the database.
>
> attached is the query with its explain plan.
>
> Thanks,
> Samir Magar
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: query performance issue
@ 2017-11-15 12:54 Samir Magar <samirmagar8@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 57+ messages in thread
From: Samir Magar @ 2017-11-15 12:54 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-performance
please find the EXPLAIN ANALYZE output.
On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
> Hi
>
> please send EXPLAIN ANALYZE output.
>
> Regards
>
> Pavel
>
> 2017-11-15 10:33 GMT+01:00 Samir Magar <samirmagar8@gmail.com>:
>
>> Hello,
>> I am having performance issues with one of the query.
>> The query is taking 39 min to fetch 3.5 mil records.
>>
>> I want to reduce that time to 15 mins.
>> could you please suggest something to its performance?
>>
>> server configuration:
>> CPUs = 4
>> memory = 16 GM
>> shared_buffers = 3 GB
>> work_mem = 100MB
>> effective_cache_size = 12 GB
>>
>> we are doing the vacuum/analyze regularly on the database.
>>
>> attached is the query with its explain plan.
>>
>> Thanks,
>> Samir Magar
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>>
>
"HashAggregate (cost=4459.68..4459.69 rows=1 width=27) (actual time=2890035.403..2892173.601 rows=3489861 loops=1)"
" Group Key: dlr_qlfy.dlr_qlfy_id, nmq_req.grace_prd, nmq_req.hide_prg_flg, nmq_req.ntfy_dlr_flg, dlr_loc.acct_num, nmq_req.nmq_req_id, new_mdl.pi_mdl_id"
" -> Nested Loop (cost=3.59..4459.67 rows=1 width=27) (actual time=0.228..2864594.177 rows=12321289 loops=1)"
" -> Nested Loop (cost=3.31..4459.29 rows=1 width=27) (actual time=0.221..2819927.249 rows=12321289 loops=1)"
" -> Nested Loop (cost=3.03..4451.45 rows=1 width=15) (actual time=0.158..36816.304 rows=12612983 loops=1)"
" Join Filter: (lead_loc.dlr_loc_id = dlr_grp_1.lead_dlr_loc_id)"
" -> Nested Loop (cost=0.58..1358.94 rows=263 width=15) (actual time=0.046..363.150 rows=52261 loops=1)"
" -> Nested Loop (cost=0.29..1227.46 rows=169 width=15) (actual time=0.024..86.909 rows=12151 loops=1)"
" -> Seq Scan on dlr_loc lead_loc (cost=0.00..757.80 rows=169 width=4) (actual time=0.010..31.028 rows=12151 loops=1)"
" Filter: (acct_num = cog_parnt_acct)"
" Rows Removed by Filter: 21593"
" -> Index Only Scan using "IDX_101" on dlr_loc cog_lead (cost=0.29..2.77 rows=1 width=11) (actual time=0.003..0.004 rows=1 loops=12151)"
" Index Cond: (dlr_loc_id = lead_loc.dlr_loc_id)"
" Heap Fetches: 0"
" -> Index Scan using idx_14 on stg_acflx_nmq_dlrs (cost=0.29..0.63 rows=15 width=14) (actual time=0.008..0.019 rows=4 loops=12151)"
" Index Cond: (rltnp_lead_acct = cog_lead.acct_num)"
" -> Nested Loop (cost=2.45..11.75 rows=1 width=33) (actual time=0.058..0.615 rows=241 loops=52261)"
" -> Index Only Scan using idx3 on dlr_grp dlr_grp_1 (cost=0.29..0.32 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=52261)"
" Index Cond: ((lead_dlr_loc_id = cog_lead.dlr_loc_id) AND (dlr_grp_typ = 'COG'::bpchar))"
" Heap Fetches: 0"
" -> Nested Loop (cost=2.17..11.42 rows=1 width=37) (actual time=0.051..0.530 rows=236 loops=53436)"
" Join Filter: (dlr_loc_2.acct_num = dlr_loc.acct_num)"
" -> Nested Loop (cost=0.58..0.77 rows=1 width=11) (actual time=0.015..0.016 rows=1 loops=53436)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_2 (cost=0.29..0.32 rows=1 width=11) (actual time=0.009..0.009 rows=1 loops=53436)"
" Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref dlr_grp_dlr_xref_1 (cost=0.29..0.43 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=53402)"
" Index Cond: ((dlr_loc_id = dlr_loc_2.dlr_loc_id) AND (dlr_grp_id = dlr_grp_1.dlr_grp_id))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.58..10.64 rows=1 width=26) (actual time=0.036..0.425 rows=243 loops=51988)"
" -> Index Only Scan using idx10 on dlr_loc (cost=0.29..0.32 rows=1 width=7) (actual time=0.009..0.009 rows=1 loops=51988)"
" Index Cond: ((is_actv = 'Y'::bpchar) AND (acct_num = stg_acflx_nmq_dlrs.acct_id))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.29..10.30 rows=1 width=19) (actual time=0.026..0.354 rows=243 loops=51988)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_1 (cost=0.29..0.34 rows=1 width=11) (actual time=0.006..0.006 rows=1 loops=51988)"
" Index Cond: ((acct_num = dlr_loc.acct_num) AND (is_actv = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.00..9.95 rows=1 width=16) (actual time=0.019..0.273 rows=243 loops=51988)"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref (cost=0.29..0.35 rows=2 width=8) (actual time=0.002..0.003 rows=2 loops=51988)"
" Index Cond: (dlr_loc_id = dlr_loc_1.dlr_loc_id)"
" Heap Fetches: 0"
" -> Nested Loop (cost=0.71..4.79 rows=1 width=20) (actual time=0.015..0.105 rows=121 loops=103987)"
" -> Index Scan using idxdg3 on dlr_grp (cost=0.29..0.33 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=103987)"
" Index Cond: (dlr_grp_id = dlr_grp_dlr_xref.dlr_grp_id)"
" Filter: ((dlr_grp_typ = 'LOC'::bpchar) OR (dlr_grp_typ = 'COG'::bpchar))"
" -> Index Only Scan using idxdq7 on dlr_qlfy (cost=0.43..4.45 rows=1 width=16) (actual time=0.009..0.066 rows=121 loops=103987)"
" Index Cond: ((qlfy_grp_id = dlr_grp.dlr_grp_id) AND (qlf_flg = 'N'::bpchar) AND (cog_grp_id = dlr_grp_dlr_xref_1.dlr_grp_id))"
" Heap Fetches: 0"
" -> Index Scan using p_key_29 on nmq_req (cost=0.28..7.83 rows=1 width=16) (actual time=0.219..0.220 rows=1 loops=12612983)"
" Index Cond: (nmq_req_id = dlr_qlfy.nmq_req_id)"
" Filter: ((pgm_dsbl_flg <> 'Y'::bpchar) AND (pgm_start_dt <= ('now'::cstring)::date) AND (pgm_end_dt > ('now'::cstring)::date) AND (NOT (SubPlan 1)))"
" Rows Removed by Filter: 0"
" SubPlan 1"
" -> Index Only Scan using idx11 on dlr_qlfy dlr_qlfy_1 (cost=0.43..13.91 rows=274 width=4) (actual time=0.008..0.153 rows=576 loops=12321289)"
" Index Cond: ((nmq_req_id = nmq_req.nmq_req_id) AND (qlf_flg = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Index Scan using idx1 on new_mdl (cost=0.28..0.37 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=12321289)"
" Index Cond: (new_mdl_id = nmq_req.new_mdl_id)"
"Planning time: 69.774 ms"
"Execution time: 2892445.829 ms"
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
[text/plain] explain with analyze.txt (6.9K, ../../CAA=to3gnNReZ62m3K3qbN_KL8Hnmrg4wfb4LefZYqUa9v4OOSQ@mail.gmail.com/3-explain%20with%20analyze.txt)
download | inline:
"HashAggregate (cost=4459.68..4459.69 rows=1 width=27) (actual time=2890035.403..2892173.601 rows=3489861 loops=1)"
" Group Key: dlr_qlfy.dlr_qlfy_id, nmq_req.grace_prd, nmq_req.hide_prg_flg, nmq_req.ntfy_dlr_flg, dlr_loc.acct_num, nmq_req.nmq_req_id, new_mdl.pi_mdl_id"
" -> Nested Loop (cost=3.59..4459.67 rows=1 width=27) (actual time=0.228..2864594.177 rows=12321289 loops=1)"
" -> Nested Loop (cost=3.31..4459.29 rows=1 width=27) (actual time=0.221..2819927.249 rows=12321289 loops=1)"
" -> Nested Loop (cost=3.03..4451.45 rows=1 width=15) (actual time=0.158..36816.304 rows=12612983 loops=1)"
" Join Filter: (lead_loc.dlr_loc_id = dlr_grp_1.lead_dlr_loc_id)"
" -> Nested Loop (cost=0.58..1358.94 rows=263 width=15) (actual time=0.046..363.150 rows=52261 loops=1)"
" -> Nested Loop (cost=0.29..1227.46 rows=169 width=15) (actual time=0.024..86.909 rows=12151 loops=1)"
" -> Seq Scan on dlr_loc lead_loc (cost=0.00..757.80 rows=169 width=4) (actual time=0.010..31.028 rows=12151 loops=1)"
" Filter: (acct_num = cog_parnt_acct)"
" Rows Removed by Filter: 21593"
" -> Index Only Scan using "IDX_101" on dlr_loc cog_lead (cost=0.29..2.77 rows=1 width=11) (actual time=0.003..0.004 rows=1 loops=12151)"
" Index Cond: (dlr_loc_id = lead_loc.dlr_loc_id)"
" Heap Fetches: 0"
" -> Index Scan using idx_14 on stg_acflx_nmq_dlrs (cost=0.29..0.63 rows=15 width=14) (actual time=0.008..0.019 rows=4 loops=12151)"
" Index Cond: (rltnp_lead_acct = cog_lead.acct_num)"
" -> Nested Loop (cost=2.45..11.75 rows=1 width=33) (actual time=0.058..0.615 rows=241 loops=52261)"
" -> Index Only Scan using idx3 on dlr_grp dlr_grp_1 (cost=0.29..0.32 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=52261)"
" Index Cond: ((lead_dlr_loc_id = cog_lead.dlr_loc_id) AND (dlr_grp_typ = 'COG'::bpchar))"
" Heap Fetches: 0"
" -> Nested Loop (cost=2.17..11.42 rows=1 width=37) (actual time=0.051..0.530 rows=236 loops=53436)"
" Join Filter: (dlr_loc_2.acct_num = dlr_loc.acct_num)"
" -> Nested Loop (cost=0.58..0.77 rows=1 width=11) (actual time=0.015..0.016 rows=1 loops=53436)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_2 (cost=0.29..0.32 rows=1 width=11) (actual time=0.009..0.009 rows=1 loops=53436)"
" Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref dlr_grp_dlr_xref_1 (cost=0.29..0.43 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=53402)"
" Index Cond: ((dlr_loc_id = dlr_loc_2.dlr_loc_id) AND (dlr_grp_id = dlr_grp_1.dlr_grp_id))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.58..10.64 rows=1 width=26) (actual time=0.036..0.425 rows=243 loops=51988)"
" -> Index Only Scan using idx10 on dlr_loc (cost=0.29..0.32 rows=1 width=7) (actual time=0.009..0.009 rows=1 loops=51988)"
" Index Cond: ((is_actv = 'Y'::bpchar) AND (acct_num = stg_acflx_nmq_dlrs.acct_id))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.29..10.30 rows=1 width=19) (actual time=0.026..0.354 rows=243 loops=51988)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_1 (cost=0.29..0.34 rows=1 width=11) (actual time=0.006..0.006 rows=1 loops=51988)"
" Index Cond: ((acct_num = dlr_loc.acct_num) AND (is_actv = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.00..9.95 rows=1 width=16) (actual time=0.019..0.273 rows=243 loops=51988)"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref (cost=0.29..0.35 rows=2 width=8) (actual time=0.002..0.003 rows=2 loops=51988)"
" Index Cond: (dlr_loc_id = dlr_loc_1.dlr_loc_id)"
" Heap Fetches: 0"
" -> Nested Loop (cost=0.71..4.79 rows=1 width=20) (actual time=0.015..0.105 rows=121 loops=103987)"
" -> Index Scan using idxdg3 on dlr_grp (cost=0.29..0.33 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=103987)"
" Index Cond: (dlr_grp_id = dlr_grp_dlr_xref.dlr_grp_id)"
" Filter: ((dlr_grp_typ = 'LOC'::bpchar) OR (dlr_grp_typ = 'COG'::bpchar))"
" -> Index Only Scan using idxdq7 on dlr_qlfy (cost=0.43..4.45 rows=1 width=16) (actual time=0.009..0.066 rows=121 loops=103987)"
" Index Cond: ((qlfy_grp_id = dlr_grp.dlr_grp_id) AND (qlf_flg = 'N'::bpchar) AND (cog_grp_id = dlr_grp_dlr_xref_1.dlr_grp_id))"
" Heap Fetches: 0"
" -> Index Scan using p_key_29 on nmq_req (cost=0.28..7.83 rows=1 width=16) (actual time=0.219..0.220 rows=1 loops=12612983)"
" Index Cond: (nmq_req_id = dlr_qlfy.nmq_req_id)"
" Filter: ((pgm_dsbl_flg <> 'Y'::bpchar) AND (pgm_start_dt <= ('now'::cstring)::date) AND (pgm_end_dt > ('now'::cstring)::date) AND (NOT (SubPlan 1)))"
" Rows Removed by Filter: 0"
" SubPlan 1"
" -> Index Only Scan using idx11 on dlr_qlfy dlr_qlfy_1 (cost=0.43..13.91 rows=274 width=4) (actual time=0.008..0.153 rows=576 loops=12321289)"
" Index Cond: ((nmq_req_id = nmq_req.nmq_req_id) AND (qlf_flg = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Index Scan using idx1 on new_mdl (cost=0.28..0.37 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=12321289)"
" Index Cond: (new_mdl_id = nmq_req.new_mdl_id)"
"Planning time: 69.774 ms"
"Execution time: 2892445.829 ms"
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: query performance issue
@ 2017-11-15 13:12 Pavel Stehule <pavel.stehule@gmail.com>
parent: Samir Magar <samirmagar8@gmail.com>
0 siblings, 1 reply; 57+ messages in thread
From: Pavel Stehule @ 2017-11-15 13:12 UTC (permalink / raw)
To: Samir Magar <samirmagar8@gmail.com>; +Cc: pgsql-performance
2017-11-15 13:54 GMT+01:00 Samir Magar <samirmagar8@gmail.com>:
> please find the EXPLAIN ANALYZE output.
>
> On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>
>> Hi
>>
>> please send EXPLAIN ANALYZE output.
>>
>> Regards
>>
>> Pavel
>>
>> 2017-11-15 10:33 GMT+01:00 Samir Magar <samirmagar8@gmail.com>:
>>
>>> Hello,
>>> I am having performance issues with one of the query.
>>> The query is taking 39 min to fetch 3.5 mil records.
>>>
>>> I want to reduce that time to 15 mins.
>>> could you please suggest something to its performance?
>>>
>>> server configuration:
>>> CPUs = 4
>>> memory = 16 GM
>>> shared_buffers = 3 GB
>>> work_mem = 100MB
>>> effective_cache_size = 12 GB
>>>
>>> we are doing the vacuum/analyze regularly on the database.
>>>
>>> attached is the query with its explain plan.
>>>
>>>
There is wrong plan due wrong estimation
for this query you should to penalize nested loop
set enable_nestloop to off;
before evaluation of this query
Thanks,
>>> Samir Magar
>>>
>>>
>>> --
>>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.
>>> org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>
>>>
>>
>
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: query performance issue
@ 2017-11-15 14:16 Justin Pryzby <pryzby@telsasoft.com>
parent: Samir Magar <samirmagar8@gmail.com>
1 sibling, 0 replies; 57+ messages in thread
From: Justin Pryzby @ 2017-11-15 14:16 UTC (permalink / raw)
To: Samir Magar <samirmagar8@gmail.com>; +Cc: pgsql-performance@postgresql.org, Pavel Stehule <pavel.stehule@gmail.com>
On Wed, Nov 15, 2017 at 03:03:39PM +0530, Samir Magar wrote:
> I am having performance issues with one of the query.
> The query is taking 39 min to fetch 3.5 mil records.
>
> I want to reduce that time to 15 mins.
> could you please suggest something to its performance?
> "HashAggregate (cost=4459.68..4459.69 rows=1 width=27) (actual time=2890035.403..2892173.601 rows=3489861 loops=1)"
Looks to me like the problem is here:
> " -> Index Only Scan using idxdq7 on dlr_qlfy (cost=0.43..4.45 ROWS=1 width=16) (actual time=0.009..0.066 ROWS=121 loops=103987)"
> " Index Cond: ((qlfy_grp_id = dlr_grp.dlr_grp_id) AND (qlf_flg = 'N'::bpchar) AND (cog_grp_id = dlr_grp_dlr_xref_1.dlr_grp_id))"
> " Heap Fetches: 0"
Returning 100x more rows than expected and bubbling up through a cascade of
nested loops.
Are those 3 conditions independent ? Or, perhaps, are rows for which
"qlfy_grp_id=dlr_grp.dlr_grp_id" is true always going to have
"cog_grp_id = dlr_grp_dlr_xref_1.dlr_grp_id" ?
Even if it's not "always" true, if rows which pass the one condition are more
likely to pass the other condition, this will cause an underestimate, as
obvserved.
You can do an experiment SELECTing just from those two tables joined and see if
you can reproduce the problem with poor rowcount estimate (hopefully in much
less than 15min).
If you can't drop one of the two conditions, you can make PG treat it as a
single condition for purpose of determining expected selectivity, using a ROW()
comparison like:
ROW(qlfy_grp_id, cog_grp_id) = ROW(dlr_grp.dlr_grp_id, dlr_grp_dlr_xref_1.dlr_grp_id)
If you're running PG96+ you may also be able to work around this by adding FKs.
Justin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: query performance issue
@ 2017-11-15 19:58 Gunther <raj@gusw.net>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 57+ messages in thread
From: Gunther @ 2017-11-15 19:58 UTC (permalink / raw)
To: pgsql-performance
On 11/15/2017 8:12, Pavel Stehule wrote:
> There is wrong plan due wrong estimation
>
> for this query you should to penalize nested loop
>
> set enable_nestloop to off;
>
> before evaluation of this query
You are not the only one with this issue. May I suggest to look at this
thread a little earlier this month.
http://www.postgresql-archive.org/OLAP-reporting-queries-fall-into-nested-loops-over-seq-scans-or-ot...
where this has been discussed in some length.
regards,
-Gunther
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: query performance issue
@ 2017-11-15 20:07 Pavel Stehule <pavel.stehule@gmail.com>
parent: Gunther <raj@gusw.net>
0 siblings, 0 replies; 57+ messages in thread
From: Pavel Stehule @ 2017-11-15 20:07 UTC (permalink / raw)
To: Gunther <raj@gusw.net>; +Cc: pgsql-performance
2017-11-15 20:58 GMT+01:00 Gunther <raj@gusw.net>:
>
> On 11/15/2017 8:12, Pavel Stehule wrote:
>
> There is wrong plan due wrong estimation
>
> for this query you should to penalize nested loop
>
> set enable_nestloop to off;
>
> before evaluation of this query
>
>
> You are not the only one with this issue. May I suggest to look at this
> thread a little earlier this month.
>
> http://www.postgresql-archive.org/OLAP-reporting-queries-
> fall-into-nested-loops-over-seq-scans-or-other-horrible-
> planner-choices-tp5990160.html
>
> where this has been discussed in some length.
>
It is typical issue. The source of these problems are correlations between
columns (it can be fixed partially by multicolumn statistics in PostgreSQL
10). Another problem is missing multi table statistics - PostgreSQL planner
expects so any value from dictionary has same probability, what is not
usually true. Some OLAP techniques like calendar tables has usually very
bad impact on estimations with this results.
Regards
Pavel
> regards,
> -Gunther
>
>
>
^ permalink raw reply [nested|flat] 57+ messages in thread
* Query Performance Issue
@ 2018-12-27 19:25 neslişah demirci <neslisah.demirci@gmail.com>
0 siblings, 3 replies; 57+ messages in thread
From: neslişah demirci @ 2018-12-27 19:25 UTC (permalink / raw)
To: pgsql-performance
Hi everyone ,
Have this explain analyze output :
*https://explain.depesz.com/s/Pra8a <https://explain.depesz.com/s/Pra8a>*
Appreciated for any help .
*PG version*
-----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-28), 64-bit
*OS version :*
CentOS Linux release 7.5.1804 (Core)
shared_buffers : 4GB
work_mem : 8MB
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query Performance Issue
@ 2018-12-28 14:53 Alexey Bashtanov <bashtanov@imap.cc>
parent: neslişah demirci <neslisah.demirci@gmail.com>
2 siblings, 0 replies; 57+ messages in thread
From: Alexey Bashtanov @ 2018-12-28 14:53 UTC (permalink / raw)
To: neslişah demirci <neslisah.demirci@gmail.com>; pgsql-performance
> *https://explain.depesz.com/s/Pra8a*
Could you share the query itself please?
And the tables definitions including indexes.
> work_mem : 8MB
That's not a lot. The 16-batches hash join may have worked faster if you
had resources to increase work_mem.
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query Performance Issue
@ 2018-12-28 15:32 Justin Pryzby <pryzby@telsasoft.com>
parent: neslişah demirci <neslisah.demirci@gmail.com>
2 siblings, 1 reply; 57+ messages in thread
From: Justin Pryzby @ 2018-12-28 15:32 UTC (permalink / raw)
To: neslişah demirci <neslisah.demirci@gmail.com>; +Cc: pgsql-performance
On Thu, Dec 27, 2018 at 10:25:47PM +0300, neslişah demirci wrote:
> Have this explain analyze output :
>
> *https://explain.depesz.com/s/Pra8a <https://explain.depesz.com/s/Pra8a>*
Row counts are being badly underestimated leading to nested loop joins:
|Index Scan using product_content_recommendation_main2_recommended_content_id_idx on product_content_recommendation_main2 prm (cost=0.57..2,031.03 ROWS=345 width=8) (actual time=0.098..68.314 ROWS=3,347 loops=1)
|Index Cond: (recommended_content_id = 3371132)
|Filter: (version = 1)
Apparently, recommended_content_id and version aren't independent condition,
but postgres thinks they are.
Would you send statistics about those tables ? MCVs, ndistinct, etc.
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram
I think the solution is to upgrade (at least) to PG10 and CREATE STATISTICS
(dependencies).
https://www.postgresql.org/docs/10/catalog-pg-statistic-ext.html
https://www.postgresql.org/docs/10/sql-createstatistics.html
https://www.postgresql.org/docs/10/planner-stats.html#PLANNER-STATS-EXTENDED
https://www.postgresql.org/docs/10/multivariate-statistics-examples.html
Justin
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query Performance Issue
@ 2018-12-29 06:58 David Rowley <david.rowley@2ndquadrant.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 57+ messages in thread
From: David Rowley @ 2018-12-29 06:58 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: neslişah demirci <neslisah.demirci@gmail.com>; pgsql-performance
On Sat, 29 Dec 2018 at 04:32, Justin Pryzby <pryzby@telsasoft.com> wrote:
> I think the solution is to upgrade (at least) to PG10 and CREATE STATISTICS
> (dependencies).
Unfortunately, I don't think that'll help this situation. Extended
statistics are currently only handled for base quals, not join quals.
See dependency_is_compatible_clause().
It would be interesting to see how far out the estimate is without the
version = 1 clause. If just the recommended_content_id clause is
underestimated enough it could be enough to have the planner choose
the nested loop. Perhaps upping the stats on that column may help, but
it may only help so far as to reduce the chances of a nested loop. If
the number of distinct recommended_content_id values is higher than
the statistic targets and is skewed enough then there still may be
some magic values in there that end up causing a bad plan.
It would also be good to know what random_page_cost is set to, and
also if effective_cache_size isn't set too high. Increasing
random_page_cost would help reduce the chances of this nested loop
plan, but it's a pretty global change and could also have a negative
effect on other queries.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query Performance Issue
@ 2018-12-29 07:15 Justin Pryzby <pryzby@telsasoft.com>
parent: neslişah demirci <neslisah.demirci@gmail.com>
2 siblings, 2 replies; 57+ messages in thread
From: Justin Pryzby @ 2018-12-29 07:15 UTC (permalink / raw)
To: neslişah demirci <neslisah.demirci@gmail.com>; David Rowley <david.rowley@2ndquadrant.com>; +Cc: pgsql-performance
On Thu, Dec 27, 2018 at 10:25:47PM +0300, neslişah demirci wrote:
> Have this explain analyze output :
>
> *https://explain.depesz.com/s/Pra8a <https://explain.depesz.com/s/Pra8a>*
On Sat, Dec 29, 2018 at 07:58:28PM +1300, David Rowley wrote:
> On Sat, 29 Dec 2018 at 04:32, Justin Pryzby <pryzby@telsasoft.com> wrote:
> > I think the solution is to upgrade (at least) to PG10 and CREATE STATISTICS
> > (dependencies).
>
> Unfortunately, I don't think that'll help this situation. Extended
> statistics are currently only handled for base quals, not join quals.
> See dependency_is_compatible_clause().
Right, understand.
Corrrect me if I'm wrong though, but I think the first major misestimate is in
the scan, not the join:
|Index Scan using product_content_recommendation_main2_recommended_content_id_idx on product_content_recommendation_main2 prm (cost=0.57..2,031.03 rows=345 width=8) (actual time=0.098..68.314 rows=3,347 loops=1)
|Index Cond: (recommended_content_id = 3371132)
|Filter: (version = 1)
|Rows Removed by Filter: 2708
Justin
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query Performance Issue
@ 2018-12-29 12:00 Jim Finnerty <jfinnert@amazon.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 57+ messages in thread
From: Jim Finnerty @ 2018-12-29 12:00 UTC (permalink / raw)
To: pgsql-performance
Try a pg_hint_plan Rows hint to explore what would happen to the plan if you
fixed the bad join cardinality estimate:
/*+ rows(prm prc #2028) */
alternatively you could specify a HashJoin hint, but I think it's better to
fix the cardinality estimate and then let the optimizer decide what the best
plan is.
I agree with Justin that it looks like the version and
recommended_content_id columns are correlated and that's the likely root
cause of the problem, but you don't need to upgrade to fix this one query.
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query Performance Issue
@ 2018-12-29 20:27 Jeff Janes <jeff.janes@gmail.com>
parent: David Rowley <david.rowley@2ndquadrant.com>
0 siblings, 0 replies; 57+ messages in thread
From: Jeff Janes @ 2018-12-29 20:27 UTC (permalink / raw)
To: David Rowley <david.rowley@2ndquadrant.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; neslişah demirci <neslisah.demirci@gmail.com>; pgsql-performance
On Sat, Dec 29, 2018 at 1:58 AM David Rowley <david.rowley@2ndquadrant.com>
wrote:
> On Sat, 29 Dec 2018 at 04:32, Justin Pryzby <pryzby@telsasoft.com> wrote:
> > I think the solution is to upgrade (at least) to PG10 and CREATE
> STATISTICS
> > (dependencies).
>
> Unfortunately, I don't think that'll help this situation. Extended
> statistics are currently only handled for base quals, not join quals.
> See dependency_is_compatible_clause().
>
>
But "recommended_content_id" and "version" are both in the same table,
doesn't that make them base quals?
The most obvious thing to me would be to vacuum
product_content_recommendation_main2 to get rid of the massive number of
heap fetches. And to analyze everything to make sure the estimation errors
are not simply due to out-of-date stats. And to increase work_mem.
It isn't clear we want to get rid of the nested loop, from the info we have
to go on the hash join might be even slower yet. Seeing the plan with
enable_nestloop=off could help there.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query Performance Issue
@ 2018-12-29 22:00 David Rowley <david.rowley@2ndquadrant.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 57+ messages in thread
From: David Rowley @ 2018-12-29 22:00 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: neslişah demirci <neslisah.demirci@gmail.com>; pgsql-performance
On Sat, 29 Dec 2018 at 20:15, Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Sat, Dec 29, 2018 at 07:58:28PM +1300, David Rowley wrote:
> > Unfortunately, I don't think that'll help this situation. Extended
> > statistics are currently only handled for base quals, not join quals.
> > See dependency_is_compatible_clause().
>
> Right, understand.
>
> Corrrect me if I'm wrong though, but I think the first major misestimate is in
> the scan, not the join:
I should have checked more carefully. Of course, they are base quals.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 57+ messages in thread
* Query performance issue
@ 2020-09-04 21:18 Nagaraj Raj <nagaraj.sf@yahoo.com>
0 siblings, 3 replies; 57+ messages in thread
From: Nagaraj Raj @ 2020-09-04 21:18 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by adding indexes in different ways but nothing helps.
Any suggestions?
Query:
EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime from (select serial_no,receivingplant,sku,eventtime as r3_eventtime, row_number() over (partition by serial_no order by eventtime desc) as mpos from receiving_item_delivered_received where eventtype='LineItemdetailsReceived'and replenishmenttype = 'DC2SWARRANTY'and coalesce(serial_no,'') <> '') Rec where mpos = 1;
Query Planner:
"Subquery Scan on rec (cost=70835.30..82275.49 rows=1760 width=39) (actual time=2322.999..3451.783 rows=333451 loops=1)"" Filter: (rec.mpos = 1)"" Rows Removed by Filter: 19900"" -> WindowAgg (cost=70835.30..77875.42 rows=352006 width=47) (actual time=2322.997..3414.384 rows=353351 loops=1)"" -> Sort (cost=70835.30..71715.31 rows=352006 width=39) (actual time=2322.983..3190.090 rows=353351 loops=1)"" Sort Key: receiving_item_delivered_received.serial_no, receiving_item_delivered_received.eventtime DESC"" Sort Method: external merge Disk: 17424kB"" -> Seq Scan on receiving_item_delivered_received (cost=0.00..28777.82 rows=352006 width=39) (actual time=0.011..184.677 rows=353351 loops=1)"" Filter: (((COALESCE(serial_no, ''::character varying))::text <> ''::text) AND ((eventtype)::text = 'LineItemdetailsReceived'::text) AND ((replenishmenttype)::text = 'DC2SWARRANTY'::text))"" Rows Removed by Filter: 55953""Planning Time: 0.197 ms""Execution Time: 3466.985 ms"
Table DDL:
CREATE TABLE receiving_item_delivered_received( load_dttm timestamp with time zone, iamuniqueid character varying(200) , batchid character varying(200) , eventid character varying(200) , eventtype character varying(200) , eventversion character varying(200) , eventtime timestamp with time zone, eventproducerid character varying(200) , deliverynumber character varying(200) , activityid character varying(200) , applicationid character varying(200) , channelid character varying(200) , interactionid character varying(200) , sessionid character varying(200) , receivingplant character varying(200) , deliverydate date, shipmentdate date, shippingpoint character varying(200) , replenishmenttype character varying(200) , numberofpackages character varying(200) , carrier_id character varying(200) , carrier_name character varying(200) , billoflading character varying(200) , pro_no character varying(200) , partner_id character varying(200) , deliveryitem character varying(200) , ponumber character varying(200) , poitem character varying(200) , tracking_no character varying(200) , serial_no character varying(200) , sto_no character varying(200) , sim_no character varying(200) , sku character varying(200) , quantity numeric(15,2), uom character varying(200) );
-- Index: receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx
-- DROP INDEX receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx;
CREATE INDEX receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx ON receiving_item_delivered_received USING btree (eventtype , replenishmenttype , COALESCE(serial_no, ''::character varying) ) ;-- Index: receiving_item_delivered_rece_serial_no_eventtype_replenish_idx
-- DROP INDEX receiving_item_delivered_rece_serial_no_eventtype_replenish_idx;
CREATE INDEX receiving_item_delivered_rece_serial_no_eventtype_replenish_idx ON receiving_item_delivered_received USING btree (serial_no , eventtype , replenishmenttype ) WHERE eventtype::text = 'LineItemdetailsReceived'::text AND replenishmenttype::text = 'DC2SWARRANTY'::text AND COALESCE(serial_no, ''::character varying)::text <> ''::text;-- Index: receiving_item_delivered_recei_eventtype_replenishmenttype_idx1
-- DROP INDEX receiving_item_delivered_recei_eventtype_replenishmenttype_idx1;
CREATE INDEX receiving_item_delivered_recei_eventtype_replenishmenttype_idx1 ON receiving_item_delivered_received USING btree (eventtype , replenishmenttype ) WHERE eventtype::text = 'LineItemdetailsReceived'::text AND replenishmenttype::text = 'DC2SWARRANTY'::text;-- Index: receiving_item_delivered_receiv_eventtype_replenishmenttype_idx
-- DROP INDEX receiving_item_delivered_receiv_eventtype_replenishmenttype_idx;
CREATE INDEX receiving_item_delivered_receiv_eventtype_replenishmenttype_idx ON receiving_item_delivered_received USING btree (eventtype , replenishmenttype ) ;-- Index: receiving_item_delivered_received_eventtype_idx
-- DROP INDEX receiving_item_delivered_received_eventtype_idx;
CREATE INDEX receiving_item_delivered_received_eventtype_idx ON receiving_item_delivered_received USING btree (eventtype ) ;-- Index: receiving_item_delivered_received_replenishmenttype_idx
-- DROP INDEX receiving_item_delivered_received_replenishmenttype_idx;
CREATE INDEX receiving_item_delivered_received_replenishmenttype_idx ON receiving_item_delivered_received USING btree (replenishmenttype ) ;
Thanks,Rj
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2020-09-04 21:23 Thomas Kellerer <shammat@gmx.net>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
2 siblings, 0 replies; 57+ messages in thread
From: Thomas Kellerer @ 2020-09-04 21:23 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Nagaraj Raj schrieb am 04.09.2020 um 23:18:
> I have a query which will more often run on DB and very slow and it
> is doing 'seqscan'. I was trying to optimize it by adding indexes in
> different ways but nothing helps.
>
> EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime
> from (select serial_no,receivingplant,sku,eventtime as r3_eventtime, row_number() over (partition by serial_no order by eventtime desc) as mpos
> from receiving_item_delivered_received
> where eventtype='LineItemdetailsReceived'
> and replenishmenttype = 'DC2SWARRANTY'
> and coalesce(serial_no,'') <> ''
> ) Rec where mpos = 1;
>
>
> Query Planner:
>
> "Subquery Scan on rec (cost=70835.30..82275.49 rows=1760 width=39) (actual time=2322.999..3451.783 rows=333451 loops=1)"
> " Filter: (rec.mpos = 1)"
> " Rows Removed by Filter: 19900"
> " -> WindowAgg (cost=70835.30..77875.42 rows=352006 width=47) (actual time=2322.997..3414.384 rows=353351 loops=1)"
> " -> Sort (cost=70835.30..71715.31 rows=352006 width=39) (actual time=2322.983..3190.090 rows=353351 loops=1)"
> " Sort Key: receiving_item_delivered_received.serial_no, receiving_item_delivered_received.eventtime DESC"
> " Sort Method: external merge Disk: 17424kB"
> " -> Seq Scan on receiving_item_delivered_received (cost=0.00..28777.82 rows=352006 width=39) (actual time=0.011..184.677 rows=353351 loops=1)"
> " Filter: (((COALESCE(serial_no, ''::character varying))::text <> ''::text) AND ((eventtype)::text = 'LineItemdetailsReceived'::text) AND ((replenishmenttype)::text = 'DC2SWARRANTY'::text))"
> " Rows Removed by Filter: 55953"
> "Planning Time: 0.197 ms"
> "Execution Time: 3466.985 ms"
The query retrieves nearly all rows from the table 353351 of 409304 and the Seq Scan takes less than 200ms, so that's not your bottleneck.
Adding indexes won't change that.
The majority of the time is spent in the sort step which is done on disk.
Try to increase work_mem until the "external merge" disappears and is done in memory.
Thomas
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2020-09-04 21:24 Nagaraj Raj <nagaraj.sf@yahoo.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
2 siblings, 1 reply; 57+ messages in thread
From: Nagaraj Raj @ 2020-09-04 21:24 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
query planner:SPJe | explain.depesz.com
|
|
| |
SPJe | explain.depesz.com
|
|
|
On Friday, September 4, 2020, 02:19:06 PM PDT, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by adding indexes in different ways but nothing helps.
Any suggestions?
Query:
EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime from (select serial_no,receivingplant,sku,eventtime as r3_eventtime, row_number() over (partition by serial_no order by eventtime desc) as mpos from receiving_item_delivered_received where eventtype='LineItemdetailsReceived'and replenishmenttype = 'DC2SWARRANTY'and coalesce(serial_no,'') <> '') Rec where mpos = 1;
Query Planner:
"Subquery Scan on rec (cost=70835.30..82275.49 rows=1760 width=39) (actual time=2322.999..3451.783 rows=333451 loops=1)"" Filter: (rec.mpos = 1)"" Rows Removed by Filter: 19900"" -> WindowAgg (cost=70835.30..77875.42 rows=352006 width=47) (actual time=2322.997..3414.384 rows=353351 loops=1)"" -> Sort (cost=70835.30..71715.31 rows=352006 width=39) (actual time=2322.983..3190.090 rows=353351 loops=1)"" Sort Key: receiving_item_delivered_received.serial_no, receiving_item_delivered_received.eventtime DESC"" Sort Method: external merge Disk: 17424kB"" -> Seq Scan on receiving_item_delivered_received (cost=0.00..28777.82 rows=352006 width=39) (actual time=0.011..184.677 rows=353351 loops=1)"" Filter: (((COALESCE(serial_no, ''::character varying))::text <> ''::text) AND ((eventtype)::text = 'LineItemdetailsReceived'::text) AND ((replenishmenttype)::text = 'DC2SWARRANTY'::text))"" Rows Removed by Filter: 55953""Planning Time: 0.197 ms""Execution Time: 3466.985 ms"
Table DDL:
CREATE TABLE receiving_item_delivered_received( load_dttm timestamp with time zone, iamuniqueid character varying(200) , batchid character varying(200) , eventid character varying(200) , eventtype character varying(200) , eventversion character varying(200) , eventtime timestamp with time zone, eventproducerid character varying(200) , deliverynumber character varying(200) , activityid character varying(200) , applicationid character varying(200) , channelid character varying(200) , interactionid character varying(200) , sessionid character varying(200) , receivingplant character varying(200) , deliverydate date, shipmentdate date, shippingpoint character varying(200) , replenishmenttype character varying(200) , numberofpackages character varying(200) , carrier_id character varying(200) , carrier_name character varying(200) , billoflading character varying(200) , pro_no character varying(200) , partner_id character varying(200) , deliveryitem character varying(200) , ponumber character varying(200) , poitem character varying(200) , tracking_no character varying(200) , serial_no character varying(200) , sto_no character varying(200) , sim_no character varying(200) , sku character varying(200) , quantity numeric(15,2), uom character varying(200) );
-- Index: receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx
-- DROP INDEX receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx;
CREATE INDEX receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx ON receiving_item_delivered_received USING btree (eventtype , replenishmenttype , COALESCE(serial_no, ''::character varying) ) ;-- Index: receiving_item_delivered_rece_serial_no_eventtype_replenish_idx
-- DROP INDEX receiving_item_delivered_rece_serial_no_eventtype_replenish_idx;
CREATE INDEX receiving_item_delivered_rece_serial_no_eventtype_replenish_idx ON receiving_item_delivered_received USING btree (serial_no , eventtype , replenishmenttype ) WHERE eventtype::text = 'LineItemdetailsReceived'::text AND replenishmenttype::text = 'DC2SWARRANTY'::text AND COALESCE(serial_no, ''::character varying)::text <> ''::text;-- Index: receiving_item_delivered_recei_eventtype_replenishmenttype_idx1
-- DROP INDEX receiving_item_delivered_recei_eventtype_replenishmenttype_idx1;
CREATE INDEX receiving_item_delivered_recei_eventtype_replenishmenttype_idx1 ON receiving_item_delivered_received USING btree (eventtype , replenishmenttype ) WHERE eventtype::text = 'LineItemdetailsReceived'::text AND replenishmenttype::text = 'DC2SWARRANTY'::text;-- Index: receiving_item_delivered_receiv_eventtype_replenishmenttype_idx
-- DROP INDEX receiving_item_delivered_receiv_eventtype_replenishmenttype_idx;
CREATE INDEX receiving_item_delivered_receiv_eventtype_replenishmenttype_idx ON receiving_item_delivered_received USING btree (eventtype , replenishmenttype ) ;-- Index: receiving_item_delivered_received_eventtype_idx
-- DROP INDEX receiving_item_delivered_received_eventtype_idx;
CREATE INDEX receiving_item_delivered_received_eventtype_idx ON receiving_item_delivered_received USING btree (eventtype ) ;-- Index: receiving_item_delivered_received_replenishmenttype_idx
-- DROP INDEX receiving_item_delivered_received_replenishmenttype_idx;
CREATE INDEX receiving_item_delivered_received_replenishmenttype_idx ON receiving_item_delivered_received USING btree (replenishmenttype ) ;
Thanks,Rj
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2020-09-04 21:36 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
2 siblings, 0 replies; 57+ messages in thread
From: Tomas Vondra @ 2020-09-04 21:36 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Fri, Sep 04, 2020 at 09:18:41PM +0000, Nagaraj Raj wrote:
> I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by adding indexes in different ways but nothing helps.
>Any suggestions?
>
1) It's rather difficult to read the query plan as it's mangled by your
e-mail client. I recommend to check how to prevent the client from doing
that, or attaching the plan as a file.
2) The whole query takes ~3500ms, and the seqscan only accounts for
~200ms, so it's very clearly not the main issue.
3) Most of the time is spent in sort, so the one thing you can do is
either increasing work_mem, or adding index providing that ordering.
Even better if you include all necessary columns to allow IOS.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2020-09-04 21:39 Michael Lewis <mlewis@entrata.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
0 siblings, 2 replies; 57+ messages in thread
From: Michael Lewis @ 2020-09-04 21:39 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
CREATE INDEX receiving_item_delivered_received
ON receiving_item_delivered_received USING btree ( eventtype,
replenishmenttype, serial_no, eventtime DESC );
>
More work_mem as Tomas suggests, but also, the above index should find the
candidate rows by the first two keys, and then be able to skip the sort by
reading just that portion of the index that matches
eventtype='LineItemdetailsReceived'
and replenishmenttype = 'DC2SWARRANTY'
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2020-09-04 21:41 Michael Lewis <mlewis@entrata.com>
parent: Michael Lewis <mlewis@entrata.com>
1 sibling, 0 replies; 57+ messages in thread
From: Michael Lewis @ 2020-09-04 21:41 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Note- you may need to vacuum* the table to get full benefit of index only
scan by updating the visibility map. I think index only scan is skipped in
favor of just checking visibility when the visibility map is stale.
*NOT full
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2020-09-04 21:44 Nagaraj Raj <nagaraj.sf@yahoo.com>
parent: Michael Lewis <mlewis@entrata.com>
1 sibling, 1 reply; 57+ messages in thread
From: Nagaraj Raj @ 2020-09-04 21:44 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; Thomas Kellerer <shammat@gmx.net>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Sorry, I have attached the wrong query planner, which executed in lower environment which has fewer resources:
Updated one,eVFiF | explain.depesz.com
|
|
| |
eVFiF | explain.depesz.com
|
|
|
Thanks,Rj On Friday, September 4, 2020, 02:39:57 PM PDT, Michael Lewis <mlewis@entrata.com> wrote:
CREATE INDEX receiving_item_delivered_received ON receiving_item_delivered_received USING btree ( eventtype, replenishmenttype, serial_no, eventtime DESC );
More work_mem as Tomas suggests, but also, the above index should find the candidate rows by the first two keys, and then be able to skip the sort by reading just that portion of the index that matches
eventtype='LineItemdetailsReceived'and replenishmenttype = 'DC2SWARRANTY'
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2020-09-04 21:55 Michael Lewis <mlewis@entrata.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
0 siblings, 1 reply; 57+ messages in thread
From: Michael Lewis @ 2020-09-04 21:55 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Thomas Kellerer <shammat@gmx.net>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
"Subquery Scan on rec (cost=1628601.89..1676580.92 rows=7381 width=41)
(actual time=22171.986..23549.079 rows=1236042 loops=1)" " Filter:
(rec.mpos = 1)" " Rows Removed by Filter: 228737" " Buffers: shared hit=45
read=1166951" " I/O Timings: read=29.530" " -> WindowAgg
(cost=1628601.89..1658127.45 rows=1476278 width=49) (actual
time=22171.983..23379.219 rows=1464779 loops=1)" " Buffers: shared hit=45
read=1166951" " I/O Timings: read=29.530" " -> Sort
(cost=1628601.89..1632292.58 rows=1476278 width=41) (actual
time=22171.963..22484.044 rows=1464779 loops=1)" " Sort Key:
receiving_item_delivered_received.serial_no,
receiving_item_delivered_received.eventtime DESC" " Sort Method: quicksort
Memory: 163589kB" " Buffers: shared hit=45 read=1166951" " I/O Timings:
read=29.530" " -> Gather (cost=1000.00..1477331.13 rows=1476278 width=41)
(actual time=1.296..10428.060 rows=1464779 loops=1)" " Workers Planned: 2" "
Workers Launched: 2" " Buffers: shared hit=39 read=1166951" " I/O Timings:
read=29.530" " -> Parallel Seq Scan on receiving_item_delivered_received
(cost=0.00..1328703.33 rows=615116 width=41) (actual time=1.262..10150.325
rows=488260 loops=3)" " Filter: (((COALESCE(serial_no, ''::character
varying))::text <> ''::text) AND ((eventtype)::text =
'LineItemdetailsReceived'::text) AND ((replenishmenttype)::text =
'DC2SWARRANTY'::text))" " Rows Removed by Filter: 6906258" " Buffers:
shared hit=39 read=1166951" " I/O Timings: read=29.530" "Planning Time:
0.375 ms" "Execution Time: 23617.348 ms"
That is doing a lot of reading from disk. What do you have shared_buffers
set to? I'd expect better cache hits unless it is quite low or this is a
query that differs greatly from the typical work.
Also, did you try adding the index I suggested? That lowest node has 488k
rows coming out of it after throwing away 6.9 million. I would expect an
index on only eventtype, replenishmenttype to be quite helpful. I don't
assume you have tons of rows where serial_no is null.
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2020-09-04 22:20 Nagaraj Raj <nagaraj.sf@yahoo.com>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 2 replies; 57+ messages in thread
From: Nagaraj Raj @ 2020-09-04 22:20 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Thomas Kellerer <shammat@gmx.net>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi Mechel,
I added the index as you suggested and the planner going through the bitmap index scan,heap and the new planner is,HaOx | explain.depesz.com
|
|
| |
HaOx | explain.depesz.com
|
|
|
Mem config:
Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
vCPU = 64RAM = 512show shared_buffers = 355 GBshow work_mem = 214 MB
show maintenance_work_mem = 8363MBshow effective_cache_size = 355 GB
Thanks,Rj
On Friday, September 4, 2020, 02:55:50 PM PDT, Michael Lewis <mlewis@entrata.com> wrote:
"Subquery Scan on rec (cost=1628601.89..1676580.92 rows=7381 width=41) (actual time=22171.986..23549.079 rows=1236042 loops=1)"" Filter: (rec.mpos = 1)"" Rows Removed by Filter: 228737"" Buffers: shared hit=45 read=1166951"" I/O Timings: read=29.530"" -> WindowAgg (cost=1628601.89..1658127.45 rows=1476278 width=49) (actual time=22171.983..23379.219 rows=1464779 loops=1)"" Buffers: shared hit=45 read=1166951"" I/O Timings: read=29.530"" -> Sort (cost=1628601.89..1632292.58 rows=1476278 width=41) (actual time=22171.963..22484.044 rows=1464779 loops=1)"" Sort Key: receiving_item_delivered_received.serial_no, receiving_item_delivered_received.eventtime DESC"" Sort Method: quicksort Memory: 163589kB"" Buffers: shared hit=45 read=1166951"" I/O Timings: read=29.530"" -> Gather (cost=1000.00..1477331.13 rows=1476278 width=41) (actual time=1.296..10428.060 rows=1464779 loops=1)"" Workers Planned: 2"" Workers Launched: 2"" Buffers: shared hit=39 read=1166951"" I/O Timings: read=29.530"" -> Parallel Seq Scan on receiving_item_delivered_received (cost=0.00..1328703.33 rows=615116 width=41) (actual time=1.262..10150.325 rows=488260 loops=3)"" Filter: (((COALESCE(serial_no, ''::character varying))::text <> ''::text) AND ((eventtype)::text = 'LineItemdetailsReceived'::text) AND ((replenishmenttype)::text = 'DC2SWARRANTY'::text))"" Rows Removed by Filter: 6906258"" Buffers: shared hit=39 read=1166951"" I/O Timings: read=29.530""Planning Time: 0.375 ms""Execution Time: 23617.348 ms"
That is doing a lot of reading from disk. What do you have shared_buffers set to? I'd expect better cache hits unless it is quite low or this is a query that differs greatly from the typical work.
Also, did you try adding the index I suggested? That lowest node has 488k rows coming out of it after throwing away 6.9 million. I would expect an index on only eventtype, replenishmenttype to be quite helpful. I don't assume you have tons of rows where serial_no is null.
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2020-09-05 08:16 David Rowley <dgrowleyml@gmail.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 0 replies; 57+ messages in thread
From: David Rowley @ 2020-09-05 08:16 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Michael Lewis <mlewis@entrata.com>; Thomas Kellerer <shammat@gmx.net>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Sat, 5 Sep 2020 at 10:20, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> I added the index as you suggested and the planner going through the bitmap index scan,heap and the new planner is,
> HaOx | explain.depesz.com
In addition to that index, you could consider moving away from
standard SQL and use DISTINCT ON, which is specific to PostgreSQL and
should give you the same result.
EXPLAIN ANALYZE
SELECT DISTINCT ON (serial_no) serial_no,receivingplant,sku,r3_eventtime
FROM receiving_item_delivered_received
WHERE eventtype='LineItemdetailsReceived'
AND replenishmenttype = 'DC2SWARRANTY'
AND coalesce(serial_no,'') <> ''
ORDER BY serial_no,eventtime DESC;
The more duplicate serial_nos you have the better this one should
perform. It appears you don't have too many so I don't think this
will be significantly faster, but it should be a bit quicker.
David
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2020-09-05 13:42 Michael Lewis <mlewis@entrata.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 1 reply; 57+ messages in thread
From: Michael Lewis @ 2020-09-05 13:42 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Thomas Kellerer <shammat@gmx.net>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Fri, Sep 4, 2020, 4:20 PM Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
> Hi Mechel,
>
> I added the index as you suggested and the planner going through the
> bitmap index scan,heap and the new planner is,
> HaOx | explain.depesz.com <https://explain.depesz.com/s/HaOx;
>
> HaOx | explain.depesz.com
>
> <https://explain.depesz.com/s/HaOx;
>
> Mem config:
>
> Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
> 4.9.3, 64-bit
> vCPU = 64
> RAM = 512
> show shared_buffers = 355 GB
> show work_mem = 214 MB
> show maintenance_work_mem = 8363MB
> show effective_cache_size = 355 GB
>
I'm not very familiar with Aurora, but I would certainly try the explain
analyze with timing OFF and verify that the total time is similar. If the
system clock is slow to read, execution plans can be significantly slower
just because of the cost to measure each step.
That sort being so slow is perplexing. Did you do the two column or four
column index I suggested?
Obviously it depends on your use case and how much you want to tune this
specific query, but you could always try a partial index matching the where
condition and just index the other two columns to avoid the sort.
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2020-09-05 21:49 Nagaraj Raj <nagaraj.sf@yahoo.com>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 0 replies; 57+ messages in thread
From: Nagaraj Raj @ 2020-09-05 21:49 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Thomas Kellerer <shammat@gmx.net>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi Michael,
I created an index as suggested, it improved. I was tried with partial index but the planner not using it.
also, there is no difference even with timing OFF. ktbv : Optimization for: plan #HaOx | explain.depesz.com
|
|
| |
ktbv : Optimization for: plan #HaOx | explain.depesz.com
|
|
|
Thanks,Rj
On Saturday, September 5, 2020, 06:42:31 AM PDT, Michael Lewis <mlewis@entrata.com> wrote:
On Fri, Sep 4, 2020, 4:20 PM Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
Hi Mechel,
I added the index as you suggested and the planner going through the bitmap index scan,heap and the new planner is,HaOx | explain.depesz.com
|
|
| |
HaOx | explain.depesz.com
|
|
|
Mem config:
Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
vCPU = 64RAM = 512show shared_buffers = 355 GBshow work_mem = 214 MB
show maintenance_work_mem = 8363MBshow effective_cache_size = 355 GB
I'm not very familiar with Aurora, but I would certainly try the explain analyze with timing OFF and verify that the total time is similar. If the system clock is slow to read, execution plans can be significantly slower just because of the cost to measure each step.
That sort being so slow is perplexing. Did you do the two column or four column index I suggested?
Obviously it depends on your use case and how much you want to tune this specific query, but you could always try a partial index matching the where condition and just index the other two columns to avoid the sort.
^ permalink raw reply [nested|flat] 57+ messages in thread
* Query performance issue
@ 2021-01-22 01:53 Nagaraj Raj <nagaraj.sf@yahoo.com>
0 siblings, 2 replies; 57+ messages in thread
From: Nagaraj Raj @ 2021-01-22 01:53 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi,
I have a query performance issue, it takes a long time, and not even getting explain analyze the output. this query joining on 3 tables which have around a - 176223509
b - 286887780
c - 214219514
explainselect Count(a."individual_entity_proxy_id")from "prospect" ainner join "individual_demographic" bon a."individual_entity_proxy_id" = b."individual_entity_proxy_id"inner join "household_demographic" c on a."household_entity_proxy_id" = c."household_entity_proxy_id"where (((a."last_contacted_anychannel_dttm" is null) or (a."last_contacted_anychannel_dttm" < TIMESTAMP '2020-11-23 0:00:00.000000')) and (a."shared_paddr_with_customer_ind" = 'N') and (a."profane_wrd_ind" = 'N') and (a."tmo_ofnsv_name_ind" = 'N') and (a."has_individual_address" = 'Y') and (a."has_last_name" = 'Y') and (a."has_first_name" = 'Y')) and ((b."tax_bnkrpt_dcsd_ind" = 'N') and (b."govt_prison_ind" = 'N') and (b."cstmr_prspct_ind" = 'Prospect')) and (( c."hspnc_lang_prfrnc_cval" in ('B', 'E', 'X') ) or (c."hspnc_lang_prfrnc_cval" is null));-- Explain output
"Finalize Aggregate (cost=32813309.28..32813309.29 rows=1 width=8)"" -> Gather (cost=32813308.45..32813309.26 rows=8 width=8)"" Workers Planned: 8"" -> Partial Aggregate (cost=32812308.45..32812308.46 rows=1 width=8)"" -> Merge Join (cost=23870130.00..32759932.46 rows=20950395 width=8)"" Merge Cond: (a.individual_entity_proxy_id = b.individual_entity_proxy_id)"" -> Sort (cost=23870127.96..23922503.94 rows=20950395 width=8)"" Sort Key: a.individual_entity_proxy_id"" -> Hash Join (cost=13533600.42..21322510.26 rows=20950395 width=8)"" Hash Cond: (a.household_entity_proxy_id = c.household_entity_proxy_id)"" -> Parallel Seq Scan on prospect a (cost=0.00..6863735.60 rows=22171902 width=16)"" Filter: (((last_contacted_anychannel_dttm IS NULL) OR (last_contacted_anychannel_dttm < '2020-11-23 00:00:00'::timestamp without time zone)) AND (shared_paddr_with_customer_ind = 'N'::bpchar) AND (profane_wrd_ind = 'N'::bpchar) AND (tmo_ofnsv_name_ind = 'N'::bpchar) AND (has_individual_address = 'Y'::bpchar) AND (has_last_name = 'Y'::bpchar) AND (has_first_name = 'Y'::bpchar))"" -> Hash (cost=10801715.18..10801715.18 rows=166514899 width=8)"" -> Seq Scan on household_demographic c (cost=0.00..10801715.18 rows=166514899 width=8)"" Filter: (((hspnc_lang_prfrnc_cval)::text = ANY ('{B,E,X}'::text[])) OR (hspnc_lang_prfrnc_cval IS NULL))"" -> Index Only Scan using indx_individual_demographic_prxyid_taxind_prspctind_prsnind on individual_demographic b (cost=0.57..8019347.13 rows=286887776 width=8)"" Index Cond: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND (cstmr_prspct_ind = 'Prospect'::text) AND (govt_prison_ind = 'N'::bpchar))"
Tables ddl are attached in dbfiddle -- Postgres 11 | db<>fiddle
|
|
| |
Postgres 11 | db<>fiddle
Free online SQL environment for experimenting and sharing.
|
|
|
Server configuration is: Version: 10.11RAM - 320GBvCPU - 32 "maintenance_work_mem" 256MB"work_mem" 1GB"shared_buffers" 64GB
Any suggestions?
Thanks,Rj
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2021-01-22 02:35 Justin Pryzby <pryzby@telsasoft.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 1 reply; 57+ messages in thread
From: Justin Pryzby @ 2021-01-22 02:35 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org
On Fri, Jan 22, 2021 at 01:53:26AM +0000, Nagaraj Raj wrote:
> Tables ddl are attached in dbfiddle -- Postgres 11 | db<>fiddle
> Postgres 11 | db<>fiddle
> Server configuration is: Version: 10.11RAM - 320GBvCPU - 32 "maintenance_work_mem" 256MB"work_mem" 1GB"shared_buffers" 64GB
> Aggregate (cost=31.54..31.55 rows=1 width=8) (actual time=0.010..0.012 rows=1 loops=1)
> -> Nested Loop (cost=0.00..31.54 rows=1 width=8) (actual time=0.007..0.008 rows=0 loops=1)
> Join Filter: (a.household_entity_proxy_id = c.household_entity_proxy_id)
> -> Nested Loop (cost=0.00..21.36 rows=1 width=16) (actual time=0.006..0.007 rows=0 loops=1)
> Join Filter: (a.individual_entity_proxy_id = b.individual_entity_proxy_id)
> -> Seq Scan on prospect a (cost=0.00..10.82 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=1)
> Filter: (((last_contacted_anychannel_dttm IS NULL) OR (last_contacted_anychannel_dttm < '2020-11-23 00:00:00'::timestamp without time zone)) AND (shared_paddr_with_customer_ind = 'N'::bpchar) AND (profane_wrd_ind = 'N'::bpchar) AND (tmo_ofnsv_name_ind = 'N'::bpchar) AND (has_individual_address = 'Y'::bpchar) AND (has_last_name = 'Y'::bpchar) AND (has_first_name = 'Y'::bpchar))
> -> Seq Scan on individual_demographic b (cost=0.00..10.53 rows=1 width=8) (never executed)
> Filter: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND (govt_prison_ind = 'N'::bpchar) AND ((cstmr_prspct_ind)::text = 'Prospect'::text))
> -> Seq Scan on household_demographic c (cost=0.00..10.14 rows=3 width=8) (never executed)
> Filter: (((hspnc_lang_prfrnc_cval)::text = ANY ('{B,E,X}'::text[])) OR (hspnc_lang_prfrnc_cval IS NULL))
> Planning Time: 1.384 ms
> Execution Time: 0.206 ms
> 13 rows
It's doing nested loops with estimated rowcount=1, which indicates a bad
underestimate, and suggests that the conditions are redundant or correlated.
Maybe you can handle this with MV stats on the correlated columns:
CREATE STATISTICS prospect_stats (dependencies) ON
shared_paddr_with_customer_ind, profane_wrd_ind, tmo_ofnsv_name_ind, has_individual_address, has_last_name, has_first_name
FROM prospect;
CREATE STATISTICS individual_demographic_stats (dependencies) ON
tax_bnkrpt_dcsd_ind, govt_prison_ind, cstmr_prspct_ind
FROM individual_demographic_stats
ANALYZE prospect, individual_demographic_stats ;
Since it's expensive to compute stats on large number of columns, I'd then
check *which* are correlated and then only compute MV stats on those. This
will show col1=>col2: X where X approaches 1, the conditions are highly
correlated:
SELECT * FROM pg_statistic_ext; -- pg_statistic_ext_data since v12
Also, as a diagnostic tool to get "explain analyze" to finish, you can
SET enable_nestloop=off;
--
Justin
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2021-02-14 22:03 Tomas Vondra <tomas.vondra@enterprisedb.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 57+ messages in thread
From: Tomas Vondra @ 2021-02-14 22:03 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org
On 1/22/21 3:35 AM, Justin Pryzby wrote:
> On Fri, Jan 22, 2021 at 01:53:26AM +0000, Nagaraj Raj wrote:
>> Tables ddl are attached in dbfiddle -- Postgres 11 | db<>fiddle
>> Postgres 11 | db<>fiddle
>> Server configuration is: Version: 10.11RAM - 320GBvCPU - 32 "maintenance_work_mem" 256MB"work_mem" 1GB"shared_buffers" 64GB
>
>> Aggregate (cost=31.54..31.55 rows=1 width=8) (actual time=0.010..0.012 rows=1 loops=1)
>> -> Nested Loop (cost=0.00..31.54 rows=1 width=8) (actual time=0.007..0.008 rows=0 loops=1)
>> Join Filter: (a.household_entity_proxy_id = c.household_entity_proxy_id)
>> -> Nested Loop (cost=0.00..21.36 rows=1 width=16) (actual time=0.006..0.007 rows=0 loops=1)
>> Join Filter: (a.individual_entity_proxy_id = b.individual_entity_proxy_id)
>> -> Seq Scan on prospect a (cost=0.00..10.82 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=1)
>> Filter: (((last_contacted_anychannel_dttm IS NULL) OR (last_contacted_anychannel_dttm < '2020-11-23 00:00:00'::timestamp without time zone)) AND (shared_paddr_with_customer_ind = 'N'::bpchar) AND (profane_wrd_ind = 'N'::bpchar) AND (tmo_ofnsv_name_ind = 'N'::bpchar) AND (has_individual_address = 'Y'::bpchar) AND (has_last_name = 'Y'::bpchar) AND (has_first_name = 'Y'::bpchar))
>> -> Seq Scan on individual_demographic b (cost=0.00..10.53 rows=1 width=8) (never executed)
>> Filter: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND (govt_prison_ind = 'N'::bpchar) AND ((cstmr_prspct_ind)::text = 'Prospect'::text))
>> -> Seq Scan on household_demographic c (cost=0.00..10.14 rows=3 width=8) (never executed)
>> Filter: (((hspnc_lang_prfrnc_cval)::text = ANY ('{B,E,X}'::text[])) OR (hspnc_lang_prfrnc_cval IS NULL))
>> Planning Time: 1.384 ms
>> Execution Time: 0.206 ms
>> 13 rows
>
> It's doing nested loops with estimated rowcount=1, which indicates a bad
> underestimate, and suggests that the conditions are redundant or correlated.
>
No, it's not. The dbfiddle does that because it's using empty tables,
but the plan shared by Nagaraj does not contain any nested loops.
Nagaraj, if the EXPLAIN ANALYZE does not complete, there are two things
you can do to determine which part of the plan is causing trouble.
Firstly, you can profile the backend using perf or some other profiles,
and if we're lucky the function will give us some hints about which node
type is using the CPU.
Secondly, you can "cut" the query into smaller parts, to run only parts
of the plan - essentially start from inner-most join, and incrementally
add more and more tables until it gets too long.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2021-02-16 16:40 Michael Lewis <mlewis@entrata.com>
parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
1 sibling, 0 replies; 57+ messages in thread
From: Michael Lewis @ 2021-02-16 16:40 UTC (permalink / raw)
To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
What indexes exist on those tables? How many rows do you expect to get back
in total? Is the last_contacted_anychannel_dttm clause restrictive, or does
that include most of the prospect table (check pg_stats for the histogram
if you don't know).
and (a."shared_paddr_with_customer_ind" = 'N')
and (a."profane_wrd_ind" = 'N')
and (a."tmo_ofnsv_name_ind" = 'N')
and (a."has_individual_address" = 'Y')
and (a."has_last_name" = 'Y')
and (a."has_first_name" = 'Y'))
Are these conditions expected to throw out very few rows, or most of the
table?
If you change both joins to EXISTS clauses, do you get the same plan when
you run explain?
^ permalink raw reply [nested|flat] 57+ messages in thread
* Query performance issue
@ 2024-07-10 07:11 Dheeraj Sonawane <Dheeraj.Sonawane@ethoca.com>
0 siblings, 1 reply; 57+ messages in thread
From: Dheeraj Sonawane @ 2024-07-10 07:11 UTC (permalink / raw)
To: pgsql-performance; +Cc: Chandan Sonaye <Chandan.Sonaye@ethoca.com>; Abhishek Patil <Abhishek.Patil@ethoca.com>
Hello all,
While executing the join query on the postgres database we have observed sometimes randomly below query is being fired which is affecting our response time.
Query randomly fired in the background:-
SELECT p.proname,p.oid FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n WHERE p.pronamespace=n.oid AND n.nspname='pg_catalog' AND ( proname = 'lo_open' or proname = 'lo_close' or proname = 'lo_creat' or proname = 'lo_unlink' or proname = 'lo_lseek' or proname = 'lo_lseek64' or proname = 'lo_tell' or proname = 'lo_tell64' or proname = 'loread' or proname = 'lowrite' or proname = 'lo_truncate' or proname = 'lo_truncate64')
Query intended to be executed:-
SELECT a.* FROM tablename1 a INNER JOIN users u ON u.id = a.user_id INNER JOIN tablename2 c ON u.client_id = c.id WHERE u.external_id = ? AND c.name = ? AND (c.namespace = ? OR (c.namespace IS NULL AND ? IS NULL))
Postgres version 11
Below are my questions:-
1. Is the query referring pg_catalog fired by postgres library implicitly?
2. Is there any way we can suppress this query?
Thanks and regards,
Dheeraj Sonawane
Mastercard
| mobile +917588196818
[cid:image001.png@01DAD2C5.3D8927C0]<www.mastercard.com>
CONFIDENTIALITY NOTICE This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately. Thank you.
Attachments:
[image/png] image001.png (3.1K, ../../DS0PR14MB7157B75FB7BFC4006204DC93F4A42@DS0PR14MB7157.namprd14.prod.outlook.com/3-image001.png)
download | view image
^ permalink raw reply [nested|flat] 57+ messages in thread
* Re: Query performance issue
@ 2024-07-10 13:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dheeraj Sonawane <Dheeraj.Sonawane@ethoca.com>
0 siblings, 0 replies; 57+ messages in thread
From: Tom Lane @ 2024-07-10 13:40 UTC (permalink / raw)
To: Dheeraj Sonawane <Dheeraj.Sonawane@ethoca.com>; +Cc: pgsql-performance; Chandan Sonaye <Chandan.Sonaye@ethoca.com>; Abhishek Patil <Abhishek.Patil@ethoca.com>
Dheeraj Sonawane <Dheeraj.Sonawane@ethoca.com> writes:
> While executing the join query on the postgres database we have observed sometimes randomly below query is being fired which is affecting our response time.
> Query randomly fired in the background:-
> SELECT p.proname,p.oid FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n WHERE p.pronamespace=n.oid AND n.nspname='pg_catalog' AND ( proname = 'lo_open' or proname = 'lo_close' or proname = 'lo_creat' or proname = 'lo_unlink' or proname = 'lo_lseek' or proname = 'lo_lseek64' or proname = 'lo_tell' or proname = 'lo_tell64' or proname = 'loread' or proname = 'lowrite' or proname = 'lo_truncate' or proname = 'lo_truncate64')
That looks very similar to libpq's preparatory lookup before executing
large object accesses (cf lo_initialize in fe-lobj.c). The details
aren't identical so it's not from libpq, but I'd guess this is some
other client library's version of the same thing.
> Query intended to be executed:-
> SELECT a.* FROM tablename1 a INNER JOIN users u ON u.id = a.user_id INNER JOIN tablename2 c ON u.client_id = c.id WHERE u.external_id = ? AND c.name = ? AND (c.namespace = ? OR (c.namespace IS NULL AND ? IS NULL))
It is *really* hard to believe that that lookup query would make any
noticeable difference on response time for some other session, unless
you are running the server on seriously underpowered hardware.
It could be that you've misinterpreted your data, and what is actually
happening is that that other session has completed its lookup query
and is now doing fast-path large object reads and writes using the
results. Fast-path requests might not show up as queries in your
monitoring, but if the large object I/O is sufficiently fast and
voluminous maybe that'd account for visible performance impact.
> 2. Is there any way we can suppress this query?
Stop using large objects? But the alternatives won't be better
in terms of performance impact. Really, if this is a problem
for you, you need a beefier server. Or split the work across
more than one server.
regards, tom lane
^ permalink raw reply [nested|flat] 57+ messages in thread
end of thread, other threads:[~2024-07-10 13:40 UTC | newest]
Thread overview: 57+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-24 07:48 Query performance issue Jonathan Gray <jgray@streamy.com>
2007-07-24 08:44 ` Chris <dmagick@gmail.com>
2007-07-24 08:50 ` Chris <dmagick@gmail.com>
2007-07-24 09:18 ` Jonathan Gray <jgray@streamy.com>
2007-07-24 09:36 ` Chris <dmagick@gmail.com>
2007-07-24 09:50 ` Jonathan Gray <jgray@streamy.com>
2011-08-31 09:00 Query performance issue Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2011-08-31 09:34 ` Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
2011-08-31 10:07 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2011-08-31 10:51 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2011-08-31 11:19 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2011-08-31 11:32 ` Venkat Balaji <venkat.balaji@verse.in>
2011-08-31 11:41 ` Tomas Vondra <tv@fuzzy.cz>
2011-08-31 11:57 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2011-08-31 09:37 ` Sushant Sinha <sushant354@gmail.com>
2011-08-31 12:40 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-09-03 04:48 ` Jayadevan <Jayadevan.Maymala@ibsplc.com>
2011-09-04 10:38 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2011-09-04 14:30 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-09-04 15:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-09-04 18:06 ` Jayadevan <Jayadevan.Maymala@ibsplc.com>
2011-09-04 20:18 ` Tomas Vondra <tv@fuzzy.cz>
2011-09-05 04:19 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2011-09-06 03:30 ` Jayadevan <Jayadevan.Maymala@ibsplc.com>
2017-11-15 09:33 query performance issue Samir Magar <samirmagar8@gmail.com>
2017-11-15 09:43 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 12:54 ` Re: query performance issue Samir Magar <samirmagar8@gmail.com>
2017-11-15 13:12 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 19:58 ` Re: query performance issue Gunther <raj@gusw.net>
2017-11-15 20:07 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 14:16 ` Re: query performance issue Justin Pryzby <pryzby@telsasoft.com>
2018-12-27 19:25 Query Performance Issue neslişah demirci <neslisah.demirci@gmail.com>
2018-12-28 14:53 ` Re: Query Performance Issue Alexey Bashtanov <bashtanov@imap.cc>
2018-12-28 15:32 ` Re: Query Performance Issue Justin Pryzby <pryzby@telsasoft.com>
2018-12-29 06:58 ` Re: Query Performance Issue David Rowley <david.rowley@2ndquadrant.com>
2018-12-29 20:27 ` Re: Query Performance Issue Jeff Janes <jeff.janes@gmail.com>
2018-12-29 07:15 ` Re: Query Performance Issue Justin Pryzby <pryzby@telsasoft.com>
2018-12-29 12:00 ` Re: Query Performance Issue Jim Finnerty <jfinnert@amazon.com>
2018-12-29 22:00 ` Re: Query Performance Issue David Rowley <david.rowley@2ndquadrant.com>
2020-09-04 21:18 Query performance issue Nagaraj Raj <nagaraj.sf@yahoo.com>
2020-09-04 21:23 ` Thomas Kellerer <shammat@gmx.net>
2020-09-04 21:24 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
2020-09-04 21:39 ` Michael Lewis <mlewis@entrata.com>
2020-09-04 21:41 ` Michael Lewis <mlewis@entrata.com>
2020-09-04 21:44 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
2020-09-04 21:55 ` Michael Lewis <mlewis@entrata.com>
2020-09-04 22:20 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
2020-09-05 08:16 ` David Rowley <dgrowleyml@gmail.com>
2020-09-05 13:42 ` Michael Lewis <mlewis@entrata.com>
2020-09-05 21:49 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
2020-09-04 21:36 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2021-01-22 01:53 Query performance issue Nagaraj Raj <nagaraj.sf@yahoo.com>
2021-01-22 02:35 ` Justin Pryzby <pryzby@telsasoft.com>
2021-02-14 22:03 ` Tomas Vondra <tomas.vondra@enterprisedb.com>
2021-02-16 16:40 ` Michael Lewis <mlewis@entrata.com>
2024-07-10 07:11 Query performance issue Dheeraj Sonawane <Dheeraj.Sonawane@ethoca.com>
2024-07-10 13:40 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox