X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 9173411F674C for ; Wed, 12 Apr 2006 20:04:20 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.129 X-Spam-Level: X-Spam-Status: No, score=0.129 required=5 tests=[AWL=0.128, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id cFIz65GYqtRS for ; Wed, 12 Apr 2006 20:04:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by postgresql.org (Postfix) with ESMTP id AB16311F66FF for ; Wed, 12 Apr 2006 20:04:15 -0300 (ADT) Received: from ausbayes2.aus.pervasive.com ([172.16.8.6]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Wed, 12 Apr 2006 18:04:16 -0500 Received: from AUSOWA.aus.pervasive.com ([172.16.4.8]) by ausbayes2.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 12 Apr 2006 18:04:16 -0500 Received: from ausmailid.aus.pervasive.com ([172.16.4.64]) by AUSOWA.aus.pervasive.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 12 Apr 2006 18:04:15 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C65E85.59766D41" Subject: Re: Better index stategy for many fields with few values Date: Wed, 12 Apr 2006 18:03:45 -0500 Message-ID: <4D27CB1096EF1C408F4BFAB0046EC7B6099EC4@ausmailid.aus.pervasive.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] Better index stategy for many fields with few values Thread-Index: AcZehGjK43fIQwifRjun3rereip+wQAAORaQ From: "Jim Nasby" To: "Oscar Picasso" Cc: "Pgsql-Performance \(E-mail\)" X-OriginalArrivalTime: 12 Apr 2006 23:04:15.0767 (UTC) FILETIME=[6BBA4A70:01C65E85] X-Archive-Number: 200604/277 X-Sequence-Number: 18361 This is a multi-part message in MIME format. ------_=_NextPart_001_01C65E85.59766D41 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Adding -performance back in -----Original Message----- From: Oscar Picasso [mailto:oscgoogle@yahoo.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?=20 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" 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. >=20 > Yes, that's true, the index overhead gets too high. >=20 > > I was also thinking about about using a functional index. >=20 > 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: >=20 > As your paramXX values can have only 10 parameters, it also might be > feasible to use a bunch of 10 conditional indices, like: >=20 > CREATE INDEX foo1 ON table (param1, param2 WHERE param0=3D'1st value'; > CREATE INDEX foo2 ON table (param1, param2 WHERE param0=3D'2nd value'; > CREATE INDEX foo3 ON table (param1, param2 WHERE param0=3D'3rd value'; > [...] Not all that weird; it's known as index partitioning. --=20 Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.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 _____ =20 Yahoo! = Messenger with Voice. = PC-to-Phone calls for ridiculously low rates. ------_=_NextPart_001_01C65E85.59766D41 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Adding=20 -performance back in
-----Original Message-----
From: Oscar Picasso=20 [mailto:oscgoogle@yahoo.com]
Sent: Wednesday, April 12, 2006 = 5:51=20 PM
To: Jim Nasby
Subject: Re: [PERFORM] Better = index=20 stategy for many fields with few values

I would like to try it.

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

Doesn't this new requirement rule out this = solution? 
No, just=20 group the columns logically.
 By the way I have = test to=20 index each column individually and check what happens in relation to = bitscan=20 map. My test table  is 1 million  rows. The explain analyze = command=20 shows that a bit scan is sometimes used but I still end up with = queries that=20 can take up to 10s which is way to much.


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

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

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

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

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

http://archives.postgresql.org

Yahoo!=20 Messenger with Voice. PC-to-Phone calls for ridiculously low=20 rates. ------_=_NextPart_001_01C65E85.59766D41--