Re: Disabling nested loops - worst case performance

Hello

for example queries with LIMIT clause can be significantly faster with
nested loop. But you don't need to disable nested loop globally.

You can wrap your query to sql functions and disable nested loop just
for these functions.

Regards

Pavel Stehule

2011/3/18 Anssi Kääriäinen <anssi(dot)kaariainen(at)thl(dot)fi>:
> Hello list,
>
> I am working on a Entity-Attribute-Value (EAV) database using PostgreSQL
> 8.4.7. The basic problem is that when joining multiple times different
> entities the planner thinks that there is vastly less rows to join than
> there is in reality and decides to use multiple nested loops for the join
> chain. This results in queries where when nested loops are enabled, query
> time is somewhere around 35 seconds, but with nested loops disabled, the
> performance is somewhere around 100ms. I don't think there is much hope for
> getting better statistics, as EAV is just not statistics friendly. The
> values of an attribute depend on the type of the attribute, and different
> entities have different attributes defined. The planner has no idea of these
> correlations.
>
> Now, my question is: if I disable nested loops completely for the users of
> the EAV database what kind of worst case performance loss can I expect? I
> don't mind if a query that normally runs in 100ms now takes 200ms, but about
> problems where the query will take much more time to complete than with
> nested loops enabled. As far as I understand these cases should be pretty
> rare if non-existent?
>
>  - Anssi
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>