CODEWITHSUNDEEP
postgresqlpostgresql-8.2
Anthony Towns
Anthony Towns

Reputation: 2914

Partial index not being used in psql 8.2

I would like to run a query on a large table along the lines of:

SELECT DISTINCT user FROM tasks WHERE ctime >= '2012-01-01' AND ctime < '2013-01-01' AND parent IS NULL;

There is already an index on tasks(ctime), but most (75%) of rows have a non-NULL parent, so that's not very effective.

I attempted to create a partial index for those rows:

CREATE INDEX CONCURRENTLY task_ctu_np ON tasks (ctime, user) WHERE parent IS NULL;

but the query planner continues to choose the tasks(ctime) index instead of my partial index.

I'm using postgresql 8.2 on the server, and my psql client is 8.1.

Upvotes: 0

Views: 55

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

First, I second Richard's suggestion that upgrading should be at the top of your priority. The areas of partial indexes, etc. have, as I understood it, improved significantly since 8.2.

The second thing is you really need the actual query plans with timing information (EXPLAIN ANALYZE) because without these we can't talk about selectivity, etc.

So my order of business if I were you would be to upgrade first and then tune after that.

Now, I understand that 8.3 is a big upgrade (it is the only one that caused us issues in LedgerSMB). You may need some time to address that, but the alternative is to get further behind and be asking questions on a version that is less and less in current understanding as time goes on.

Upvotes: 1

Related Questions