Re: 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 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?

Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org