Re: Parameterized-path cost comparisons need some work

On Sun, Mar 4, 2012 at 12:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> After looking at the results, I think that the fallacy in what we've
> been discussing is this: a parameterized path may well have some extra
> selectivity over a less-parameterized one, but perhaps *not enough to be
> meaningful*.  The cases I was getting hits on were where the rowcount
> estimate got rounded off to be the same as for the less-parameterized
> path.  (In this connection it's worth noting that most of the hits were
> for rowcount estimates of only 1 or 2 rows.)  So basically, the scenario
> is where you have restriction clauses that are already enough to get
> down to a small number of rows retrieved, and then you have some join
> clauses that are not very selective and don't reduce the rowcount any
> further.  Or maybe you have some nicely selective join clauses, and then
> adding more joins to some other relations doesn't help any further.

OK, makes sense.

> One annoying thing about that is that it will reduce the usefulness of
> add_path_precheck, because that's called before we compute the rowcount
> estimates (and indeed not having to make the rowcount estimates is one
> of the major savings from the precheck).  I think what we'll have to do
> is assume that a difference in parameterization could result in a
> difference in rowcount, and hence only a dominant path with exactly the
> same parameterization can result in failing the precheck.

I wish we had some way of figuring out how much this - and maybe some
of the other new planning possibilities like index-only scans - were
going to cost us on typical medium-to-large join problems. In the
absence of real-world data it's hard to know how worried we should be.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company