Re: Yet another abort-early plan disaster on 9.3

From: Tomas Vondra <tv(at)fuzzy(dot)cz>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Yet another abort-early plan disaster on 9.3
Date: 2014-11-23 20:19:56
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-performance

On 21.11.2014 19:38, Jeff Janes wrote:
> When I run this patch on the regression database, I get a case where
> the current method is exact but the adaptive one is off:
> WARNING: ndistinct estimate current=676.00 adaptive=906.00
> select count(distinct stringu1) from onek;
> 676
> It should be seeing every single row, so I don't know why the
> adaptive method is off. Seems like a bug.

Thanks for noticing this. I wouldn't call it a bug, but there's clearly
room for improvement.

The estimator, as described in the original paper, does not expect the
sampling to be done "our" way (using fixed number of rows) but assumes
to get a fixed percentage of rows. Thus it does not expect the number of
sampled rows to get so close (or equal) to the total number of rows.

I think the only way to fix this is by checking if samplerows is close
to totalrows, and use a straightforward estimate in that case (instead
of a more sophisticated one). Something along these lines:

if (samplerows >= 0.95 * totalrows)
stats->stadistinct = (d + d/0.95) / 2;

which means "if we sampled >= 95% of the table, use the number of
observed distinct values directly".

I have modified the estimator to do the adaptive estimation, and then do
this correction too (and print the values). And with that in place I get
these results

WARNING: ndistinct estimate current=676.00 adaptive=996.00
WARNING: corrected ndistinct estimate current=676.00 adaptive=693.79

So it gets fairly close to the original estimate (and exact value).

In the end, this check should be performed before calling the adaptive
estimator at all (and not calling it in case we sampled most of the rows).

I also discovered an actual bug in the optimize_estimate() function,
using 'f_max' instead of the number of sampled rows.

Attached is a patch fixing the bug, and implementing the sample size check.


Attachment Content-Type Size
ndistinct-estimator-v2.patch text/x-diff 5.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2014-11-23 20:32:43 Re: Turning recovery.conf into GUCs
Previous Message Vladimir Koković 2014-11-23 18:37:58 make check-world regress failed

Browse pgsql-performance by date

  From Date Subject
Next Message Johann Spies 2014-11-24 07:01:56 Re: pgtune + configurations with 9.3
Previous Message Jeff Janes 2014-11-21 18:38:27 Re: Yet another abort-early plan disaster on 9.3