Re: How to force planner to use GiST index? - Mailing list pgsql-general

araza@esri.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
>