Re: [HACKERS] [GENERAL] Remove useless joins (VARCHAR vs TEXT)

2017-09-17 Thread Tom Lane
David Rowley  writes:
> On 17 September 2017 at 08:07, Kim Rose Carlsen  wrote:
>> It seems there are some difference in VARCHAR vs TEXT when postgres tries to
>> decide if a LEFT JOIN is useful or not.

> Yeah, it looks like the code to check for distinctness in the subquery
> fails to consider that the join condition may contain RelabelTypes
> instead of plain Vars.
> 
> The attached fixes.

Looks like a good fix to me (except for the copied-and-pasted,
not-quite-on-point comment ;-)).  Pushed.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] [GENERAL] Remove useless joins (VARCHAR vs TEXT)

2017-09-16 Thread David Rowley
On 17 September 2017 at 08:07, Kim Rose Carlsen  wrote:
> It seems there are some difference in VARCHAR vs TEXT when postgres tries to
> decide if a LEFT JOIN is useful or not. I can't figure out if this is
> intentional because there are some difference between TEXT and VARCHAR that
> I dont know about or if it's a bug.
>
>
> I would expect both examples to produce same query plan
>
>
> a)
>
> create table a (id varchar primary key);
> create table b (id varchar primary key);
>
> explain   select a.*
>  from a
> left join (select distinct id from b) as b
>on a.id = b.id;
>
>
> QUERY PLAN
> --
>  Hash Right Join  (cost=67.60..113.50 rows=1360 width=32)
>Hash Cond: ((b.id)::text = (a.id)::text)
>->  HashAggregate  (cost=27.00..40.60 rows=1360 width=32)
>  Group Key: b.id
>  ->  Seq Scan on b  (cost=0.00..23.60 rows=1360 width=32)
>->  Hash  (cost=23.60..23.60 rows=1360 width=32)
>  ->  Seq Scan on a  (cost=0.00..23.60 rows=1360 width=32)
> (7 rows)
>
> b)
>
> create table a (id text primary key);
>
> create table b (id text primary key);
>
> explain   select a.*
>  from a
> left join (select distinct id from b) as b
>on a.id = b.id;
>
>   QUERY PLAN
> --
>  Seq Scan on a  (cost=0.00..23.60 rows=1360 width=32)

Yeah, it looks like the code to check for distinctness in the subquery
fails to consider that the join condition may contain RelabelTypes
instead of plain Vars.

The join would be removed if you'd written:

explain select a.* from a left join b on a.id = b.id;

so really the subquery version should be too.

I'm undecided if this should be classed as a bug or just a missed
optimisation. Certainly, the original code should have done this, so
I'm leaning slightly towards this being a bug.

The attached fixes.

(CC'd -hackers since we're starting to discuss code changes. Further
discussion which includes -hackers should drop the general list)

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


join_removal_subquery_fix.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers