Skip site navigation (1) Skip section navigation (2)

Disabling nested loops - worst case performance

From: Anssi Kääriäinen <anssi(dot)kaariainen(at)thl(dot)fi>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Disabling nested loops - worst case performance
Date: 2011-03-18 07:15:51
Message-ID: 4D8306A7.7020205@thl.fi (view raw or flat)
Thread:
Lists: pgsql-performance
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




Responses

pgsql-performance by date

Next:From: Arjen van der MeijdenDate: 2011-03-18 07:16:56
Subject: Re: Request for feedback on hardware for a new database server
Previous:From: Pavel StehuleDate: 2011-03-18 07:02:05
Subject: Re: Disabling nested loops - worst case performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group