Does this change...
---
Allow index scans to use an intermediate in-memory bitmap (Tom)
In previous releases, only a single index could be used to do
lookups on a table. With this feature, if a query has WHERE tab.col1 =
4 and tab.col2 = 9, and there is no multicolumn index on col1 and
col2, but there is an index on col1 and another on col2, it is
possible to search both indexes and combine the results in memory,
then do heap fetches for only the rows matching both the col1 and col2
restrictions. This is very useful in environments that have a lot of
unstructured queries where it is impossible to createindexes that
match all possible access conditions. Bitmap scans are useful even
with a single index, as they reduce the amount of random access
needed; a bitmap index scan is efficient for retrieving fairly large
fractions of the complete table, whereas plain index scans are not.
---
...in 8.1 mean that it's no longer necessary to have a multi-column
index when using a multi-column ORDER BY clause? I was hoping it
would, but my testing suggests it won't. (I can provide my test
output if desired, but it looks like this: cost=0.00..0.99 when
there's a multi-col index, cost=176727.37..176727.39 when there
isn't.)
Maybe I should explain my problem...
I allow paging through results, sortable by any column. I always use
the PK column as the secondary sort, so that I can track where the
paging is at (and keep the sort stable). Until now I've had (col,
pk_col) indexes on for every column in every pageable table, but that
hurts insert performance (I need both fast read and insert). Is there
a way to achieve this without ton of two-col indexes?
Thanks in advance.
Adam Pritchard
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
