Corner-case improvement to eqjoinsel_semi

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Corner-case improvement to eqjoinsel_semi
Date: 2016-11-29 01:58:33
Message-ID: 31089.1480384713@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

There's a complaint in bug #14438 about poor estimation of join size
for a semijoin whose inner side is empty. I think the root of it is that,
having no statistics for the empty table, eqjoinsel_semi distrusts its
estimate of the number of distinct values on the inner side, and falls
back to a conservative calculation that has little to do with reality.
However, it's not really true that the nd2 estimate is based on nothing at
all, because we clamped it to be no more than the estimated size of the
inner relation (which we knew to be small). If we go ahead and use that
number as a valid estimate, we get a far better selectivity estimate ---
in the bug's example, the join size estimate goes from 16000-some to 2,
which is a tad closer to the correct value of 0.

Hence, I propose the attached patch. This would kick in whenever the
inner side of a semi/antijoin has no statistics and an estimated size
of less than 200 rows.

Thoughts?

regards, tom lane

Attachment Content-Type Size
better-default-estimate-in-eqjoinsel_semi.patch text/x-diff 1.2 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Nico Williams 2016-11-29 02:26:24 Re: Tackling JsonPath support
Previous Message Christian Convey 2016-11-29 01:50:40 Re: Tackling JsonPath support