Fantastic
Fantastic
Posted Sep 25, 2005 23:17 UTC (Sun) by yem (guest, #1138)Parent article: PostgreSQL 8.1 Beta 2 available
I just compiled up a fresh 8.1b2 and loaded in a 4.5Gb database that was performing poorly on 7.3. Here's what happened when I ran the offending query (a SELECT with two AND'd conditions each of which has an index):
Dual Xeon 2.4Ghz, 2Gb RAM, tuned 7.3: 2673 secs
Single P4 2.6Ghz, 1Gb RAM, default 8.1b2: 23 secs
I suspect this feature can take the credit for this improvement:
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.