Re: Index over only uncommon values in table

On Tue, Jun 18, 2013 at 12:17 PM, Steven Schlansker <steven(at)likeness(dot)com>wrote:

> Hi everyone,
>
> I assume this is not easy with standard PG but I wanted to double check.
>
> I have a column that has a very uneven distribution of values. ~95% of
> the values will be the same, with some long tail of another few dozens of
> values.
>
> I want to have an index over this value. Queries that select the most
> common value will not use the index, because it is a overwhelming
> percentage of the table. This means that ~95% of the disk space and IOPS
> to maintain the index is "wasted".
>
> I cannot use a hardcoded partial index because:
> 1) The common value is not known at schema definition time, and may change
> (very slowly) over time.
>

I think this is going to turn into a game of whack-a-mole. There is going
to have to be some transition period during which all or most of the rows
need to be indexed. So that amount of space is going to have to be
available, and given that it is available, what advantage is there to using
it some of the time and not using it some of the time? You can't feasibly
use it for something else during the off periods, because then you will run
into emergency out of space situations.

> 2) JDBC uses prepared statements for everything, and the value to be
> selected is not known at statement prepare time, so any partial indices are
> ignored (this is a really really obnoxious behavior and makes partial
> indices almost useless combined with prepared statements, sadly…)
>

What version are you using? This has been improved in 9.2.0.

Cheers,

Jeff