[postgis-users] [GENERAL] How to improve this query?

[Stefan Keller]

More possibilities (just ideas) before update:

* VACUUM FULL and reindex after that. If not, your indexes became bloated.
* If you've got the RAM, just use more of it, 1 or 2 GB. You can do
this at runtime: SET maintenance_work_mem="180MB". If you can fit
whole tables into memory, reindexing should be fast.
* You may want to increase checkpoint_segments in postgresql.conf. You
can reload config settings without restarting database:
http://heatware.net/databases/postgresql-reload-config-without-restarting/

Yours, S.

2011/2/8 Stefan Keller <sfkeller at gmail.com>:
> "  Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD)"
> tells me that these two longish strings are compared first.
> Did you already try to index TABLE_C.STR_FIELD and TABLE_B.STR_FIELD
> as hash instead btree?
> Did you set autovacuum off during update? transaction level?
>
> Yours, S.
>
> 2011/2/7 Jorge Arévalo <jorge.arevalo at deimos-space.com>:
>> Hello,
>>
>> update TABLE_A set INT_FIELD = TABLE_B.INT_FIELD from TABLE_C, TABLE_B
>> where TABLE_B.STR_FIELD = TABLE_C.STR_FIELD and TABLE_A.INT_FIELD2 >=
>> TABLE_B.min and TABLE_A.INT_FIELD2 <= TABLE_B.max and
>> st_intersects(TABLE_A.wkb_geometry, TABLE_C.the_geom);
>>
>> Tables description:
>>
>> ++++++++++++ TABLE A
>>
>> CREATE TABLE TABLE_A
>> (
>>  ogc_fid serial NOT NULL,
>>  wkb_geometry geometry,
>>  INT_FIELD2 integer,
>>  INT_FIELD integer NOT NULL DEFAULT 0,
>>  CONSTRAINT TABLE_A_pk PRIMARY KEY (ogc_fid),
>>  CONSTRAINT enforce_dims_wkb_geometry CHECK (st_ndims(wkb_geometry) = 2),
>>  CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = 23030)
>> )
>> WITH (
>>  OIDS=FALSE
>> );
>>
>> CREATE INDEX TABLE_A_geom_idx
>>  ON TABLE_A
>>  USING gist
>>  (wkb_geometry);
>>
>> CREATE INDEX TABLE_A_INT_FIELD2
>>  ON TABLE_A
>>  USING btree
>>  (INT_FIELD2);
>>
>> +++++++++++++++++++
>>
>>
>> ++++++++++++ TABLE B
>>
>> CREATE TABLE TABLE_B
>> (
>>  STR_FIELD character(50) DEFAULT NULL::bpchar,
>>  min integer NOT NULL DEFAULT 0,
>>  max integer NOT NULL DEFAULT 0,
>>  INT_FIELD integer NOT NULL DEFAULT 0,
>>  oid integer NOT NULL DEFAULT 0,
>>  CONSTRAINT TABLE_B_pk PRIMARY KEY (oid)
>> )
>> WITH (
>>  OIDS=FALSE
>> );
>>
>> CREATE INDEX TABLE_B_idx
>>  ON TABLE_B
>>  USING btree
>>  (STR_FIELD);
>>
>> CREATE INDEX TABLE_B_max_idx
>>  ON TABLE_B
>>  USING btree
>>  (max);
>>
>> CREATE INDEX TABLE_B_min_idx
>>  ON TABLE_B
>>  USING btree
>>  (min);
>>
>> +++++++++++++++++++
>>
>>
>>
>> ++++++++++++ TABLE C
>>
>> CREATE TABLE TABLE_C
>> (
>>  the_geom geometry,
>>  STR_FIELD character(50)
>> )
>> WITH (
>>  OIDS=FALSE
>> );
>>
>> CREATE INDEX TABLE_C_index
>>  ON TABLE_C
>>  USING gist
>>  (the_geom);
>>
>> CREATE INDEX TABLE_C_string_idx
>>  ON TABLE_C
>>  USING btree
>>  (STR_FIELD);
>>
>> +++++++++++++++++++
>>
>>
>> Tables data:
>>
>> - TABLE_A: 896888 entries. The geometries are single polygons
>> (squares, actually), coordinates are floating point numbers
>> - TABLE_B: 88 entries.
>> - TABLE C: 69352 entries. Geometries are single polygons too, but much
>> bigger than the polygons from TABLE_A.
>>
>> As you can see in the query, I'm interested in the polygons of TABLE_A
>> that intersects the big polygons in TABLE_C.
>>
>> Query plan (explain <query> output):
>>
>> "Hash Join  (cost=3.98..986808.75 rows=209049 width=497)"
>> "  Hash Cond: (TABLE_C.STR_FIELD = TABLE_B.STR_FIELD)"
>> "  Join Filter: ((TABLE_A.INT_FIELD2 >= TABLE_B.min) AND
>> (TABLE_A.INT_FIELD2 <= TABLE_B.max))"
>> "  ->  Nested Loop  (cost=0.00..955055.47 rows=470360 width=543)"
>> "        Join Filter: _st_intersects(TABLE_A.wkb_geometry, TABLE_C.the_geom)"
>> "        ->  Index Scan using TABLE_C_string_idx on TABLE_C
>> (cost=0.00..8044.56 rows=69352 width=1517)"
>> "        ->  Index Scan using TABLE_A_geom_idx on TABLE_A
>> (cost=0.00..12.61 rows=4 width=493)"
>> "              Index Cond: (TABLE_A.wkb_geometry && TABLE_C.the_geom)"
>> "  ->  Hash  (cost=2.88..2.88 rows=88 width=63)"
>> "        ->  Seq Scan on TABLE_B  (cost=0.00..2.88 rows=88 width=63)"
>>
>>
>> With that information, how could I make the update faster? It takes
>> more than 24hours to finish.
>>
>>
>> Many thanks in advance
>>
>>
>> Best regards,
>>
>> --
>> Jorge Arévalo
>> Internet & Mobilty Division, DEIMOS
>> jorge.arevalo at deimos-space.com
>> http://es.linkedin.com/in/jorgearevalo80
>> http://mobility.grupodeimos.com/
>> http://www.twitter.com/jorgeas80
>> http://gis4free.wordpress.com
>> http://geohash.org/ezjqgrgzz0g
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general at postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>