Re: Where to create an Index

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Efraín Déctor
Sent: Monday, April 02, 2012 5:40 PM
To: pgsql-general(at)postgresql(dot)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.