Thread: How to create index on only some of the rows

Hello.

How do you create an index for only some of the rows in a table? I
read in the docs:

 "The expression used in the WHERE clause can refer only to columns of
the underlying table, but it can use all columns, not just the ones
being indexed. Presently, subqueries and aggregate expressions are
also forbidden in WHERE. The same restrictions apply to index fields
that are expressions. "

So until this changes, can you just add a boolean field to tell if the
column should be used in the index, and then run  "create index ....
where use_in_index = true"  or are there other (better?) ways of doing
this?

On Feb 7, 2011, at 11:00 PM, A B wrote:
> So until this changes, can you just add a boolean field to tell if the
> column should be used in the index, and then run  "create index ....
> where use_in_index = true"  or are there other (better?) ways of doing
> this?


If you want you can do that Or You can use proper where clause as given below:
CREATE INDEX indexname on tablename(columname) where condition;

example:
create index directed_graph_idx on directed_graph(node_from) where node_from in ('A','B');

Thanks & Regards,
Vibhor Kumar
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

On Mon, Feb 07, 2011 at 03:00:54PM +0100, A B wrote:
> So until this changes, can you just add a boolean field to tell if the
> column should be used in the index, and then run  "create index ....
> where use_in_index = true"  or are there other (better?) ways of doing
> this?

What are the criteria for inclusion in the index?  Those would be the
criteria you put in your WHERE clause.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On 2011-02-07, A B <gentosaker@gmail.com> wrote:
> Hello.
>
> How do you create an index for only some of the rows in a table? I
> read in the docs:
>
>  "The expression used in the WHERE clause can refer only to columns of
> the underlying table, but it can use all columns, not just the ones
> being indexed. Presently, subqueries and aggregate expressions are
> also forbidden in WHERE. The same restrictions apply to index fields
> that are expressions. "
>
> So until this changes, can you just add a boolean field to tell if the
> column should be used in the index, and then run  "create index ....
> where use_in_index = true"  or are there other (better?) ways of doing
> this?

the manual section quoted above is not exactly correct,
you are also allowed constants, immutable functions and operators.

 create index foo_bigbar on foo(bar) where quux > 8.6;
 create index foo_bar_nofred on foo(bar) where fred is null;

etc...





--
⚂⚃ 100% natural