From: Anssi Kääriäinen <anssi.kaariainen@thl.fi>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Disabling nested loops - worst case performance
Date: Fri, 18 Mar 2011 09:15:51 +0200
Message-ID: <4D8306A7.7020205@thl.fi> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: anssi.kaariainen@thl.fi
Subject: Re: Disabling nested loops - worst case performance
In-Reply-To: <4D8306A7.7020205@thl.fi>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox