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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
