Re: 7.4.7: strange planner decision - Mailing list pgsql-general

From Roman Neuhauser
Subject Re: 7.4.7: strange planner decision
Date
Msg-id 20050713135809.GB63397@isis.sigpipe.cz
Whole thread Raw
In response to Re: 7.4.7: strange planner decision  (Richard Huxton <dev@archonet.com>)
Responses Re: 7.4.7: strange planner decision
Re: 7.4.7: strange planner decision
List pgsql-general
# dev@archonet.com / 2005-07-13 14:09:34 +0100:
> Roman Neuhauser wrote:
> >    callrec32=# \d fix.files
> >                  Table "fix.files"
> >     Column |          Type          | Modifiers
> >    --------+------------------------+-----------
> >     dir    | character varying(255) |
> >     base   | character varying(255) |
> >    Indexes:
> >        "base_storename_idx" btree (base, ((((dir)::text || '/'::text) ||
> >        (base)::text)))
> >        "ff_baseonly_idx" btree (base)
> >        "ff_storename_idx" btree (((((dir)::text || '/'::text) ||
> >        (base)::text)))
> >
> >    callrec32=# explain select fd.base from fix.dups fd join fix.files ff
> >    using (base);
> >                                     QUERY PLAN
> >    ----------------------------------------------------------------------------
> >     Hash Join  (cost=5340.00..292675.06 rows=176161 width=44)
> >       Hash Cond: (("outer".base)::text = ("inner".base)::text)
> >       ->  Seq Scan on files ff  (cost=0.00..117301.58 rows=5278458
> >       width=41)
> >       ->  Hash  (cost=3436.60..3436.60 rows=176160 width=44)
> >             ->  Seq Scan on dups fd  (cost=0.00..3436.60 rows=176160
> >             width=44)
> >    (5 rows)

> What happens to the plan if you SET enable_seqscan=false; first? It's
> presumably getting the row-estimate right, so unless there's terrible
> correlation on "base" in the files table I can only assume it's getting
> the cost estimates horribly wrong.

callrec32=# SET enable_seqscan=false;
SET
callrec32=# explain select fd.base from fix.dups fd join fix.files ff using (base);
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..1066990.93 rows=176161 width=44)
   ->  Index Scan using dups_base_key on dups fd  (cost=0.00..5570.86 rows=176160 width=44)
   ->  Index Scan using ff_baseonly_idx on files ff  (cost=0.00..6.01 rows=1 width=41)
         Index Cond: (("outer".base)::text = (ff.base)::text)
(4 rows)

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

pgsql-general by date:

Previous
From: Tino Wildenhain
Date:
Subject: Re: To Postgres or not
Next
From: Tino Wildenhain
Date:
Subject: Re: INSERT INTO from a SELECT query