pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Multi-column index not used, new flipped column index is 4+ messages / 3 participants [nested] [flat]
* Multi-column index not used, new flipped column index is @ 2006-05-10 16:21 ogjunk-pgjedan@yahoo.com 0 siblings, 1 reply; 4+ messages in thread From: ogjunk-pgjedan@yahoo.com @ 2006-05-10 16:21 UTC (permalink / raw) To: 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Multi-column index not used, new flipped column index is @ 2006-05-10 16:46 Markus Schaber <schabi@logix-tt.com> parent: ogjunk-pgjedan@yahoo.com 0 siblings, 1 reply; 4+ messages in thread From: Markus Schaber @ 2006-05-10 16:46 UTC (permalink / raw) To: ogjunk-pgjedan@yahoo.com; +Cc: pgsql-sql 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Multi-column index not used, new flipped column index is @ 2006-05-10 18:32 Andreas Kretschmer <akretschmer@spamfence.net> parent: Markus Schaber <schabi@logix-tt.com> 0 siblings, 1 reply; 4+ messages in thread From: Andreas Kretschmer @ 2006-05-10 18:32 UTC (permalink / raw) To: pgsql-sql Markus Schaber <schabi@logix-tt.com> schrieb: > Bitmap Index Scans can be your solution, but AFAIK they were invented in > 8.1. Right. > > 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. Yeah! The Bitmap Index Scan is a great thing. With some selects i have a performance-boost from 5 to 10, compared with 8.0. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Multi-column index not used, new flipped column index is @ 2006-05-11 16:13 Andreas Kretschmer <akretschmer@spamfence.net> parent: Andreas Kretschmer <akretschmer@spamfence.net> 0 siblings, 0 replies; 4+ messages in thread From: Andreas Kretschmer @ 2006-05-11 16:13 UTC (permalink / raw) To: pgsql-sql ogjunk-pgjedan@yahoo.com <ogjunk-pgjedan@yahoo.com> schrieb: > Hi Andreas and Markus, > > Bitmap indices sound like a good reason to go from 8.0 to 8.1. Is 8.2 > around the corner, by any chance? IIRC in autumn/winter. If it is finish ;-) > > I searched PG docs to see if I need to do something special to create > Bitmap indices, but couldn't find any information. Are the indices > created the same old CREATE INDEX .... ON Table(column(s)) way and it > is just PG that can choose to use them in this "Bitmap index-way"? Yes, you create indexes on the old way. You have a table with many columns, say, a,b,c,d. On a and b you have a index. Now you have select a,b,c,d from table where a=N and b=M; Prior, PG use one index first and then the other. Now, since 8.1, it combines both indexes in memory first and need only one bitmap index scan on the table. If you have many indexes, and suitable selects, you get a high performance boost. Read http://www.postgresql.org/docs/8.1/static/release-8-1.html, 2nd point. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-05-11 16:13 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2006-05-10 16:21 Multi-column index not used, new flipped column index is ogjunk-pgjedan@yahoo.com 2006-05-10 16:46 ` Markus Schaber <schabi@logix-tt.com> 2006-05-10 18:32 ` Andreas Kretschmer <akretschmer@spamfence.net> 2006-05-11 16:13 ` Andreas Kretschmer <akretschmer@spamfence.net>
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