Reg Me Please wrote:
Only one question remains in my mind:
why the planner is not using the partial index?
The partial index is covering 2 predicates out of the 3 used in the where
condition. Actually there is a boolean flag (to exclude "disabled" rows),
a timestamp (for row age) and an int8 (a FK to another table).
The first two are in the partial index in order to exclude "disabled" and
older rows. The int8 is the "random" key I mentioned earlier.
So the WHERE condition reads like:
flag AND tstz >= '2008-01-01'::timestamptz and thekey=42
I can see in the EXPLAIN that there is no mention to the partial index.
Please keep in mind that the table has 8+M rows, few of which are flagged,
about 70% don't match the age limit and few dozens match the key.
In my opinion the partial index should help a lot.
--
Fahrbahn ist ein graues Band
weisse Streifen, grüner Rand
For an index to be used the where clause must match the index. As the
index gets more complicated its less likely to be used.
I have 5 indexes on one table to answer the 5 possible ways the where
clause can look like.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
