Re: query plan, index scan cost

[Decibel! <decibel@xxxxxxxxxxx>]

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@xxxxxxxxxxx
Give your computer some brain candy! www.distributed.net Team #1828

Attachment: smime.p7s
Description: S/MIME cryptographic signature