I have just noticed that the optimizer's indexing code is doing
something that looks pretty bogus for non-btree-type indexes.
In optimizer/util/plancat.c, there's a routine index_info() that
pulls the necessary information about an index out of the catalogs.
It is picking up whatever operator is listed as "strategy 1" for the
index opclass of each index. Later on, the optimizer assumes that this
operator represents the sort order induced by an indexscan over the
given index. That's fine for btree, where strategy operator 1 is "<".
But for rtree and hash it seems to yield some rather odd choices:
<< |box_left |rtree
<< |box_left |rtree
<< |poly_left |rtree
<< |circle_left|rtree
= |texteq |hash
= |int4eq |hash
= |int2eq |hash
= |oideq |hash
= |oid8eq |hash
= |float4eq |hash
= |nameeq |hash
= |chareq |hash
= |float8eq |hash
= |datetime_eq|hash
= |time_eq |hash
= |timespan_eq|hash
= |date_eq |hash
= |int8eq |hash
= |macaddr_eq |hash
= |varchareq |hash
= |network_eq |hash
= |bpchareq |hash
= |network_eq |hash
I do not know whether an indexscan of an rtree can be counted on
to yield the values in "<<" order ... but I do think it's pretty
strange to consider "=" as the sort order of a hash index!
Shouldn't we fix this somehow? The cleanest solution that comes
to mind is to add a column to pg_am, wherein we would put the
strategy number of the operator that represents the sort ordering
of the index, or zero if the index has no useful sort order (like
hash). Any comments on this idea? Does it work for GIST indexes?
Also, does anyone know whether "<<" really is the sort order of
an rtree? A couple of cursory tests didn't disprove it, but
I'm not confident about it...
regards, tom lane
