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
therewas 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 (~
2500ms) 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
resultat all.