Re: [GENERAL] How to create index on only some of the rows
On 2011-02-07, A B 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to create index on only some of the rows
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 a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to create index on only some of the rows
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.ku...@enterprisedb.com
Blog:http://vibhork.blogspot.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
