From: Tom Lane <tgl@sss.pgh.pa.us>
To: Henrik Zagerholm <henke@mac.se>
Cc: Gregory Stark <stark@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: [PERFORM] Planner making wrong decisions 8.2.4. Insane cost calculations.
Date: Mon, 06 Aug 2007 18:23:34 -0400
Message-ID: <3569.1186439014@sss.pgh.pa.us> (raw)
In-Reply-To: <3F198A58-3132-4B8E-8E2D-1D158C147EA3@mac.se>
References: <0C963119-103A-4EB1-B9D8-0FD3E16A11F0@mac.se>
<87abt4n75m.fsf@oxford.xeocode.com>
<A9EBAF31-5AEF-4F1C-B845-2D81D9936560@mac.se>
<27852.1186414311@sss.pgh.pa.us>
<3F198A58-3132-4B8E-8E2D-1D158C147EA3@mac.se>
Henrik Zagerholm <henke@mac.se> writes:
> At what point does the planner choose seq scans?
When it thinks it's cheaper than the other way. There's no hard and
fast answer. The immediate problem you've got is that the estimated
size of the tbl_file/tbl_filetype_suffix join is off by a factor of
almost 20 (8061 vs 486). The plan that you think would be faster
involves an inner indexscan on the larger table for each result row from
that join, and therefore this error translates directly to a 20x
overestimate of its cost, and therefore the planner avoids that in favor
of a hash join that indeed is more efficient when there are lots of rows
to be joined.
It may well be that you also need to adjust random_page_cost and/or
effective_cache_size so that the planner's estimates of indexscan vs
seqscan costs are more in line with reality on your machine. But it's
a capital error to tinker with those numbers on the basis of an example
where the rowcount estimates are so far off. (Actually I'd not advise
changing them on the basis of *any* single test case, you need to look
at average behaviors. Get the count estimates fixed first and then
see where you are.)
It's also not impossible that the planner is right and the seqscan is
better than a lot of index probes ...
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, henke@mac.se, stark@enterprisedb.com, pgsql-general@postgresql.org
Subject: Re: [PERFORM] Planner making wrong decisions 8.2.4. Insane cost calculations.
In-Reply-To: <3569.1186439014@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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