Re: Allowing join removals for more join types

Поиск
Список
Период
Сортировка
От David Rowley
Тема Re: Allowing join removals for more join types
Дата
Msg-id CAApHDvoH2Xvg4nCq04GXnD6zo-t+fFrwdnzd+bLanAXK030D3A@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Allowing join removals for more join types  (Dilip kumar <dilip.kumar@huawei.com>)
Ответы Re: Allowing join removals for more join types
Re: Allowing join removals for more join types
Список pgsql-hackers
Дерево обсуждения
On Mon, May 19, 2014 at 5:47 PM, Dilip kumar <dilip.kumar@huawei.com> wrote:

 

So I think now when you are considering this join removal for subqueries then this can consider other case also like unique index inside subquery,

because in attached patch  unique index is considered only if its RTE_RELATION

 

+          if (innerrel->rtekind == RTE_RELATION &&

+                      relation_has_unique_index_for(root, innerrel, clause_list, NIL, NIL))

                       return true;

 

 


I've just had a bit of a look at implementing checks allowing subqueries with unique indexes on the join cols being removed, but I'm hitting a bit of a problem and I'm not quite sure if this is possible at this stage of planning.

In the function join_is_removable() the variable innerrel is set to the RelOptInfo of the relation which we're checking if we can remove. In the case of removing subqueries the innerrel->rtekind will be RTE_SUBQUERY. I started going over the pre-conditions that the sub query will need to meet for this to be possible and the list so far looks something like:

1. Only a single base table referenced in the sub query.
2. No FOR UPDATE clause
3. No GROUP BY or DISTINCT clause
4. No set returning functions
5. no volatile functions.
6. has unique index that covers the join conditions or a subset of.

I'm hitting a bit of a roadblock on point 1. Here's a snipped from my latest attempt:

if (bms_membership(innerrel->relids) == BMS_SINGLETON)
{
int subqueryrelid = bms_singleton_member(innerrel->relids);
RelOptInfo *subqueryrel = find_base_rel(innerrel->subroot, subqueryrelid);
if (relation_has_unique_index_for(root, subqueryrel, clause_list, NIL, NIL))
return true;
}

But it seems that innerrel->subroot is still NULL at this stage of planning and from what I can tell does not exist anywhere else yet and is not generated until make_one_rel() is called from query_planner()

Am I missing something major here,or does this sound about right?

Regards

David Rowley

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

Предыдущее
От: "Paragon Corporation"
Дата:
Сообщение: Re: PostgreSQL 9.4 InterlockedCompareExchange appearing in mingw64-w32 causing issue with PostGIS win32 load
Следующее
От: Matteo Beccati
Дата:
Сообщение: Re: uuid-ossp (Re: [pgsql-packagers] Postgresapp 9.4 beta build ready)