Re: [PERFORM] integer[] indexing.

[Pierre-Frdric Caillaud]

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}'));

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] integer[] indexing.

[Harald Fuchs]

In article [EMAIL PROTECTED],
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= [EMAIL PROTECTED] 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.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] integer[] indexing.

[Tom Lane]

Dawid Kuroczko [EMAIL PROTECTED] writes:
 But when I phrase the query:

 SELECT * FROM table WHERE (icount(ids) = 1 AND ids[1] = 33) OR
 (icount(ids)  1 AND ids  '{33}');

 Planner insists on using seqscan.  Even with enable_seqscan = off;

The OR-index-scan mechanism isn't currently smart enough to use partial
indexes that are only valid for some of the OR'd clauses rather than all
of them.  Feel free to fix it ;-).  (This might not even be very hard;
I haven't looked.)

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]