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 20050713124827.GA63397@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
List pgsql-general
# dev@archonet.com / 2005-07-13 12:57:31 +0100:
> Roman Neuhauser wrote:
> >Why does the planner want to crawl the table that has 5M rows instead of
> >the one
> >with 176k rows? Both tables are freshly vacuum-full-analyzed.
>
> Because you don't have an index on "base" for the files table.

    I added one, ran vacuum full analyze fix.files, and:

    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)

    Which is exactly what I expected. Using left prefix of a multicolumn
    index normally works just fine, thank you.

    http://www.postgresql.org/docs/7.4/static/indexes-multicolumn.html:

    The query planner can use a multicolumn index for queries that involve
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    the leftmost column in the index definition plus any number of columns
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    listed to the right of it, without a gap. For example, an index on (a,
    b, c) can be used in queries involving all of a, b, and c, or in queries
    involving both a and b, or in queries involving only a

--
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: Einar Indridason
Date:
Subject: Strange memory behaviour with PGreset() ...
Next
From: Alvaro Herrera
Date:
Subject: Re: can you tell me how to use rollback work in 'pgsql' function? and give me a exsample?