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(dot)korotkov(at)postgrespro(dot)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