Fascinating.
On Fri, Jul 04, 2014 at 10:47:06AM +0800, gotoschool6g wrote:
> slow query(8531 ms):
> SELECT ST_Distance_Sphere(shape,ST_GeomFromText('POINT(116.41386186784513
> 40.12211338311868)')) FROM road order by id LIMIT 1;
>
> explain output:
> "Limit (cost=4653.48..4653.48 rows=1 width=3612)"
> " -> Sort (cost=4653.48..4683.06 rows=11832 width=3612)"
> " Sort Key: id"
> " -> Seq Scan on road (cost=0.00..4594.32 rows=11832 width=3612)"
>
> fast query(16ms):
> select ST_Distance_Sphere(shape,ST_GeomFromText('POINT(116.41386186784513
> 40.12211338311868)')) from (SELECT shape FROM road order by id LIMIT 1) a
>
> explain output:
> "Subquery Scan on a (cost=1695.48..1695.74 rows=1 width=3608)"
> " -> Limit (cost=1695.48..1695.48 rows=1 width=3612)"
> " -> Sort (cost=1695.48..1725.06 rows=11832 width=3612)"
> " Sort Key: road.id"
> " -> Seq Scan on road (cost=0.00..1636.32 rows=11832
> width=3612)"So Postgres knows perfectly well that it's expensive, it just doesn't appear to understand it has the option of moving the calculation above the limit. In this case though, it seems an index on road(id) would make it instant in any case. Have a nice day, -- Martijn van Oosterhout <klep...@svana.org> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
signature.asc
Description: Digital signature
