Thread: 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
thatas the size of the data increases, it will get worse. Of course the plan may change when that happens but I'd also
liketo 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
inan 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
guessesas to the reasons the default plan is slow or how to affect it, can someone enlighten me? 

Regards
Oliver Kohll
www.agilebase.co.uk

Thanks,

It does look like an incorrect prediction. Looking again, I think it's the row estimate for the join that's out - the
plannerestimates 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
fromthe query plan where there is a slow query and automatically test turning off nested joins. I'll just have to keep
aneye 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:

> 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