From: Harald Fuchs <hf0722x@protecting.net>
To: pgsql-performance@postgresql.org
Subject: Re: integer[] indexing.
Date: 08 Oct 2004 14:28:52 +0200
Message-ID: <puhdp5mm0r.fsf@srv.protecting.net> (raw)
References: <758d5e7f041008021163fb40eb@mail.gmail.com>
<opsfjonlc0cq72hf@musicbox>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hf0722x@protecting.net
Subject: Re: integer[] indexing.
In-Reply-To: <puhdp5mm0r.fsf@srv.protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox