Re: convert EXSITS to inner join gotcha and bug

On Fri, Apr 28, 2017 at 12:48 PM, Teodor Sigaev <teodor(at)sigaev(dot)ru> wrote:

> Both 9.6 and 10devel are affected to addiction of query result on seqscan
>> variable.
>>
> Oops, I was too nervious, 9.6 is not affected to enable_seqscan setting.
> But it doesn't push down condition too.

I've reproduced this bug on d981074c.
On default config, after loading example.sql.bz2 and VACUUM ANALYZE, query
result is OK.

# explain analyze SELECT
*
FROM
t1
INNER JOIN t2 ON (
EXISTS (
SELECT
true
FROM
t3
WHERE
t3.id1 = t1.id AND
t3.id2 = t2.id
)
)
WHERE
t1.name = '5c5fec6a41b8809972870abc154b3ecd';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=6.42..1924.71 rows=1 width=99) (actual
time=14.044..34.957 rows=*162* loops=1)
Join Filter: (t3.id1 = t1.id)
Rows Removed by Join Filter: 70368
-> Index Only Scan using t1i2 on t1 (cost=0.28..4.30 rows=1 width=66)
(actual time=0.026..0.028 rows=1 loops=1)
Index Cond: (name = '5c5fec6a41b8809972870abc154b3ecd'::text)
Heap Fetches: 0
-> Hash Join (cost=6.14..1918.37 rows=163 width=66) (actual
time=0.077..28.310 rows=70530 loops=1)
Hash Cond: (t3.id2 = t2.id)
-> Seq Scan on t3 (cost=0.00..1576.30 rows=70530 width=66)
(actual time=0.005..6.433 rows=70530 loops=1)
-> Hash (cost=3.84..3.84 rows=184 width=33) (actual
time=0.065..0.065 rows=184 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 20kB
-> Seq Scan on t2 (cost=0.00..3.84 rows=184 width=33)
(actual time=0.003..0.025 rows=184 loops=1)
Planning time: 2.542 ms
Execution time: 35.008 ms
(14 rows)

But with seqscan and hashjoin disabled, query returns 0 rows.

# set enable_seqscan = off;
# set enable_hashjoin = off;
# explain analyze SELECT
*
FROM
t1
INNER JOIN t2 ON (
EXISTS (
SELECT
true
FROM
t3
WHERE
t3.id1 = t1.id AND
t3.id2 = t2.id
)
)
WHERE
t1.name = '5c5fec6a41b8809972870abc154b3ecd';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.97..5265.82 rows=1 width=99) (actual
time=18.718..18.718 rows=*0* loops=1)
Join Filter: (t3.id1 = t1.id)
Rows Removed by Join Filter: 163
-> Index Only Scan using t1i2 on t1 (cost=0.28..4.30 rows=1 width=66)
(actual time=0.024..0.024 rows=1 loops=1)
Index Cond: (name = '5c5fec6a41b8809972870abc154b3ecd'::text)
Heap Fetches: 0
-> Merge Join (cost=0.69..5259.48 rows=163 width=66) (actual
time=0.033..18.670 rows=163 loops=1)
Merge Cond: (t2.id = t3.id2)
-> Index Only Scan using t2i1 on t2 (cost=0.27..19.03 rows=184
width=33) (actual time=0.015..0.038 rows=184 loops=1)
Heap Fetches: 0
-> Index Only Scan using t3i2 on t3 (cost=0.42..4358.37
rows=70530 width=66) (actual time=0.015..10.484 rows=70094 loops=1)
Heap Fetches: 0
Planning time: 2.571 ms
Execution time: 18.778 ms
(14 rows)

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company