No, this is my own type and I want to use this. PostGIS is another option. Ale. -----Original Message----- From: Brent Wood [mailto:b.wood@xxxxxxxxxx] Sent: Wednesday, March 07, 2007 5:43 PM To: Ale Raza Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: [GENERAL] How to force planner to use GiST 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 >
