Re: [PERFORM] multicolumn indexes still efficient if not fully stressed?
Jörg Kiegeland kiegel...@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° -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] multicolumn indexes still efficient if not fully stressed?
On Mon, Jan 12, 2009 at 12:23 PM, Andreas Kretschmer akretsch...@spamfence.net wrote: Jörg Kiegeland kiegel...@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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] multicolumn indexes still efficient if not fully stressed?
On Monday 12 January 2009, Scott Marlowe scott.marl...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
