Re: advanced index (descending and table-presorted descending)

On Nov 22, 2006, at 1:51 PM, John D. Burger wrote:
>> However, Cluster might work for you, but you need to re-cluster after
>> every updates or inserts, so it will probably be fine for static
>> data.
>
> This reminds me of a (somewhat off-topic) question I have had:
>
> I have a static database, and most of the tables are 100%
> correlated with one column or another (because I build them that
> way, or due to clustering).  In some cases I join two tables on one
> of these perfectly correlated columns, and so the planner wants to
> sort the two on that column.  Of course, this is unnecessary, and
> for large tables, the sorts get spilled to disk (I suppose) and can
> take a while.  Is there any way to convince the planner that the
> sorts are unnecessary, and it can just zip the two tables together
> as is?
>
> This is under PG 7.4, by the way.  Any comments welcome.

The problem is that there's no way to actually guarantee that the
table is already pre-sorted. If we had the concept of read-only
tables, and you clustered one of them, it could be made to work...
--
Jim Nasby                               jim.nasby@enterprisedb.com
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)