Re: integer[] indexing.

In article <opsfjonlc0cq72hf(at)musicbox>,
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= <lists(at)boutiquenumerique(dot)com> writes:

> disclaimer : brainless proposition

> (SELECT * FROM table WHERE (icount(ids) <= 1 AND ids[1] = 33)
> UNION ALL
> (SELECT * FROM table WHERE (icount(ids) > 1 AND ids && '{33}'));

I guess my proposition is even more brainless :-)

If 95% of all records have only one value, how about putting the first
(and most often only) value into a separate column with a btree index
on it? Something like that:

CREATE TABLE tbl (
-- other columns
id1 INT NOT NULL,
idN INT[] NULL
);

CREATE INDEX tbl_id1_ix ON tbl (id1);

If id1 is selective enough, you probably don't need another index on idn.