cluster on conditional index?

From: Doug Hunley <doug(dot)hunley(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: cluster on conditional index?
Date: 2012-08-14 15:27:18
Message-ID: CALxYTP4vNQ+_rA7eHS+-wCYs7GaQdyaFoJb7g_UzvBt1ou2Eng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

According to the docs on cluster:
if you tend to access some data more than others, and there is an
index that groups them together, you will benefit from using CLUSTER

however, this doesn't address the situation where you have a
conditional index. For example, we have certain large tables that have
a column called 'is_deleted'. It's a boolean, to indicate whether the
record is 'deleted' as far as the app is concerned. Since the app only
ever shows data where is_deleted is false, I created an index:
create index foo on bar where is_deleted is false;
and now I'm wondering if clustering on this index will bring the
benefits noted above or if I should rebuild my index w/o the where
clause to obtain the best 'improvement' from cluster.

Anyone know?

--
Douglas J Hunley (doug(dot)hunley(at)gmail(dot)com)
Twitter: @hunleyd Web:
douglasjhunley.com
G+: http://goo.gl/sajR3

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff Janes 2012-08-14 17:10:47 Re: cluster on conditional index?
Previous Message Jeff Janes 2012-08-14 04:14:42 Re: Index Bloat Problem