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@xxxxxxx>
To: Vikul Khosla <vkhosla@xxxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
Sent: Fri, October 16, 2009 8:27:15 PM
Subject: Re: Indexes on low cardinality columns
On Fri, Oct 16, 2009 at 4:36 PM, Vikul Khosla <vkhosla@xxxxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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@xxxxxxx>
To: Vikul Khosla <vkhosla@xxxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
Sent: Fri, October 16, 2009 8:27:15 PM
Subject: Re: Indexes on low cardinality columns
On Fri, Oct 16, 2009 at 4:36 PM, Vikul Khosla <vkhosla@xxxxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
