Re: Indexes on low cardinality columns

From: Vikul Khosla <webmaster(at)gridsolv(dot)com>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Indexes on low cardinality columns
Date: 2009-10-17 15:21:38
Message-ID: 771067.43371.qm@web303.biz.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Vikul Khosla 2009-10-17 17:02:55 Re: Indexes on low cardinality columns
Previous Message Robert Haas 2009-10-17 10:56:56 Re: [PERFORM] Regarding facing lot of time Consumed by Socket.Poll()