Re: Tuplesort merge pre-reading

On Thu, Sep 15, 2016 at 1:51 PM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
> BTW, does a 1-way merge make any sense? I was surprised to see this in the
> log, even without this patch:
>
> LOG: finished 1-way merge step: CPU 0.62s/7.22u sec elapsed 8.43 sec
> STATEMENT: SELECT COUNT(*) FROM (SELECT * FROM medium.random_ints ORDER BY
> i) t;
> LOG: finished 1-way merge step: CPU 0.62s/7.22u sec elapsed 8.43 sec
> STATEMENT: SELECT COUNT(*) FROM (SELECT * FROM medium.random_ints ORDER BY
> i) t;
> LOG: finished 1-way merge step: CPU 0.62s/7.22u sec elapsed 8.43 sec
> STATEMENT: SELECT COUNT(*) FROM (SELECT * FROM medium.random_ints ORDER BY
> i) t;
> LOG: finished 1-way merge step: CPU 0.62s/7.22u sec elapsed 8.43 sec
> STATEMENT: SELECT COUNT(*) FROM (SELECT * FROM medium.random_ints ORDER BY
> i) t;
> LOG: finished 3-way merge step: CPU 0.62s/7.23u sec elapsed 8.44 sec
> STATEMENT: SELECT COUNT(*) FROM (SELECT * FROM medium.random_ints ORDER BY
> i) t;
> LOG: finished 6-way merge step: CPU 0.62s/7.24u sec elapsed 8.44 sec
> STATEMENT: SELECT COUNT(*) FROM (SELECT * FROM medium.random_ints ORDER BY
> i) t;
> LOG: finished 6-way merge step: CPU 0.62s/7.24u sec elapsed 8.45 sec
> STATEMENT: SELECT COUNT(*) FROM (SELECT * FROM medium.random_ints ORDER BY
> i) t;

Another thing that I think it worth pointing out here is that the
number of merge passes shown is excessive, practically speaking. I
suggested that we have something like checkpoint_warning for this last
year, which Greg Stark eventually got behind, but Robert Haas didn't
seem to like. Maybe this idea should be revisited. What do you think?

There is no neat explanation for why it's considered excessive to
checkpoint every 10 seconds, but not every 2 minutes. But, we warn
about the former case by default, and not the latter. It's hard to
know exactly where to draw the line, but that isn't a great reason to
not do it (maybe one extra merge pass is a good threshold -- that's
what I suggested once). I think that other database systems similarly
surface multiple merge passes. It's just inefficient to ever do
multiple merge passes, even if you're very frugal with memory.
Certainly, it's almost impossible to defend doing 3+ passes these
days.

--
Peter Geoghegan