X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5D1929DCBA4 for ; Sat, 18 Mar 2006 00:41:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15407-07 for ; Sat, 18 Mar 2006 00:41:13 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id EC82E9DCA89 for ; Sat, 18 Mar 2006 00:41:12 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k2I4fBOs012824; Fri, 17 Mar 2006 23:41:11 -0500 (EST) To: Dan Harris cc: PostgreSQL Performance Subject: Re: Help optimizing a slow index scan In-reply-to: <441B1FC6.60001@drivefaster.net> References: <4419DB50.10404@drivefaster.net> <441B1FC6.60001@drivefaster.net> Comments: In-reply-to Dan Harris message dated "Fri, 17 Mar 2006 13:44:54 -0700" Date: Fri, 17 Mar 2006 23:41:11 -0500 Message-ID: <12823.1142656871@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.113 required=5 tests=[AWL=0.113] X-Spam-Score: 0.113 X-Spam-Level: X-Archive-Number: 200603/300 X-Sequence-Number: 17737 Dan Harris 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