Re: Partial indexes (was: Re: Indexing a boolean) - Mailing list pgsql-admin

Tom's example shows that the query actually takes
longer using the index than without.  And that's not
surprising.  A btree index doesn't buy you much when
you have very low cardinality.  That's ideal for bitmap
indexes that PostgreSQL currently doesn't offer.

In the expample of the original question no index on
the boolean column is probably also better/faster.
Just try it.

What may help is a multicolum index.
So lets say you have the columns first_name, last_name,
gender.  Instead of creating one index for last_name
and one for gender.  Just create a multicolumn index on
last_name and gender.  Of course that assumes that you
have both columns in your WHERE clause combined with an
AND.

CREATE INDEX name_gender ON person (last_name, gender);

SELECT *
FROM person
WHERE last_name='Smith'
AND gender='male';

Regards,
Nikolaus

On Fri, 22 Aug 2003 10:34:39 -0400, Tom Lane wrote:

>
> "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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster