Skip site navigation (1) Skip section navigation (2)

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 (view raw, whole thread or download thread mbox)
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: better-default-estimate-in-eqjoinsel_semi.patch
Description: text/x-diff (1.2 KB)

pgsql-hackers by date

Next:From: Nico WilliamsDate: 2016-11-29 02:26:24
Subject: Re: Tackling JsonPath support
Previous:From: Christian ConveyDate: 2016-11-29 01:50:40
Subject: Re: Tackling JsonPath support

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group