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
LIMIT1;
>
> 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
FROMroad 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 <kleptog@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