explain analyse and nested loop joins
Hi,
I have a query I'm trying to optimise. It takes just under a second to run, not too bad for my users but I'm worried that as the size of the data increases, it will get worse. Of course the plan may change when that happens but I'd also like to learn a bit more about optimisation anyway.
The explain is here:
http://explain.depesz.com/s/Ost
- that one took 690ms. Seeing it had a couple of nested joins at the top, I 'set enable_nestloop = false;', resulting in an improvement of about 20x:
http://explain.depesz.com/s/BRi
The query is below. It joins to a second report dbvcalc_delivery_charges which I can also send if necessary. I've only guesses as to the reasons the default plan is slow or how to affect it, can someone enlighten me?
Regards
Oliver Kohll
www.agilebase.co.uk
Oops, forgot to include the query, it's
SELECT b2deliveryorders.idb2deliveryorders, a2clientpremises.ida2clientpremises, a2clientpremises.premisesname, a2clientpremises.town, b2deliveryorders.expectedby, b2deliveryorders.dateordered, b2deliveryorders.invoicenumber, b2deliveryorders.deliverymethod, b2deliveryorders.driver, dbvcalc_delivery_charges.total, dbvcalc_delivery_charges.boxes, b2deliveryorders.createdbyauto
FROM b2deliveryorders
LEFT JOIN a2clientpremises ON b2deliveryorders.a2clientpremisespremisesname = a2clientpremises.ida2clientpremises
LEFT JOIN dbvcalc_delivery_charges ON b2deliveryorders.idb2deliveryorders = dbvcalc_delivery_charges.idb2deliveryorders
WHERE b2deliveryorders.complete = false AND b2deliveryorders.invoiced = false
ORDER BY b2deliveryorders.expectedby NULLS FIRST;
Oliver
Begin forwarded message:
Show quoted text
From: Oliver Kohll - Mailing Lists <oliver.lists@gtwm.co.uk>
Subject: explain analyse and nested loop joins
Date: 5 November 2011 19:21:23 GMT
To: pgsql-general <pgsql-general@postgresql.org>Hi,
I have a query I'm trying to optimise.
Thanks,
It does look like an incorrect prediction. Looking again, I think it's the row estimate for the join that's out - the planner estimates one row returned, in which case a nested join would probably make sense, whereas in fact there are 23.
However it's a generated (user created) query, so I think what I might do is get the application to detect this case from the query plan where there is a slow query and automatically test turning off nested joins. I'll just have to keep an eye on it to see if it becomes unnecessary in future PG versions.
Regards
Oliver
www.agilebase.co.uk
On 6 Nov 2011, at 04:17, Pavel Stehule wrote:
Show quoted text
Hello
Propably there are a dependency between following columns - and then a
prediction is not correct.Try to move one less selective to OUTER SELECT
SELECT * FROM (SELECT your query OFFSET 0) x WHERE x.invoiced = false
Regards
Pavel Stehule
2011/11/5 Oliver Kohll - Mailing Lists <oliver.lists@gtwm.co.uk>:
b2deliveryorders.complete = false AND b2deliveryorders.invoiced = false
Import Notes
Reply to msg id not found: CAFj8pRCpJaFkUPoUth2vZ2x3oaKzFmEGHSSVrnsqWn0mJbfaRQ@mail.gmail.com