[postgis-users] problem with query planner

[Stefan Zweig]

hi list,

i have a problem with time consuming query. first of all my table structure:

CREATE TABLE nw_tla_2008_4_deu
(
  "ID" bigint NOT NULL,
  "NET2CLASS" smallint,
  "FOW" smallint,
  CONSTRAINT nw_tla_2008_4_deu_pkey PRIMARY KEY ("ID"),
) 
WITHOUT OIDS;

CREATE INDEX nw_tla_2008_4_deu_fow_idx
  ON nw_tla_2008_4_deu
  USING btree
  ("FOW");

CREATE INDEX nw_tla_2008_4_deu_net2class_idx
  ON nw_tla_2008_4_deu
  USING btree
  ("NET2CLASS");

CREATE INDEX nw_tla_2008_4_deu_the_geom_gist
  ON nw_tla_2008_4_deu
  USING gist
  (the_geom gist_geometry_ops);
ALTER TABLE nw_tla_2008_4_deu CLUSTER ON nw_tla_2008_4_deu_the_geom_gist;


when i run this query with explain analyze i get the following result:

EXPLAIN 
ANALYZE 

SELECT 
nw."ID" AS id

FROM 
nw_tla_2008_4_deu AS nw 

WHERE  
expand(st_pointfromtext('POINT(13.7328934 51.049476)',4326), 0.24769615911118054) && nw.the_geom 
AND nw."FOW" IN (1,2,3,4,10,17)
AND nw."NET2CLASS" IN (0,1,2,3)

Bitmap Heap Scan on nw_tla_2008_4_deu nw  (cost=35375.52..77994.15 rows=11196 width=8) (actual time=13307.830..13368.969 rows=15425 loops=1)

  Recheck Cond: ("NET2CLASS" = ANY ('{0,1,2,3}'::integer[]))

  Filter: (('0103000020E61000000100000005000000000000C06BF82A40000000A0A0664940000000C06BF82A40000000C009A64940000000E00FF62B40000000C009A64940000000E00FF62B40000000A0A0664940000000C06BF82A40000000A0A0664940'::geometry && the_geom) AND ("FOW" = ANY ('{1,2,3,4,10,17}'::integer[])))

  ->  BitmapAnd  (cost=35375.52..35375.52 rows=12614 width=0) (actual time=13307.710..13307.710 rows=0 loops=1)

        ->  Bitmap Index Scan on nw_tla_2008_4_deu_the_geom_gist  (cost=0.00..1759.12 rows=55052 width=0) (actual time=22.452..22.452 rows=52840 loops=1)

              Index Cond: ('0103000020E61000000100000005000000000000C06BF82A40000000A0A0664940000000C06BF82A40000000C009A64940000000E00FF62B40000000C009A64940000000E00FF62B40000000A0A0664940000000C06BF82A40000000A0A0664940'::geometry && the_geom)

        ->  Bitmap Index Scan on nw_tla_2008_4_deu_net2class_idx  (cost=0.00..33610.55 rows=1864620 width=0) (actual time=13284.121..13284.121 rows=2021814 loops=1)

              Index Cond: ("NET2CLASS" = ANY ('{0,1,2,3}'::integer[]))

Total runtime: 13.372 ms


running this query which is only slightly different leads to the following result:

EXPLAIN 
ANALYZE 

SELECT 
nw."ID" AS id

FROM 
nw_tla_2008_4_deu AS nw 

WHERE  
expand(st_pointfromtext('POINT(13.7328934 51.049476)',4326), 0.24769615911118054) && nw.the_geom 
AND nw."FOW" IN (1,2,3,4,10,17)


Bitmap Heap Scan on nw_tla_2008_4_deu nw  (cost=1771.34..146161.54 rows=48864 width=8) (actual time=23.285..99.493 rows=47723 loops=1)

  Filter: (('0103000020E61000000100000005000000000000C06BF82A40000000A0A0664940000000C06BF82A40000000C009A64940000000E00FF62B40000000C009A64940000000E00FF62B40000000A0A0664940000000C06BF82A40000000A0A0664940'::geometry && the_geom) AND ("FOW" = ANY ('{1,2,3,4,10,17}'::integer[])))

  ->  Bitmap Index Scan on nw_tla_2008_4_deu_the_geom_gist  (cost=0.00..1759.12 rows=55052 width=0) (actual time=22.491..22.491 rows=52840 loops=1)

        Index Cond: ('0103000020E61000000100000005000000000000C06BF82A40000000A0A0664940000000C06BF82A40000000C009A64940000000E00FF62B40000000C009A64940000000E00FF62B40000000A0A0664940000000C06BF82A40000000A0A0664940'::geometry && the_geom)

Total runtime: 109ms


so in both querys there are and conditions. there two and conditions in the first query and one and condition in the second query. unfortunately i am not an expert in reading the query plan. basically i am wondering why in the first query a second index scan is done whereas in the second query the second index scan is not done.

any ideas or should i rather point this to the postgresql forum?

regards, stefan
____________________________________________________________________
Ihre Messenger, Communities und E-Mails jetzt in einem Programm!
WEB.DE MultiMessenger http://www.produkte.web.de/messenger/?did=3071