In article <opsfjonlc0cq72hf@musicbox>,
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= <lists@boutiquenumerique.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.