Skip site navigation (1) Skip section navigation (2)

Re: integer[] indexing.

From: Harald Fuchs <hf0722x(at)protecting(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: integer[] indexing.
Date: 2004-10-08 12:28:52
Message-ID: puhdp5mm0r.fsf@srv.protecting.net (view raw or flat)
Thread:
Lists: pgsql-performance
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.


In response to

pgsql-performance by date

Next:From: simonDate: 2004-10-08 12:38:01
Subject: Re: Re: Data warehousing requirements
Previous:From: Mike HardingDate: 2004-10-08 12:10:29
Subject: COPY slows down?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group