From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Efraín Déctor
Sent: Monday, April 02, 2012 5:40 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Where to create an Index
I have this query:
SELECT ST_Distance(transform(geometryfromtext('POINT(-97.096667
18.858611)',4326),32614),C.point_geomutm)
AS DIST ,nombre FROM ciudad AS C ORDER BY DIST ASC limit 1;
It runs a secuential query on the table “ciudad” wich is a big table. I want to
create an index but I don’t know where, using a gist index on point_geomutm
does nothing and also creating one using nombre.
Thanks in advance.
If you are using version 9.1 the following feature seems relevant:
“Add nearest-neighbor (order-by-operator) searching to GiST indexes
<http://www.postgresql.org/docs/9.1/interactive/gist.html> (Teodor Sigaev, Tom
Lane)
This allows GiST indexes to quickly return the N closest values in a query with
LIMIT. For example
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
finds the ten places closest to a given target point.”
You would index “location” in the example or “point_geomutm” in your situation.
The fact you felt an index on “nombre” might help indicates you need to read up
more about how (and when) indexes work. Since you have no explicit (or
implicit) filter on “nombre” there is no possibility that such an index would
be used. The “ORDER BY” is an implicit filter on whatever columns are being
ordered (in this case the result of the ST_Distance function). Since you
wouldn’t generally index on a function call with user-supplied parameters the
basic query cannot use an index. The 9.1 feature noted above, however, does
some kind of magic to get somewhere between brute-force and pure-index
performance.
David J.
