pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPossible regression (slow query on 9.2/9.3 when compared to 9.1)
2+ messages / 2 participants
[nested] [flat]
* Possible regression (slow query on 9.2/9.3 when compared to 9.1)
@ 2013-12-26 19:49 Ronaldo Maia <romaia@async.com.br>
0 siblings, 1 reply; 2+ messages in thread
From: Ronaldo Maia @ 2013-12-26 19:49 UTC (permalink / raw)
To: pgsql-performance
Hello everybody.
Recently I have tried to upgrade our postgres instalation from 9.1 to 9.3,
but one query in particular got extremelly slow. The query is:
EXPLAIN ANALYZE SELECT *
FROM sellable
JOIN product ON product.sellable_id = sellable.id
LEFT JOIN storable ON storable.product_id = product.id
LEFT JOIN sellable_category
ON sellable_category.id = sellable.category_id
LEFT JOIN (SELECT storable.id AS storable_id, branch.id AS branch_id,
SUM(product_stock_item.quantity) AS stock,
SUM((product_stock_item.quantity*product_stock_item.stock_cost)) AS
total_stock_cost
FROM storable
CROSS JOIN branch
LEFT JOIN product_stock_item ON product_stock_item.branch_id
= branch.id
AND
product_stock_item.storable_id = storable.id
GROUP BY storable.id, branch.id) AS "_stock_summary"
ON _stock_summary.storable_id = storable.id
WHERE
(_stock_summary.branch_id = '04c3a996-f7c1-11e2-9274-000ae4372716'
OR _stock_summary.branch_id IS NULL)
AND stoq_normalize_string(sellable.description) ILIKE
stoq_normalize_string('%ray%')
AND stoq_normalize_string(sellable_category.description) ILIKE
stoq_normalize_string('%receit%')
On 9.1 it runs in about 500ms, while on a later version, it takes a lot more
than 180000ms (thats 0.5 seconds vs 3 minutes).
Even though this might not be the most well writen query, thats quite some
time difference.
A few things to notice:
- stoq_normalize_string is a wrapper around unaccent marking it as
unmutable, so it can be used to create an index
- The original query has a few more joins but I removed the most I could
without influencing the results.
- The query is actually created using python-storm (an orm for python)
Using git bisect I have found that the problem starts with commit
5b7b5518d0ea56c422a197875f7efa5deddbb388 (And the times I posted above are
from this commit and its parent).
Now this is as far as I can investigate, since my knowledge of the
postgresql inners are between null and zero
Trying to find out where the problem is, here are a few thinks that I have
tried that changed the speed (but does not really fix it for me):
- Replace stoq_normalize_string with unaccent
- Remove the branch_id IS NULL from the where clause
- Remove the left join with sellable_category
There you can download an extract from the database with the needed tables
to reproduce the problem.
http://www.stoq.com.br/~romaia/base.sql.bz2
So, finally, the question is: Is this a regression or was I just luck in
the first place
that the query was 'fast enought' and this is a somewhat expected behaviour
for this query?
--
Ronaldo Maia
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Possible regression (slow query on 9.2/9.3 when compared to 9.1)
@ 2013-12-29 22:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ronaldo Maia <romaia@async.com.br>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2013-12-29 22:32 UTC (permalink / raw)
To: Ronaldo Maia <romaia@async.com.br>; +Cc: pgsql-performance
Ronaldo Maia <romaia@async.com.br> writes:
> Recently I have tried to upgrade our postgres instalation from 9.1 to 9.3,
> but one query in particular got extremelly slow.
FWIW, this test case doesn't reproduce any problem for me --- I get
identical plans and indistinguishable timings (about 450ms on my machine)
from 9.1 and 9.3 branch tips. This is with all-default settings and
a VACUUM ANALYZE after loading the data. I had to guess at the definition
of stoq_normalize_string(), too, so I used
create function stoq_normalize_string(text) returns text language sql
strict immutable as 'select unaccent($1)';
I speculate that you forgot to analyze the data after loading, or there's
some performance-relevant setting that you didn't carry forward from the
9.1 database.
regards, tom lane
--
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] 2+ messages in thread
end of thread, other threads:[~2013-12-29 22:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-12-26 19:49 Possible regression (slow query on 9.2/9.3 when compared to 9.1) Ronaldo Maia <romaia@async.com.br>
2013-12-29 22:32 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox