From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Stefan Andreatta <s(dot)andreatta(at)synedra(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: anti-join with small table via text/varchar cannot estimate rows correctly |
Date: | 2017-03-02 01:06:58 |
Message-ID: | 1454.1488416818@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Stefan Andreatta <s(dot)andreatta(at)synedra(dot)com> writes:
> The same anti-join using the text fields, however estimates just 1
> resulting row, while there are still of course 9,999 of them:
> =# explain analyze
> select tmp_san_1.id
> from tmp_san_1
> left join tmp_san_2 on tmp_san_1.text = tmp_san_2.text
> where tmp_san_2.id is null;
That is not an anti-join. To make it one, you have to constrain the RHS
join column to be IS NULL, not some random other column. Note the join
type isn't getting shown as Anti:
> Hash Left Join (cost=1.02..192.53 rows=1 width=4) (actual time=0.020..3.091 rows=9999 loops=1)
As written, the query could return some rows that weren't actually
antijoin rows, ie tmp_san_1.text *did* have a match in tmp_san_2,
but that row chanced to have a null value of id.
Possibly the planner could be smarter about estimating for this case,
but it doesn't look much like a typical use-case to me.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Stefan Andreatta | 2017-03-02 05:14:57 | Re: anti-join with small table via text/varchar cannot estimate rows correctly |
Previous Message | David G. Johnston | 2017-03-02 00:28:28 | Re: anti-join with small table via text/varchar cannot estimate rows correctly |