Re: Proposal - improve eqsel estimates by including histogram bucket numdistinct statistics

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> (In fact, I don't think the plan would change, in this case. The reason
> for the clamp to 1 row is to avoid foolish results for join situations.)

The screw case I've seen is when you have a large partitioned table where
constraint_exclusion fails to exclude the irrelevant partitions. You're going
to get 0 rows from all but the one partition which contains the 1 row you're
looking for. But since each partition is clamped to 1 you end up with an
estimate of a few hundred rows coming out of the Append node.

The natural way to kill this is to allow fractional rows for these scans. We
know they're usually going to be producing 0 so if the estimates produced the
right average expected value the sum would add up to 1 and the Append node
would get the right value.

Alternatively we could make Append more clever about estimating the number of
rows it produces. Somehow it could be informed of some holistic view of the
quals on its children and how they're inter-dependent. If it's told that only
one of its children will produce rows then it can use max() instead of sum()
to calculate its rows estimate.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!