Re: Minmax indexes

On 2013-09-16 11:19:19 +0100, Chris Travers wrote:
>
>
> > On 16 September 2013 at 11:03 Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>
> > wrote:
>
> >
> > Something like this seems completely sensible to me:
> >
> > create index i_accounts on accounts using minmax (ts) where valid = true;
> >
> > The situation where that would be useful is if 'valid' accounts are
> > fairly well clustered, but invalid ones are scattered all over the
> > table. The minimum and maximum stoed in the index would only concern
> > valid accounts.

Yes, I wondered the same myself.

> Here's one that occurs to me:
>
> CREATE INDEX i_billing_id_mm ON billing(id) WHERE paid_in_full IS NOT TRUE;
>
> Note that this would be a frequently moving target and over years of billing,
> the subset would be quite small compared to the full system (imagine, say, 50k
> rows out of 20M).

In that case you'd just use a normal btree index, no?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services