Обсуждение: Possible regression (slow query on 9.2/9.3 when compared to 9.1)
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.
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
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