Re: Top-N sorts verses parallelism

On Tue, Dec 12, 2017 at 10:46 PM, Thomas Munro <
thomas(dot)munro(at)enterprisedb(dot)com> wrote:

> Hi hackers,
>
> The start-up cost of bounded (top-N) sorts is sensitive at the small
> end of N, and the
> comparison_cost * tuples * LOG2(2.0 * output_tuples) curve doesn't
> seem to correspond to reality. Here's a contrived example that comes
> from a benchmark:
>
> create table foo as
> select generate_series(1, 1000000)::int a, (random() * 1000)::int b;
> analyze foo;
> select * from foo order by b limit N;
>

This looks like a costing bug. The estimated cost of sorting 416,667
estimated tuples in one parallel worker is almost identical to the
estimated cost of sorting 1,000,000 tuples when parallelism is turned off.
Adding some logging to the cost_sort function, it looks like the limit does
not get sent down for the parallel estimate:

NOTICE: JJ cost_sort tuples 1000000.000000, limit 61.000000, sort_mem 65536
NOTICE: JJ cost_sort tuples 416667.000000, limit -1.000000, sort_mem 65536

So the LIMIT gets passed down for the execution step, but not for the
planning step.

(On my system, LIMIT 61 is the point at which it switches to parallel)

In any case, if we "fixed" the top-n estimate to use the random-case rather
than the worst-case, that would make the LIMIT 133 look more like the LIMIT
1, so would be the opposite direction of what you want.

Cheers,

Jeff