Re: Indexes on low cardinality columns

Thanks Greg!.

Yes, we do need to query on all 3000 values ... potentially. Considering
that when we changed the B-Tree indexes to Bitmap indexes in Oracle
we saw a huge performance boost ... doesn't that suggest that absence of this
feature in PG is a constraint ?

Are there any other clever workarounds to boosting performance involving
low queries on low cardinality columns ? i.e avoiding a full table scan ?

VK

________________________________
From: Greg Stark <gsstark(at)mit(dot)edu>
To: Vikul Khosla <vkhosla(at)gridsolv(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Sent: Fri, October 16, 2009 8:27:15 PM
Subject: Re: [PERFORM] Indexes on low cardinality columns

On Fri, Oct 16, 2009 at 4:36 PM, Vikul Khosla <vkhosla(at)gridsolv(dot)com> wrote:
> In Oracle, we replaced the B-Tree Indexes with Bitmap indexes and saw
> performance go
> through the roof. I know Postgres does not have Bitmap indexes,
> but is there a reasonable alternative to boost performance in situations
> where low cardinality
> columns are involved ?

Do you need to query on all of the 3,000 values?

If it's just particular values which are common i would suggest using
partial indexes on some other column with a where clause restricting
them to only one value in the low-cardinality column. But I wouldn't
want to have 3,000 indexes.

Alternately you could try partitioning the table, though 3,000
partitions is a lot too. If you often update this value then
partitioning wouldn't work well anyways (but then bitmap indexes
wouldn't have worked well in oracle either)

--
greg