In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: http://stackoverflow.com/a/12026593/808921

I am attempting to make use of the "partial index" feature of PostgreSQL that Erwin Brandstetter recommends. I have a table with a couple million records already, and I would like to add the index to that table, like so:

CREATE INDEX schema_defs_deprovision ON schema_defs (deprovision) 
WHERE deprovision = 0;

(the vast majority of the records will have deprovision = 1)

The problem is that when I try to use this index with the simplest of queries, PostgreSQL acts as if it isn't there:

explain select * from schema_defs where deprovision = 0;

Seq Scan on schema_defs (cost=0.00..1.05 rows=1 width=278)
Filter: (deprovision = 0)

The really strange thing is that I have found that if this index is created before there is data in the table, then it does actually work fine. Don't believe me? Here are some SQL Fiddle entries that prove it:

Partial Index Created After Inserts (Index not working)

Partial Index Created Before Inserts (Index working properly)

In both of those, just expand the "View Execution Plan" link to see what I'm talking about.

So, my question is - what do I have to do in order to get PostgreSQL to start using a partial index on a table that had data in it before the index was created?

BTW I'm also the developer of SQL Fiddle and this question is related to a new development effort I'm making for it.

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: https://stackoverflow.com/a/12026593/808921

I am attempting to make use of the "partial index" feature of PostgreSQL that Erwin Brandstetter recommends. I have a table with a couple million records already, and I would like to add the index to that table, like so:

CREATE INDEX schema_defs_deprovision ON schema_defs (deprovision) 
WHERE deprovision = 0;

(the vast majority of the records will have deprovision = 1)

The problem is that when I try to use this index with the simplest of queries, PostgreSQL acts as if it isn't there:

explain select * from schema_defs where deprovision = 0;

Seq Scan on schema_defs (cost=0.00..1.05 rows=1 width=278)
Filter: (deprovision = 0)

The really strange thing is that I have found that if this index is created before there is data in the table, then it does actually work fine. Don't believe me? Here are some SQL Fiddle entries that prove it:

Partial Index Created After Inserts (Index not working)

Partial Index Created Before Inserts (Index working properly)

In both of those, just expand the "View Execution Plan" link to see what I'm talking about.

So, my question is - what do I have to do in order to get PostgreSQL to start using a partial index on a table that had data in it before the index was created?

BTW I'm also the developer of SQL Fiddle and this question is related to a new development effort I'm making for it.

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: http://stackoverflow.com/a/12026593/808921

I am attempting to make use of the "partial index" feature of PostgreSQL that Erwin Brandstetter recommends. I have a table with a couple million records already, and I would like to add the index to that table, like so:

CREATE INDEX schema_defs_deprovision ON schema_defs (deprovision) 
WHERE deprovision = 0;

(the vast majority of the records will have deprovision = 1)

The problem is that when I try to use this index with the simplest of queries, PostgreSQL acts as if it isn't there:

explain select * from schema_defs where deprovision = 0;

Seq Scan on schema_defs (cost=0.00..1.05 rows=1 width=278)
Filter: (deprovision = 0)

The really strange thing is that I have found that if this index is created before there is data in the table, then it does actually work fine. Don't believe me? Here are some SQL Fiddle entries that prove it:

Partial Index Created After Inserts (Index not working)

Partial Index Created Before Inserts (Index working properly)

In both of those, just expand the "View Execution Plan" link to see what I'm talking about.

So, my question is - what do I have to do in order to get PostgreSQL to start using a partial index on a table that had data in it before the index was created?

BTW I'm also the developer of SQL Fiddle and this question is related to a new development effort I'm making for it.

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: http://stackoverflow.com/a/12026593/808921

I am attempting to make use of the "partial index" feature of PostgreSQL that Erwin Brandstetter recommends. I have a table with a couple million records already, and I would like to add the index to that table, like so:

CREATE INDEX schema_defs_deprovision ON schema_defs (deprovision) 
WHERE deprovision = 0;

(the vast majority of the records will have deprovision = 1)

The problem is that when I try to use this index with the simplest of queries, PostgreSQL acts as if it isn't there:

explain select * from schema_defs where deprovision = 0;

Seq Scan on schema_defs (cost=0.00..1.05 rows=1 width=278)
Filter: (deprovision = 0)

The really strange thing is that I have found that if this index is created before there is data in the table, then it does actually work fine. Don't believe me? Here are some SQL Fiddle entries that prove it:

Partial Index Created After Inserts (Index not working)

Partial Index Created Before Inserts (Index working properly)

In both of those, just expand the "View Execution Plan" link to see what I'm talking about.

So, my question is - what do I have to do in order to get PostgreSQL to start using a partial index on a table that had data in it before the index was created?

BTW I'm also the developer of SQL Fiddle and this question is related to a new development effort I'm making for it.