Re: Indexes on low cardinality columns

On Sat, Oct 17, 2009 at 10:02 AM, Vikul Khosla <vkhosla(at)gridsolv(dot)com> wrote:
>
> 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 ?

Was the bitmap index in Oracle used all by itself, or was it used in
concert with other bitmaps (either native bitmap indexes or a bitmap
conversion of a non-bitmap index) to produce the speed up?

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

Have you tired setting enable_seqscan=off to see what plan that
produces and whether it is faster or slower? If it is better, then
lowering random_page_cost or increasing cpu_tuple_cost might help
motivate it to make that decision without having to resort to
enable_seqscan. Of course tuning those setting just to focus on one
query could backfire rather badly.

Jeff