[postgis-devel] Gist index not used with Multipolygons

Mark Leslie mark.leslie at lisasoft.com
Thu Oct 11 17:38:19 PDT 2007


It may be as simple as having more polygons than multipolygons.  I don't 
have a complete understanding of PostgreSQLs optimizer, but if it 
decides that there are few enough entries in the table that a sequence 
scan will be faster, it will take that approach.  Try running the query 
on another table built using the multi(polygon) command.  This will 
produce a multipolygon for each polygon, and should result in the same 
execution plan.
Mark Leslie

James DeMichele wrote:
> 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?
>
>
>   
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>   




More information about the postgis-devel mailing list