DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=LJF0U017hKv1QPjF4QkUOo4E1fRQDgVyxmyxcDVlvQmchMWkZPzKAB5iS2+RYDlgs67MS1nFeTSALYI+bL66z4wiE053yRUCPhaSxAveic8qJonb/Lc2UqaJXt9g0lp05fT7OHq/OOYOzMfgpJG4+5rarVlGLNKC3HE8N0Xs/Ic=
	;
Message-ID: <20060413154006.46851.qmail@web54710.mail.yahoo.com>
Date: Thu, 13 Apr 2006 08:40:06 -0700 (PDT)
From: Oscar Picasso <oscgoogle@yahoo.com>
Subject: Re: Better index stategy for many fields with few values
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1643493875-1144942806=:44309"
Content-Transfer-Encoding: 8bit

--0-1643493875-1144942806=:44309
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi Markus,

Markus Schaber <schabi@logix-tt.com> 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 may or may not appear
>> in a query.
>
>That's suboptimal.
>
>> Summrarizing what I have learned:
>> - I cannot use multicolumn indexes because I cannot group the column
>> logically.
>> - I cannot use funtional indexes
>> - I cannot use clustering.
>
>You still can have a set of partitioned multi-column indices,
>overlapping enough that every combination of columns is covered (or risk
>a sequential sub scan for the last two or three columns, this should not
>hurt too much if the first 17 columns were selective enough).
>
>The main problem with indices is that they also decrease write performance.
>
>If disk costs are not limited, it will make sense to have WAL, table and
>indices on different disks / raid arrays, to parallelize writes.
>
>Btw, I guess you have multiple, concurrent users?

Yes I do.

I have just made other tests with only the individual indexes and performance is much better than previously. Obviously there was an I/O problem during my initial test.

Something interesting though. If I use few columns in the query the results come very quickly and pg does a sequential scan. 

When it reachs some threshold (4 or 5 columns) pg switches to bitmap scans. It then takes an almost constant time (~ 2500 ms) not matter how many more columns I add to the where clause.

Interestingly enough, queries with many columns are less common. They also return less results and even many times no result at all.