Re: cost_rescan (was: match_unsorted_outer() vs. cost_nestloop())
On Sat, Sep 12, 2009 at 6:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Sep 6, 2009, at 10:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
... But now that we have a plan for a less obviously broken costing
approach, maybe we should open the floodgates and allow
materialization
to be considered for any inner path that doesn't materialize itself
alreadyMaybe. I think some experimentation will be required. We also have
to be aware of effects on planning time; match_unsorted_outer() is,
AIR, a significant part of the CPU cost of planning large join problems.I've committed some changes pursuant to this discussion. It may be that
match_unsorted_outer gets a bit slower, but I'm not too worried about
that. My experience is that the code that tries different mergejoin
options eats way more cycles than the nestloop code does.
One problem with the current implementation of cost_rescan() is that
it ignores caching effects. It seems to be faster to rescan a
materialize node than it is to rescan a seqscan of a table, even if
there are no restriction clauses, presumably because you get to skip
tuple visibility checks and maybe some other overhead, too. But
cost_rescan() thinks that rescanning the table will require rereading
the whole thing from disk, which isn't right either - it probably
ought to factor in effective_cache_size much as the estimates for
iterated index scans do. I'm not sure how many real problems this is
going to create.
Another potential problem is that materializing a whole-table seqscan
to avoid repeating the tuple visibility checks may be a win in some
strict sense, but there are externalities: it's also going to use a
lot more memory/disk than just rescanning the table.
...Robert
Robert Haas <robertmhaas@gmail.com> writes:
One problem with the current implementation of cost_rescan() is that
it ignores caching effects.
Well, that's intentional, per the head comment for the function.
We might want to extend it later but I'd like to get some field
experience with what it's trying to model now. I believe that it
is covering the first-order effects, and possible cache effects
would be second-order.
It seems to be faster to rescan a
materialize node than it is to rescan a seqscan of a table, even if
there are no restriction clauses, presumably because you get to skip
tuple visibility checks and maybe some other overhead, too.
Exactly. IIRC, tuplestore's on-disk representation is also more compact
(less header overhead, no dead tuples, etc) so the amount of I/O needed
will also be less, if you're doing any at all. But the code already
knows that scanning a tuplestore is cheaper than scanning a table ---
that doesn't seem to me to be relevant to the question of whether we
need to model cache effects in cost_rescan.
Another potential problem is that materializing a whole-table seqscan
to avoid repeating the tuple visibility checks may be a win in some
strict sense, but there are externalities: it's also going to use a
lot more memory/disk than just rescanning the table.
This is not specific to materialize, it is part of the generic problem
that we don't model the true costs of using work_mem in each of several
parts of a query. There have been discussions about how to fix that
before, but no particularly good ideas have emerged.
regards, tom lane