|
Hello. Thanks for your answer.
I have the following Index:
CREATE INDEX index_pointgeomutm_ciudad
ON ciudad
USING btree_gist
(point_geomutm);
But the query is not using it.
From:
pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx]
On Behalf Of Efraín Déctor 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
(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. |
