Re: [PERFORM] Better index stategy for many fields with few values

Hi Markus,Markus Schaber <[EMAIL PROTECTED]> wrote:>Hi, Oscar,>>Please reply to the list and not privately, so others can learn from>your replies, and possibly have better Ideas than me.That was my intention. I made a mistake.>Oscar Picasso wrote:>>> I cannot group the columns logically. Any column

Re: [PERFORM] Better index stategy for many fields with few values

Hi, Oscar, Please reply to the list and not privately, so others can learn from your replies, and possibly have better Ideas than me. Oscar Picasso wrote: > I cannot group the columns logically. Any column may or may not appear > in a query. That's suboptimal. > Summrarizing what I have learne

Re: [PERFORM] Better index stategy for many fields with few values

Hi, Jim, Jim Nasby wrote: > Adding -performance back in > 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.

Re: [PERFORM] Better index stategy for many fields with few values

Hi, Jim, Jim C. Nasby wrote: >>>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? 10 different values

Re: [PERFORM] Better index stategy for many fields with few values

Adding -performance back in -Original Message-From: Oscar Picasso [mailto:[EMAIL PROTECTED]Sent: Wednesday, April 12, 2006 5:51 PMTo: Jim NasbySubject: Re: [PERFORM] Better index stategy for many fields with few values I would like to try it.However in an other post I

Re: [PERFORM] Better index stategy for many fields with few

Oscar, On 4/10/06 9:58 AM, "Oscar Picasso" <[EMAIL PROTECTED]> wrote: > - My items table: > code int -- can take one of 100 values > property varchar(250) -- can take one of 5000 values > param01 char(10) -- can take one of 10 values > param02 char(10) -- can take one of 10

Re: [PERFORM] Better index stategy for many fields with few values

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 abou

Re: [PERFORM] Better index stategy for many fields with few values

Hi, Oscar, Oscar Picasso wrote: > [ all the 20 paramXX columns are used in the query} > How can I optimize this kind of query? PostgreSQL 8.1 has so-called bitmap index scans, which can combine several index scans before actually accessing the data. So I think it's best to create an index on e

Re: [PERFORM] Better index stategy for many fields with few values

- My items table: code int -- can take one of 100 values property varchar(250) -- can take one of 5000 values param01 char(10) -- can take one of 10 values param02 char(10) -- can take one of 10 values ... [ 20 similar columns } ... parama20 char(10) -- can take one of

[PERFORM] Better index stategy for many fields with few values

Hi,I want to optimize something like this.- My items table:code int  -- can take one of 100 valuesproperty varchar(250) -- can take one of 5000 valuesparam01 char(10)  -- can take one of 10 valuesparam02 char(10)  -- can take one of 10 values...[ 20 similar columns }...parama20