[postgis-devel] Gist index not used with Multipolygons

[James DeMichele]

I have created 2 tables, one with a POLYGON geometry column, and one
with a MULTIPOLYGON geometry column. They both have the same data,
meaning that the POLYGON table has simple POLYGONs and the MULTIPOLYGON
table has the collect(polygon) grouped by an id. Here are the 2 table
structures:

 

----------------------------

CREATE TABLE state_shapes

(state_shape_id bigserial NOT NULL,

  state_id bigint NOT NULL);

SELECT
AddGeometryColumn('','state_shapes','polygon','4326','POLYGON',2);

CREATE INDEX state_poly_idx ON state_shapes USING gist (polygon);

 

CREATE TABLE state_shapes_2

(state_shape_id bigserial NOT NULL,

  state_id bigint NOT NULL);

SELECT
AddGeometryColumn('','state_shapes_2','polygon','4326','MULTIPOLYGON',2)
;

CREATE INDEX state_poly_idx_2 ON state_shapes_2 USING gist (polygon);

-----------------------------

 

Then, I run a VACUUM ANALYZE on both of these tables. Unfortunately, now
when I run a query against these tables, ONLY the table with the POLYGON
geometry type is using the Gist Index:

 

FOR STATE_SHAPES:

explain analyze

SELECT s.name, ss.polygon 

FROM states s, state_shapes ss 

WHERE s.state_id = ss.state_id 

AND ss.polygon && setsrid('BOX3D(-152.495155 57.894353, -152.295155
57.694353)'::box3d, 4326)

AND within(GeomFromText( 'POINT(-152.395155 57.794353)', 4326),
ss.polygon);

 

-[ RECORD 1
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN | Hash Join  (cost=8.28..11.12 rows=1 width=66618) (actual
time=1.365..1.418 rows=1 loops=1)

-[ RECORD 2
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN |   Hash Cond: (s.state_id = ss.state_id)

-[ RECORD 3
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN |   ->  Seq Scan on states s  (cost=0.00..2.60 rows=60
width=21) (actual time=0.008..0.039 rows=60 loops=1)

-[ RECORD 4
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN |   ->  Hash  (cost=8.27..8.27 rows=1 width=66613) (actual
time=1.343..1.343 rows=1 loops=1)

-[ RECORD 5
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN |         ->  Index Scan using state_poly_idx on state_shapes
ss  (cost=0.00..8.27 rows=1 width=66613) (actual time=1.33

2..1.340 rows=1 loops=1)

-[ RECORD 6
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN |               Index Cond: (polygon &&
'0103000020E610000001000000050000006E6E4C4FD80F63C09370218FE0D84C406E6E4
C4FD80F6

3C0000000207AF24C400708E6E8710963C0000000207AF24C400708E6E8710963C093702
18FE0D84C406E6E4C4FD80F63C09370218FE0D84C40'::geometry)

-[ RECORD 7
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN |               Filter: ((polygon &&
'0103000020E610000001000000050000006E6E4C4FD80F63C09370218FE0D84C406E6E4
C4FD80F63C0

000000207AF24C400708E6E8710963C0000000207AF24C400708E6E8710963C09370218F
E0D84C406E6E4C4FD80F63C09370218FE0D84C40'::geometry) AND wi

thin('0101000020E61000003A3B191CA50C63C0603DEE5BADE54C40'::geometry,
polygon))

-[ RECORD 8
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN | Total runtime: 1.459 ms

 

-----------------------------------------------------------------------

FOR STATE_SHAPES_2:

explain analyze

SELECT s.name, ss.polygon 

FROM states s, state_shapes_2 ss 

WHERE s.state_id = ss.state_id 

AND ss.polygon && setsrid('BOX3D(-152.495155 57.894353, -152.295155
57.694353)'::box3d, 4326)

AND within(GeomFromText( 'POINT(-152.395155 57.794353)', 4326),
ss.polygon);

 

-[ RECORD 1
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN | Hash Join  (cost=1.81..4.64 rows=1 width=165866) (actual
time=61.708..61.762 rows=1 loops=1)

-[ RECORD 2
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN |   Hash Cond: (s.state_id = ss.state_id)

-[ RECORD 3
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN |   ->  Seq Scan on states s  (cost=0.00..2.60 rows=60
width=21) (actual time=0.008..0.041 rows=60 loops=1)

-[ RECORD 4
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN |   ->  Hash  (cost=1.79..1.79 rows=1 width=165861) (actual
time=61.683..61.683 rows=1 loops=1)

-[ RECORD 5
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN |         ->  Seq Scan on state_shapes_2 ss  (cost=0.00..1.79
rows=1 width=165861) (actual time=59.926..61.673 rows=1 lo

ops=1)

-[ RECORD 6
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN |               Filter: ((polygon &&
'0103000020E610000001000000050000006E6E4C4FD80F63C09370218FE0D84C406E6E4
C4FD80F63C0

000000207AF24C400708E6E8710963C0000000207AF24C400708E6E8710963C09370218F
E0D84C406E6E4C4FD80F63C09370218FE0D84C40'::geometry) AND wi

thin('0101000020E61000003A3B191CA50C63C0603DEE5BADE54C40'::geometry,
polygon))

-[ RECORD 7
]-----------------------------------------------------------------------
-----------------------------------------------

------------------------------------------------------------------------
-----------------------------------------------------------

------------------------------------------------------------------------
------

QUERY PLAN | Total runtime: 61.825 ms

 

---------------------------------------------------------

 

Why does the Multipolygon table NOT make use of the index?

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20071011/053a5e44/attachment.html>