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 72F3F9DC81C for ; Sat, 18 Mar 2006 01:29:55 -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 18170-10 for ; Sat, 18 Mar 2006 01:29:53 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id 69BA59DC820 for ; Sat, 18 Mar 2006 01:29:51 -0400 (AST) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id k2I5TgX5001110 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Fri, 17 Mar 2006 22:29:45 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id k2I5TgIl002378; Fri, 17 Mar 2006 22:29:42 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.4/8.13.4/Submit) id k2I5Tgp7002371; Fri, 17 Mar 2006 22:29:42 -0700 (MST) (envelope-from mfuhr) Date: Fri, 17 Mar 2006 22:29:41 -0700 From: Michael Fuhr To: Tom Lane Cc: Dan Harris , PostgreSQL Performance Subject: Re: Help optimizing a slow index scan Message-ID: <20060318052941.GA46733@winnie.fuhr.org> References: <4419DB50.10404@drivefaster.net> <441B1FC6.60001@drivefaster.net> <12823.1142656871@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <12823.1142656871@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.121 required=5 tests=[AWL=0.121] X-Spam-Score: 0.121 X-Spam-Level: X-Archive-Number: 200603/301 X-Sequence-Number: 17738 On Fri, Mar 17, 2006 at 11:41:11PM -0500, Tom Lane wrote: > 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. The OpenGIS Simple Features Specification[1] is a step in that direction, no? PostGIS[2], MySQL[3], and Oracle Spatial[4] implement to varying degrees. With PostGIS you do have to add non-standard operators to a query's predicate to benefit from GiST indexes on spatial columns, but the rest of the query can be straight out of the SQL and OGC standards. [1] http://www.opengeospatial.org/docs/99-049.pdf [2] http://www.postgis.org/ [3] http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html [4] http://www.oracle.com/technology/products/spatial/index.html -- Michael Fuhr