[postgis-users] speed up spatial queries (distance() or intersects() or ???)

[Daniel Faivre]

Thank you all very much, specially D Blasby.

I've tried your last idea, and obtain a little speed improvememnt by 
replacing my subselect by a big WKB multipolygon. Now, my request take 
about 85 seconds instaed of 94 seconds before. Here it is:

SELECT oid AS qoid FROM lg_annee WHERE lga_annee='2003' and 
lga_nat_cdn=2 AND lga_geom && 
box3d(GeomFromWKB('mybigmultipolygon_asbinary',-1)) AND 
distance(lga_geom, GeomFromWKB('('mybigmultipolygon_asbinary')',-1)) = 0;

And here is the analyze:

Seq Scan on lg_annee (cost=0.00..996.74 rows=35 width=4) (actual 
time=31.54..86748.73 rows=5150
loops=1)
Total runtime: 86760.72 msec

But now, I do not see any way to seriously increase speed ...

Other important informations: stats are active, i've updated 
geometry_stats(), and i've got indexes on: fields oid, lg_annee, 
lga_nat, and lga_geom.

Somebody told me to create an index on three columns in the same order 
than in my query, including the geometry_column. Is there any way to 
create such indexes ? (My first attempts results on pgsql alerts both 
for rtree or btree indexes on mixed column types, including a 
geometry_column). I would try to explore this way, if it is possible ...
Second question: is the using of WKB is the best way to re-introduice 
the result of a subselect in a query ? (I've tested WKT too and it was 
less efficient, but is there a third way ?)

Best regards,

-- 
Daniel Faivre
Ohceo - Camptocamp
04 79 75 28 19
180 rue du Genevois
73000 Chambe'ry le Haut

David Blasby wrote:

> Daniel Faivre wrote:
>
>> According to the "explain analyze", this part of the request seems 
>> not time-expensive (except if I misunderstand that and if these 
>> subrequest is done 5150 times ???).
>
>
> This is probably whats happening. Why dont you try a query with the 
> actual geometry instead of a subselect and see if its faster?
>
> dave