pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.




view thread (4+ messages)  latest in thread

Message-ID: <puhdp5mm0r.fsf@srv.protecting.net>
Permalink:  ../puhdp5mm0r.fsf@srv.protecting.net/
Also on:    postgresql.org/message-id/puhdp5mm0r.fsf@srv.protecting.net

 · 

reply

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