Re: Help optimizing a slow index scan

From: Oleg Bartunov
Subject: Re: Help optimizing a slow index scan
Date: ,
Msg-id: Pine.GSO.4.63.0603181147240.22593@ra.sai.msu.su
(view: Whole thread, Raw)
In response to: Re: Help optimizing a slow index scan  (Michael Fuhr)
Responses: Re: Help optimizing a slow index scan  (Bruno Wolff III)
List: pgsql-performance

Tree view

Help optimizing a slow index scan  (Dan Harris, )
 Re: Help optimizing a slow index scan  (Dan Harris, )
 Re: Help optimizing a slow index scan  (Dan Harris, )
  Re: Help optimizing a slow index scan  (Dan Harris, )
   Re: Help optimizing a slow index scan  (Evgeny Gridasov, )
    Re: Help optimizing a slow index scan  (Oleg Bartunov, )
  Re: Help optimizing a slow index scan  (Bruno Wolff III, )
   Re: Help optimizing a slow index scan  ("Merlin Moncure", )
 Re: Help optimizing a slow index scan  ("Merlin Moncure", )
  Re: Help optimizing a slow index scan  (Dan Harris, )
   Re: Help optimizing a slow index scan  ("Merlin Moncure", )
   Re: Help optimizing a slow index scan  (Tom Lane, )
    Re: Help optimizing a slow index scan  (Michael Fuhr, )
     Re: Help optimizing a slow index scan  (Oleg Bartunov, )
      Re: Help optimizing a slow index scan  (Bruno Wolff III, )

I may be wrong but we in astronomy have several sky indexing schemes, which
allows to effectively use classical btree index. See
http://www.sai.msu.su/~megera/oddmuse/index.cgi/SkyPixelization
for details. Sergei Koposov has developed Q3C contrib module for
PostgreSQL 8.1+ and we use it with billiard size astronomical catalogs.


     Oleg

On Fri, 17 Mar 2006, Michael Fuhr wrote:

> 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
>
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: , http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83


pgsql-performance by date:

From: Antoine
Date:
Subject: n00b autovacuum question
From: Bruno Wolff III
Date:
Subject: Re: Help optimizing a slow index scan