Thread: multicolumn indexes still efficient if not fully stressed?
Hello, I created a multicolumn index on the columns c_1,..,c_n . If I do use only a true subset of these columns in a SQL query, is the index still efficient? Or is it better to create another multicolumn index defined on this subset? Thanks for any comments!
On Mon, 2009-01-12 at 18:49 +0100, Jörg Kiegeland wrote: > Hello, > > I created a multicolumn index on the columns c_1,..,c_n . > If I do use only a true subset of these columns in a SQL query, is the > index still efficient? > Or is it better to create another multicolumn index defined on this subset? > > Thanks for any comments! Why would you create a multicolumn index for all columns if that's not what you actually query on? The order of columns matter for multicolumn indexes. Multicolumn indexes work best for queries that use all of the columns in the index, but can also be helpful if at least the leftmost columns in the index are specified in the query. So it depends on the order. If the index is defined on (c_1, c_2, c_3, c_4) and your query includes: "WHERE c_2=val AND c_3=val AND c_4=val", then the index is almost certainly useless. On the other hand, if you were to query "WHERE c_1=val" then if c_1 is highly selective the index would still help. See here: http://www.postgresql.org/docs/8.3/interactive/indexes-multicolumn.html -- Mark Lewis
Jörg Kiegeland <kiegeland@ikv.de> schrieb: > Hello, > > I created a multicolumn index on the columns c_1,..,c_n . > If I do use only a true subset of these columns in a SQL query, is the > index still efficient? > Or is it better to create another multicolumn index defined on this subset? Create several indexes for each column, since 8.1 PG can use a so called Bitmap Index Scan. Read more about that: - http://www.postgresql-support.de/pgbook/node492.html (in german, i think, you can read that) - http://en.wikipedia.org/wiki/Bitmap_index 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." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Mon, Jan 12, 2009 at 12:23 PM, Andreas Kretschmer <akretschmer@spamfence.net> wrote: > Jörg Kiegeland <kiegeland@ikv.de> schrieb: > >> Hello, >> >> I created a multicolumn index on the columns c_1,..,c_n . >> If I do use only a true subset of these columns in a SQL query, is the >> index still efficient? >> Or is it better to create another multicolumn index defined on this subset? > > Create several indexes for each column, since 8.1 PG can use a so called > Bitmap Index Scan. Read more about that: I've found that when you do frequently query on two or more columns, a multi-column index is faster than bitmap scans, especially for larger data sets.
On Monday 12 January 2009, "Scott Marlowe" <scott.marlowe@gmail.com> wrote: > I've found that when you do frequently query on two or more columns, a > multi-column index is faster than bitmap scans, especially for larger > data sets. Very much faster, especially if you're only looking for a few dozen or hundred rows out of multi-million row tables. -- Current Peeve: The mindset that the Internet is some sort of school for novice sysadmins and that everyone -not- doing stupid dangerous things should act like patient teachers with the ones who are. -- Bill Cole, NANAE