From: Stefan Andreatta <s.andreatta@synedra.com>
To: pgsql-performance@postgresql.org
Subject: anti-join with small table via text/varchar cannot estimate rows correctly
Date: Wed, 1 Mar 2017 23:00:06 +0100
Message-ID: <4de52c93-68e1-7c4b-86ff-4dc17ae400bb@synedra.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hello,
I have encountered a strange problem when doing an anti-join with a very
small table via a varchar or text field as opposed to an integer field.
Postgres version is 9.5.3
I did some experiments to extract the problem in a simple form. FIrst
generate two tables with a series of numbers - once as integers once as
text. The first table has 10,000 rows the second table just one:
=# select generate_series(1, 10000) as id, generate_series(1,10000)::text as text into table tmp_san_1;
SELECT 10000
=# select generate_series(1, 1) as id, generate_series(1,1)::text as text into table tmp_san_2;
SELECT 1
=# analyze tmp_san_1;
ANALYZE
=# analyze tmp_san_2;
ANALYZE
=# \d tmp_san_*
Table "public.tmp_san_1"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
text | text |
Table "public.tmp_san_2"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
text | text |
Now I do an anti-join between the two tables via the id field (integer).
The number of resulting rows are estimated correctly as 9,999:
=# explain analyze
select tmp_san_1.id
from tmp_san_1
left join tmp_san_2 on tmp_san_1.id = tmp_san_2.id
where tmp_san_2.id is null;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=1.02..281.26 rows=9999 width=4) (actual time=0.019..2.743 rows=9999 loops=1)
Hash Cond: (tmp_san_1.id = tmp_san_2.id)
-> Seq Scan on tmp_san_1 (cost=0.00..154.00 rows=10000 width=4) (actual time=0.007..1.023 rows=10000 loops=1)
-> Hash (cost=1.01..1.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on tmp_san_2 (cost=0.00..1.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)
Planning time: 0.138 ms
Execution time: 3.218 ms
(8 rows)
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;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=1.02..192.53 rows=1 width=4) (actual time=0.020..3.091 rows=9999 loops=1)
Hash Cond: (tmp_san_1.text = tmp_san_2.text)
Filter: (tmp_san_2.id IS NULL)
Rows Removed by Filter: 1
-> Seq Scan on tmp_san_1 (cost=0.00..154.00 rows=10000 width=8) (actual time=0.008..0.983 rows=10000 loops=1)
-> Hash (cost=1.01..1.01 rows=1 width=6) (actual time=0.004..0.004 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on tmp_san_2 (cost=0.00..1.01 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=1)
Planning time: 0.173 ms
Execution time: 3.546 ms
(10 rows)
I cannot explain that behavior and much less think of a fix or
workaround. Unfortunately my real-world example has to use varchar for
the join.
Thanks for any help,
Stefan
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: s.andreatta@synedra.com
Subject: Re: anti-join with small table via text/varchar cannot estimate rows correctly
In-Reply-To: <4de52c93-68e1-7c4b-86ff-4dc17ae400bb@synedra.com>
* 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