From: Oscar Picasso <oscgoogle@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Better index stategy for many fields with few values
Date: Thu, 13 Apr 2006 08:40:06 -0700 (PDT)
Message-ID: <20060413154006.46851.qmail@web54710.mail.yahoo.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: oscgoogle@yahoo.com
Subject: Re: Better index stategy for many fields with few values
In-Reply-To: <20060413154006.46851.qmail@web54710.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox