From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stefan Andreatta <s.andreatta@synedra.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: anti-join with small table via text/varchar cannot estimate rows correctly
Date: Wed, 01 Mar 2017 20:06:58 -0500
Message-ID: <1454.1488416818@sss.pgh.pa.us> (raw)
In-Reply-To: <4de52c93-68e1-7c4b-86ff-4dc17ae400bb@synedra.com>
References: <4de52c93-68e1-7c4b-86ff-4dc17ae400bb@synedra.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Stefan Andreatta <s.andreatta@synedra.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, s.andreatta@synedra.com
Subject: Re: anti-join with small table via text/varchar cannot estimate rows correctly
In-Reply-To: <1454.1488416818@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox