From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dan Harris <fbsd@drivefaster.net>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Help optimizing a slow index scan
Date: Fri, 17 Mar 2006 23:41:11 -0500
Message-ID: <12823.1142656871@sss.pgh.pa.us> (raw)
In-Reply-To: <441B1FC6.60001@drivefaster.net>
References: <4419DB50.10404@drivefaster.net>
<b42b73150603170856j588b268bjd89ce769f4ac7a28@mail.gmail.com>
<441B1FC6.60001@drivefaster.net>
Dan Harris <fbsd@drivefaster.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, fbsd@drivefaster.net
Subject: Re: Help optimizing a slow index scan
In-Reply-To: <12823.1142656871@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox