Re: [HACKERS] convert EXSITS to inner join gotcha and bug - Mailing list pgsql-hackers
| From | Alexander Korotkov |
|---|---|
| Subject | Re: [HACKERS] convert EXSITS to inner join gotcha and bug |
| Date | |
| Msg-id | CAPpHfdtY-S51uNfEHObmq+3gJK_Ohfx7pEMs7k5SEM-JUMjrNA@mail.gmail.com Whole thread Raw |
| In response to | Re: [HACKERS] convert EXSITS to inner join gotcha and bug (Teodor Sigaev <teodor@sigaev.ru>) |
| Responses |
Re: [HACKERS] convert EXSITS to inner join gotcha and bug
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: [HACKERS] convert EXSITS to inner join gotcha and bug (David Rowley <david.rowley@2ndquadrant.com>) |
| List | pgsql-hackers |
On Fri, Apr 28, 2017 at 12:48 PM, Teodor Sigaev <teodor@sigaev.ru> wrote:
Both 9.6 and 10devel are affected to addiction of query result on seqscanOops, I was too nervious, 9.6 is not affected to enable_seqscan setting. But it doesn't push down condition too.
variable.
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.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.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
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
pgsql-hackers by date: