Search Postgresql Archives

Re: Where to create an Index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.
 
Sent: Monday, April 02, 2012 5:04 PM
Subject: RE: Where to create an Index
 

 

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

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux