From: | <ogjunk-pgjedan(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Multi-column index not used, new flipped column index is |
Date: | 2006-05-10 16:21:43 |
Message-ID: | 20060510162143.96628.qmail@web50306.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
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;
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 .
Thanks,
Otis
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Schaber | 2006-05-10 16:46:10 | Re: Multi-column index not used, new flipped column index is |
Previous Message | Michael Glaesemann | 2006-05-10 03:24:56 | Re: Most efficient way to hard-sort records |