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

["Jim Nasby" <jim(dot)nasby(at)enterprisedb(dot)com>]

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(dot)nasby(at)enterprisedb(dot)com
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)