Re: cluster on conditional index?

On Wed, Aug 15, 2012 at 2:19 PM, Bosco Rama <postgres(at)boscorama(dot)com> wrote:
> On 08/15/12 14:05, Josh Berkus wrote:
>>
>>> That actually makes sense to me. Cluster the rows covered by that
>>> index, let the rest fall where they may. I'm typically only accessing
>>> the rows covered by that index, so I'd get the benefit of the cluster
>>> command but wouldn't have to spend cycles doing the cluster for rows I
>>> don't care about.
>>
>> Sure, that's a feature request though. And thinking about it, I'm
>> willing to bet that it's far harder to implement than it sounds.
>>
>> In the meantime, you could ad-hoc this by splitting the table into two
>> partitions and clustering one of the two partitions.
>
> Wouldn't creating a second index on the boolean itself and then clustering
> on that be much easier?

I would take an existing useful index, and build a new one on the same
columns but with is_deleted prepended.

That way, since you are going through the effort to rewrite the whole
table anyway, the ties are broken in a way that might be of further
use.

Once the CLUSTER is done, the index might even be useful enough to
keep around for use with queries, or even replace the original index
altogether.

Cheers,

Jeff