Re: Partial indexes (was: Re: Indexing a boolean)

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Basically, for partial / functional indexes, the create index where clause
> needs to exactly (or nearly so) match the select query's where clause:

Another consideration is that an index declaration like this:

    create index fooi on foo (flag) where flag;

is really rather redundant, since the entries will only be made for rows
where flag is true, and so storage of the column value in the index is
useless.  You may be able to get more mileage out of the index by making
the index on another column that you often test in conjunction with the
flag.  For example:

regression=# create table foo (flag bool, ts timestamp);
CREATE TABLE
regression=# create index fooi on foo(ts) where flag;
CREATE INDEX
regression=# explain select * from foo where ts > '2003-10-11' and flag;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..22.50 rows=167 width=9)
   Filter: ((ts > '2003-10-11 00:00:00'::timestamp without time zone) AND flag)
(2 rows)

regression=# set enable_seqscan TO 0;
SET
regression=# explain select * from foo where ts > '2003-10-11' and flag;
                               QUERY PLAN
-------------------------------------------------------------------------
 Index Scan using fooi on foo  (cost=0.00..43.25 rows=167 width=9)
   Index Cond: (ts > '2003-10-11 00:00:00'::timestamp without time zone)
   Filter: flag
(3 rows)

(In a more realistic situation, the planner would probably have chosen
the indexscan without any prompting.)

            regards, tom lane