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.
