Re: How to force planner to use GiST index?

araza(at)esri(dot)com wrote:

Have you considered using PostGIS (www.postgis.org) to provide OGC
compliant
spatial data management for Postgresql, including projection support,
indexing &
a good selection of spatial query functions?

Cheers,

Brent Wood
> Hi,
>
> I have a GiST index on st_geometry type (a user defined type). It looks
> like index is not getting hit when I use some geometric operator. Here
> is the example of st_contains operator.
>
> EXPLAIN analyze Select count(a.objectid_1) as contains from sde.parcel_l
> a
> Where st_contains(st_geometry('polygon ((6221958 1949440, 6349378
> 1949440,
> 6349378 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3),
> a.shape) = 1;
>
> QUERY PLAN
> ------------------------------------------------------------------------
> -----------------------------------------
> Aggregate (cost=79132.24..79132.25 rows=1 width=4)
> (actual time=49614.399..49614.400 rows=1 loops=1)
> -> Seq Scan on parcel_l a (cost=0.00..79122.79 rows=3778 width=4)
> (actual time=2.343..49388.591 rows=184750 loops=1)
> Filter: (st_contains('ST_POLYGON'::st_geometry, shape) = 1)
> Total runtime: 49614.479 ms
>
> The time 49614 ms is too high for 184k rows. I have tried various
> configuration parameters as mentioned in section 17.6 (Query planning)
> of PostgreSQL 8.2.1 Documentation. For example setting
> Set enable_seqscan = off;
> Set random_page_cost = 10; etc.,
>
> Changing these parameters did not improve performance.
>
> If I call one of the operators (~) of GiST operator class, then it takes
> 1015 ms for 184k rows.
>
> EXPLAIN analyze Select count(a.objectid_1) as contains from sde.parcel_l
> a where
> (st_geometry('polygon ((6221958 1949440, 6349378 1949440, 6349378
> 2033808, 6221958 2033808, 6221958 1949440))'::cstring,3) ~ a.shape) =
> 't';
> ------------------------------------------------------------------------
> ------------------------------------------
> Aggregate (cost=2827.78..2827.79 rows=1 width=4)
> (actual time=1015.025..1015.026 rows=1 loops=1)
> -> Bitmap Heap Scan on parcel_l a (cost=46.05..2825.89 rows=756
> width=4)
> (actual time=213.914..876.122 rows=180512 loops=1)
> Filter: ('ST_POLYGON'::st_geometry ~ shape)
> -> Bitmap Index Scan on parcel_l_ind (cost=0.00..46.05
> rows=756 width=0)
> (actual time=202.629..202.629 rows=180170 loops=1)
> Index Cond: ('ST_POLYGON'::st_geometry ~ shape)
> Total runtime: 1015.223 ms
>
> Here is information about table, type, index and rows in the table.
>
> pg=# SELECT relname, relkind, reltuples, relpages FROM pg_class
> WHERE relname LIKE 'parcel_l%';
> relname | relkind | reltuples | relpages
> --------------------+---------+-----------+----------
> parcel_l_pkey | i | 755653 | 1665
> parcel_l | r | 755653 | 67788
> parcel_l_ind | i | 755653 | 9582
> (3 rows)
>
>
> pg=# \d parcel_l
> Table "sde.parcel_l"
> olumn | Type | Modifiers
> ---------------+-----------------------------+-----------
> objectid_1 | integer | not null
> area | numeric(38,8) |
> .....
> fid_len | numeric(38,8) |
> shape | st_geometry |
> Indexes:
> "parcel_l_pkey" PRIMARY KEY, btree (objectid_1)
> "parcel_l_ind" gist (shape)
>
>
>
> How can I force or direct the planner to use the GiST index? Am I
> missing something?
>
> Thanks.
>
> Ale Raza.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>