18.03.11 09:15, Anssi Kääriäinen написав(ла):
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.
Hello.
If your queries work on single attribute, you can try adding partial
indexes for different attributes. Note that in this case parameterized
statements may prevent index usage, so check also with attribute id inlined.
Best regards, Vitalii Tymchyshyn
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
