2011/10/8 René Fournier <renefourn...@gmail.com>
>
> Thanks. Based on some further reading, this is what I came up with, in
> order to hopefully use the GiST index to greatest benefit:
>
> gc3=# SELECT datasetnam, r_hnumf, r_hnuml, r_stname_c, r_placenam,
> ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom)
> AS distance
> gc3-# FROM nrn_ab_8_0_roadseg
> gc3-# WHERE the_geom && SetSRID('BOX3D(-114.1 49.9,-113.9
> 51.1)'::box3d,4269)
> gc3-# ORDER BY distance ASC LIMIT 5;
> datasetnam | r_hnumf | r_hnuml | r_stname_c | r_placenam |
> distance
>
> ------------+---------+---------+----------------------+------------+----------------------
> Alberta | 407 | 459 | 19 Avenue North-east | Calgary |
> 5.74515867479735e-05
> Alberta | 2004 | 2004 | 4 Street North-east | Calgary |
> 0.000663366090673065
> Alberta | 0 | 0 | 4 Street North-east | Calgary |
> 0.000667603774783403
> Alberta | 425 | 425 | 18 Avenue North-east | Calgary |
> 0.000835708003512673
> Alberta | 407 | 449 | 20 Avenue North-east | Calgary |
> 0.000981910679856406
> (5 rows)
>
> gc3=# EXPLAIN SELECT datasetnam, r_hnumf, r_hnuml, r_stname_c, r_placenam,
> ST_Distance(ST_GeomFromText('POINT(-114.053205 51.069644)',4269),the_geom)
> AS distance
> gc3-# FROM nrn_ab_8_0_roadseg
> gc3-# WHERE the_geom && SetSRID('BOX3D(-114.1 49.9,-113.9
> 51.1)'::box3d,4269)
> gc3-# ORDER BY distance ASC LIMIT 5;
>
> QUERY PLAN
>
>
>
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=33632.15..33632.16 rows=5 width=480)
> -> Sort (cost=33632.15..33693.00 rows=24341 width=480)
> Sort Key:
> (st_distance('0101000020AD100000F5BEF1B567835CC06A2E3718EA884940'::geometry,
> the_geom))
> -> Bitmap Heap Scan on nrn_ab_8_0_roadseg (cost=812.99..33227.85
> rows=24341 width=480)
> Recheck Cond: (the_geom &&
> '0103000020AD10000001000000050000006666666666865CC03333333333F348406666666666865CC0CDCCCCCCCC8C49409A99999999795CC0CDCCCCCCCC8C49409A99999999795CC03333333333F348406666666666865CC03333333333F34840'::geometry)
> -> Bitmap Index Scan on nrn_ab_8_0_roadseg_the_geom_gist
> (cost=0.00..806.91 rows=24341 width=0)
> Index Cond: (the_geom &&
> '0103000020AD10000001000000050000006666666666865CC03333333333F348406666666666865CC0CDCCCCCCCC8C49409A99999999795CC0CDCCCCCCCC8C49409A99999999795CC03333333333F348406666666666865CC03333333333F34840'::geometry)
> (7 rows)
>
>
> Does this appear optimal to you?
>
>
>
I think it's closer to optimal. The real question is: is this fast enough
for your application?
Can you show EXPLAIN (ANALYZE on,BUFFERS on) result?
