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 |
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 |