[HACKERS] propose to pushdown qual into EXCEPT

Поиск
Список
Период
Сортировка
От Armor
Тема [HACKERS] propose to pushdown qual into EXCEPT
Дата
Msg-id tencent_423CD7A4633009A46075A3ED@qq.com
обсуждение исходный текст
Ответы Re: [HACKERS] propose to pushdown qual into EXCEPT
Список pgsql-hackers
Дерево обсуждения
    Recently, we find PG fails to generate an effective plan for following SQL:
    select * from (select * from table1 execpt select * from table2) as foo where foo.a > 0;
    Because PG does not pushdown qual to the none of the subquery. And I check the source code, find some comments in src/backend/optimizer/path/allpaths.c, which says "If the     subquery contains EXCEPT or EXCEPT ALL set ops we cannot push quals into it, because that could change the results".
    However, for this case, I think we can pushdown qual to the  left most subquery of EXCEPT, just like other database does. And we can get an more effective plan such as:
postgres=# explain select * from (select * from table1 except select * from table2) as foo where foo.a > 0;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Subquery Scan on foo  (cost=0.00..118.27 rows=222 width=8)
   ->  HashSetOp Except  (cost=0.00..116.05 rows=222 width=12)
         ->  Append  (cost=0.00..100.98 rows=3013 width=12)
               ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..45.78 rows=753 width=12)
                     ->  Seq Scan on table1  (cost=0.00..38.25 rows=753 width=8)
                           Filter: (a > 0)
               ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..55.20 rows=2260 width=12)
                     ->  Seq Scan on table2  (cost=0.00..32.60 rows=2260 width=8)
(8 rows)

   And the attached patch is a draft, it works for this case.
    
------------------
Jerry Yu
https://github.com/scarbrofair
 
Вложения

В списке pgsql-hackers по дате отправления: