From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | Dilip kumar <dilip(dot)kumar(at)huawei(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Allowing join removals for more join types |
Date: | 2014-05-20 09:44:38 |
Message-ID: | CAApHDvq5utD0w_00F41OTs5vPYnn+jk3mK9DmFXDoNxtyBeWUw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, May 19, 2014 at 9:22 PM, Dilip kumar <dilip(dot)kumar(at)huawei(dot)com> wrote:
> On 19 May 2014 12:15 David Rowley Wrote,
>
>
>
>
>
> May be we can convert my above example like below à in this case we
> have unique index on field a and we are limiting it by first 100 tuple
> (record are already order because of index)
>
>
>
> Create table t1 (a int, b int);
>
> Create table t2 (a int, b int);
>
> Create unique index on t2(a);
>
>
>
> create view v1 as
>
> select x.a, y.b
>
> from t1 x left join (select t2.a a1, b from t2 limit 100) as y on
> x.a=y.a1;
>
>
>
> select a from v1; à for this query I think left join can be removed, But
> in view since non join field(b) is also projected so this cannot be
> simplified there.
>
>
>
Ok I see what you mean.
I guess then that if we did that then we should also support removals of
join in subqueries of subqueries. e.g:
select t1.* from t1 left join (select t2.uniquecol from (select
t2.uniquecol from t2 limit 1000) t2 limit 100) t2 on t1.id = t2.uniquecol
On my first round of thoughts on this I thought that we could keep looking
into the sub queries until we find that the sub query only queries a single
table or it is not a base relation. If we find one with a single table and
the sub query has no distinct or group bys then I thought we could just
look at the unique indexes similar to how it's done now for a direct table
join. But after giving this more thought, I'm not quite sure if a lack of
DISTINCT and GROUP BY clause is enough for us to permit removing the join.
Would it matter if the sub query did a FOR UPDATE?
I started looking at is_simple_subquery() in prepjointree.c but if all
those conditions were met then the subquery would have been pulled up to a
direct join anyway.
I'm also now wondering if I need to do some extra tests in the existing
code to ensure that the subquery would have had no side affects.
For example:
SELECT t1.* FROM t1
LEFT OUTER JOIN (SELECT id,some_function_that_does_something(id) FROM t2
GROUP BY id) t2 ON t1.id = t2.id;
Regards
David Rowley
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Korotkov | 2014-05-20 10:56:09 | Re: Negative imact of maintenance_work_mem to GIN size |
Previous Message | Yuto HAYAMIZU | 2014-05-20 08:02:54 | HEAD crashes with assertion and LWLOCK_STATS enabled |