Re: Better index stategy for many fields with few values

Adding -performance back in

-----Original Message-----
From: Oscar Picasso [mailto:oscgoogle(at)yahoo(dot)com]
Sent: Wednesday, April 12, 2006 5:51 PM
To: Jim Nasby
Subject: Re: [PERFORM] Better index stategy for many fields with few values

I would like to try it.

However in an other post I added that contrary to what I stated initially all the paramXX columns are not mandatory in the query. So it seems that requirement make the problem more complexe.

Doesn't this new requirement rule out this solution?

No, just group the columns logically.

By the way I have test to index each column individually and check what happens in relation to bitscan map. My test table is 1 million rows. The explain analyze command shows that a bit scan is sometimes used but I still end up with queries that can take up to 10s which is way to much.

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> wrote:

On Wed, Apr 12, 2006 at 02:59:32PM +0200, Markus Schaber wrote:
> > I was thinking about using a multicolumns index, but I have read that
> > we should limit multicolumns indice to at most 2 or 3 columns.
>
> Yes, that's true, the index overhead gets too high.
>
> > I was also thinking about about using a functional index.
>
> If there's a logical relation between those values that they can easily
> combined, that may be a good alternative.

How would that be any better than just doing a multi-column index?

> I just had another weird idea:
>
> As your paramXX values can have only 10 parameters, it also might be
> feasible to use a bunch of 10 conditional indices, like:
>
> CREATE INDEX foo1 ON table (param1, param2 WHERE param0='1st value';
> CREATE INDEX foo2 ON table (param1, param2 WHERE param0='2nd value';
> CREATE INDEX foo3 ON table (param1, param2 WHERE param0='3rd value';
> [...]

Not all that weird; it's known as index partitioning.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

_____

Yahoo! <http://us.rd.yahoo.com/mail_us/taglines/postman3/*http://us.rd.yahoo.com/evt=39666/*http://beta.messenger.yahoo.com> Messenger with Voice. PC-to-Phone calls for ridiculously low rates.