Search Postgresql Archives

Re: How to force planner to use GiST index?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



araza@xxxxxxxx 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux