> - CREATE INDEX xifoo ON foo(bar_id);
In this simple (which means "reduced") test database, yes. But the
actual table "foo" in production database:
1. partitioned, with 50+ partitions
2. heavily updated (and indexes make it slow)
3. has more fields like bar_id
We had indexes on several fields based on typical access patterns, but
after "foo" grew to a certain size (many gigabytes), sequential scans on
selected partitions became the only feasible solution.
We can fix this particular query with an index, but the more general
problem with planner choosing multiple scans over big table due to wrong
estimate of results from the small table, remains.
Alex
Rodrigo E. De León Plicet wrote:
On Wed, Apr 2, 2008 at 12:36 PM, Alex Solovey <a.solovey@xxxxxxxxx> wrote:
... I have no idea how it could be fixed.
- CREATE INDEX xifoo ON foo(bar_id);
- ANALYZE;
- Retry.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
