pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery plan, index scan cost
2+ messages / 2 participants
[nested] [flat]
* query plan, index scan cost
@ 2008-07-18 10:28 Stefan Zweig <stefanzweig1881@web.de>
0 siblings, 1 reply; 2+ messages in thread
From: Stefan Zweig @ 2008-07-18 10:28 UTC (permalink / raw)
To: pgsql-performance
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 the following 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.332* ms
running the next query which is only slightly different and has one instead of two and conditions 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: *109*ms
so in both querys there are and conditions. there are two and conditions in the first query and one and condition in the second query. unfortunately i am not an expert in reading the postgre 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. the second query runs hundred times faster then first one which surprising to me.
any ideas?
regards, stefan
_________________________________________________________________________
In 5 Schritten zur eigenen Homepage. Jetzt Domain sichern und gestalten!
Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: query plan, index scan cost
@ 2008-08-13 14:50 Decibel! <decibel@decibel.org>
parent: Stefan Zweig <stefanzweig1881@web.de>
0 siblings, 0 replies; 2+ messages in thread
From: Decibel! @ 2008-08-13 14:50 UTC (permalink / raw)
To: Stefan Zweig <stefanzweig1881@web.de>; +Cc: pgsql-performance
On Jul 18, 2008, at 5:28 AM, Stefan Zweig wrote:
> 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;
You might want to give up on the double-quotes... you'll have to use
them everywhere. It'd drive me nuts... :)
> 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)
<snip>
> Total runtime: *13.332* ms
>
>
> running the next query which is only slightly different and has one
> instead of two and conditions 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)
<snip>
> Total runtime: *109*ms
>
>
> so in both querys there are and conditions. there are two and
> conditions in the first query and one and condition in the second
> query. unfortunately i am not an expert in reading the postgre
> 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. the second query runs hundred times faster
> then first one which surprising to me.
The second index scan wasn't done in the second query because you
don't have the second IN clause. And it's actually the 1st query that
was faster, because it returned fewer rows (15k instead of 45k).
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-08-13 14:50 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-18 10:28 query plan, index scan cost Stefan Zweig <stefanzweig1881@web.de>
2008-08-13 14:50 ` Decibel! <decibel@decibel.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox