From: Markus Schaber <schabi@logix-tt.com>
To: ogjunk-pgjedan@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Multi-column index not used, new flipped column index is
Date: Wed, 10 May 2006 18:46:10 +0200
Message-ID: <446218D2.8030002@logix-tt.com> (raw)
In-Reply-To: <20060510162143.96628.qmail@web50306.mail.yahoo.com>
References: <20060510162143.96628.qmail@web50306.mail.yahoo.com>
Hi, Otis,
ogjunk-pgjedan@yahoo.com wrote:
> I'm using PG 8.0.3 and recently spotted a query that was not using a
> multi-column index I had created. The index looks like:
>
> CREATE INDEX ..... ON FooTable(fkColumnOne, fkColumnTwo);
>
> The query that was not using the index was using:
>
> SELECT .... a bunch of columns and joins .... WHERE
> FooTable.fkColumnTwo=1000;
That's how it is supposed to be currently. AFAIK, Oracle has a technique
called "index skip scan" that could take (limited) use of that second
index, but PostgreSQL currently does not support them.
> So I added another index where the indexed columns are flipped:
>
> CREATE INDEX ..... ON FooTable(fkColumnTwo, fkColumnOne);
>
> Now the query started to use that index -- gooooood! But now I have 2
> indices that are nearly the same, and that means overhead during
> INSERTs/DELETEs. :(
> Is there a way to get this to use that first index, so I don't have
> to have this additional index? If I had PG 8.1.3, would PG know how
> to use that first index? I seem to recall something about this
> somewhere... but I don't see it at
> http://www.postgresql.org/docs/whatsnew .
Bitmap Index Scans can be your solution, but AFAIK they were invented in
8.1.
For bitmap index scans, you have one index on fkColumnOne and one on
fkColumnTwo, and the query planner knows to combine them when both
columns are given in a WHERE clause.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
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-sql@postgresql.org
Cc: schabi@logix-tt.com, ogjunk-pgjedan@yahoo.com
Subject: Re: Multi-column index not used, new flipped column index is
In-Reply-To: <446218D2.8030002@logix-tt.com>
* 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