[postgis-users] Postgresql not using Spatial Index

[Paul Ramsey]

You could turn off the seqscans and see just how wrong PgSQL is in its 
decision. 5% of the table seems like it is on the cusp. I am surprised 
it takes 20 seconds to scan 100K rows, frankly.  Are they very very wide?

P.

dblasby at openplans.org wrote:

> I'm trying to get postgresql to use the spatial index, but I'm having an
> awful time.
> 
> For example, in the included query postgresql wants to use a sequential
> scan instead of an index scan.  The table has just under 100,000 rows,
> and the estimate (and actual) number of returned rows is 4,400 (5%). 
> It seems a bit excessive to do a sequential scan for 5% of the table!
> 
> I changed my random_page_cost from 4 to 2 to try to get it to prefer the
> index, but its still no go!
> 
> This sure seems like a problem in the cost analysis.
> 
> dave
> ps. the data is all the TIGER roads for NY.
> ps. I've "vacuum analyse"-ed the table and the database.
> ps. It takes almost 20seconds to do a seq scan on the table (the numbers
> below are when the table is already in the disk cache)
> 
> -------------------
> query plans (a) normal (b) with enable_seqscan =off
> -------------------
> 
> new_tiger=# vacuum analyse finalroads;
> VACUUM
> 
> new_tiger=# explain analyse SELECT "oid",
> bytea(AsBinary(force_2d("wkb_geometry"), 'XDR')) FROM "finalroads"
> WHERE "wkb_
> geometry" && GeometryFromText('POLYGON ((-74.16533714779995
> 40.55016355826745, -74.16533714779995 40.6032587900393, -74.
> 05901394617682 40.6032587900393, -74.05901394617682 40.55016355826745,
> -74.16533714779995 40.55016355826745))', 1);
>                                                                         
>                                         QUERY P
> LAN
> ------------------------------------------------------------------------------------------------------------------------
> --------------------------------------------------------------------------------------------------------------------
>  Seq Scan on finalroads  (cost=0.00..4323.96 rows=4394 width=66) (actual
> time=50.000..301.000 rows=4449 loops=1)
>    Filter: (wkb_geometry &&
> '010300002001000000010000000500000057A742E2948A52C0D71A6DC26B46444057A742E2948A52C0251F83953
> 74D444097356EE2C68352C0251F8395374D444097356EE2C68352C0D71A6DC26B46444057A742E2948A52C0D71A6DC26B464440'::geometry)
>  Total runtime: 301.000 ms
> (3 rows)
> 
> 
> new_tiger=# set enable_seqscan =off;
> SET
> new_tiger=# explain analyse SELECT "oid",
> bytea(AsBinary(force_2d("wkb_geometry"), 'XDR')) FROM "finalroads"
> WHERE "wkb_
> geometry" && GeometryFromText('POLYGON ((-74.16533714779995
> 40.55016355826745, -74.16533714779995 40.6032587900393, -74.
> 05901394617682 40.6032587900393, -74.05901394617682 40.55016355826745,
> -74.16533714779995 40.55016355826745))', 1);
>                                                                         
>                                           QUERY
>  PLAN
> ------------------------------------------------------------------------------------------------------------------------
> ------------------------------------------------------------------------------------------------------------------------
>  Index Scan using finalroads_idx on finalroads  (cost=0.00..6508.77
> rows=4394 width=66) (actual time=0.000..40.000 rows=
> 4449 loops=1)
>    Index Cond: (wkb_geometry &&
> '010300002001000000010000000500000057A742E2948A52C0D71A6DC26B46444057A742E2948A52C0251F8
> 395374D444097356EE2C68352C0251F8395374D444097356EE2C68352C0D71A6DC26B46444057A742E2948A52C0D71A6DC26B464440'::geometry)
>  Total runtime: 60.000 ms
> (3 rows)
> 
> new_tiger=# select count(*) from finalroads;
>  count
> -------
>  91839
> (1 row)
> 
> ----------------------------------------------------------
> This mail sent through IMP: https://webmail.limegroup.com/
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users