Re: Help optimizing a slow index scan

Dan Harris <fbsd(at)drivefaster(dot)net> writes:
> Furthermore, by doing so, I am tying my queries directly to
> "postgres-isms". One of the long term goals of this project is to be
> able to fairly transparently support any ANSI SQL-compliant back end
> with the same code base.

Unfortunately, there isn't any portable or standard (not exactly the
same thing ;-)) SQL functionality for dealing gracefully with
two-dimensional searches, which is what your lat/long queries are.
You should accept right now that you can have portability or you can
have good performance, not both.

Merlin's enthusiasm for row-comparison queries is understandable because
that fix definitely helped a common problem. But row comparison has
nothing to do with searches in two independent dimensions. Row
comparison basically makes it easier to exploit the natural behavior of
multicolumn btree indexes ... but a multicolumn btree index does not
efficiently support queries that involve separate range limitations on
each index column. (If you think about the index storage order you'll
see why: the answer entries are not contiguous in the index.)

To support two-dimensional searches you really need a non-btree index
structure, such as GIST. Since this isn't standard, demanding a
portable answer won't get you anywhere. (I don't mean to suggest that
Postgres is the only database that has such functionality, just that
the DBs that do have it don't agree on any common API.)

regards, tom lane