Re: Performance improvement for joins where outer side is unique

On 2015-08-06 15:36, Uriy Zhuravlev wrote:
> On Wednesday 08 July 2015 12:29:38 David Rowley wrote:
>> On 8 July 2015 at 02:00, Alexander Korotkov 
>> <a.korotkov@postgrespro.ru>
>> 
>> wrote:
>> > Patch doesn't apply to current master. Could you, please, rebase it?
>> 
>> Attached. Thanks.
>> 
>> Regards
>> 
>> David Rowley
>> 
>> --
>>  David Rowley                   http://www.2ndQuadrant.com/
>> <http://www.2ndquadrant.com/>
>>  PostgreSQL Development, 24x7 Support, Training & Services
> 
> Hello.
> What is the current status of the patch?
> 

[unique_joins_9e6d4e4_2015-07-08.patch]


FWIW, I just happened to be looking at the latest patch:

applies OK (against current HEAD)
compiles OK
make check fail:     join                     ... FAILED
installs OK

I was just going to repeat David's 2 queries (hardware: x86_64, Linux 
2.6.18-402.el5)

-- with these tables:
create table t1 (id int primary key);
create table t2 (id int primary key);
insert into t1 select x.x from generate_series(1,1000000) x(x);
insert into t2 select x.x from generate_series(1,1000000) x(x);

create table s1 (id varchar(32) primary key);
insert into s1 select x.x::varchar from generate_series(1,1000000) x(x);
create table s2 (id varchar(32) primary key);
insert into s2 select x.x::varchar from generate_series(1,1000000) x(x);

vacuum analyze;


I do not see much difference between patched and unpatched
after running both David's statements (unijoin.sql and unijoin2.sql):

-- pgbench -f unijoin.sql -n -T 300 -P30 testdb
select count(t2.id) from t1 left outer join t2 on t1.id=t2.id;
tps = 2.323222 - unpatched
tps = 2.356906 - patched

-- -- pgbench -f unijoin2.sql -n -T 300 -P30 testdb
tps = 1.257656 - unpatched
tps = 1.225758 - patched


So as far as I can tell it does not look very promising.


Erik Rijkers