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