Hi all, this is my first post and my english is bad, please be patient :-)

I've a problem with a full outer join, let me explain.

2 tables (100000 rows each), 2 cte, 2 full outer joins, 100 rows output.
No indexes. Execution took about 40s..... in postgres (i've do a equal
test comparision)...270ms

I've tested it on FB 2.1.7, 2.5.5 and 3.0.0, OS is virtual 64 bit
Windows server 2012 8GB ram, Classic Server, Dialect 3, no firebird.conf
optimization/modify, no ssd, no raid, pagesize 16mb

The test query may be stupid, but it explains exacty the performace problem.

Create table script:

TABLE 1:
CREATE SEQUENCE GEN_NEW_TABLE_ID;

CREATE TABLE NEW_TABLE (
ID INTEGER NOT NULL,
FIELD1 INTEGER,
FIELD2 INTEGER,
FIELD3 INTEGER,
FIELD4 INTEGER,
FILED5 INTEGER
);

ALTER TABLE NEW_TABLE ADD CONSTRAINT PK_NEW_TABLE PRIMARY KEY (ID);

CREATE OR ALTER TRIGGER NEW_TABLE_BI FOR NEW_TABLE
ACTIVE BEFORE INSERT POSITION 0
as
begin
new.id = gen_id(gen_new_table_id,1); --or use next value for
end

TABLE 2:
CREATE SEQUENCE GEN_NEW_TABLE2_ID;

CREATE TABLE NEW_TABLE2 (
ID INTEGER NOT NULL,
FIELD1 INTEGER,
FIELD2 INTEGER,
FIELD3 INTEGER,
FIELD4 INTEGER,
FILED5 INTEGER
);

ALTER TABLE NEW_TABLE2 ADD CONSTRAINT PK_NEW_TABLE2 PRIMARY KEY (ID);

CREATE OR ALTER TRIGGER NEW_TABLE2_BI FOR NEW_TABLE2
ACTIVE BEFORE INSERT POSITION 0
as
begin
new.id = gen_id(gen_new_table2_id,1); --or use next value for
end

Stupid bulk insert 100000 records in each table (same records in each
table):

execute block
as
declare variable i int =0;
declare variable x int =0;
begin
while (x < 100) do
begin
i =0;
while (i < 1000) do
begin
insert into NEW_TABLE values (:i, :i, :i, :i, :i, :i);
insert into NEW_TABLE2 values (:i, :i, :i, :i, :i, :i);
i = i + 1;
end
x = x + 1;
end
end

Ok, now the query:

with
T2CTE as (select first 10 * from new_table2 where field1=1),
T1CTE as (select first 100 * from new_table where field1=1)

select t1.id, t1.FIELD1, t1.FIELD2, t1.FIELD3, t1.FIELD4
from T1CTE t1
full outer join T2CTE t2 on (t2.field1 = t1.field1 and t2.field2 =
t1.field2 and t2.field3 = t1.field3 and t2.field4 = t1.field4 )
full outer join T1CTE t1x on (t1x.field1 = t1.field1 and t1x.field2 =
t1.field2 and t1x.field3 = t1.field3 and t1x.field4 = t1.field4)
group by t1.id, t1.FIELD1, t1.FIELD2, t1.FIELD3, t1.FIELD4

Yes, it's stupid but shows me the problem (IBEXPERT output):

Plan
PLAN SORT (JOIN (T1X NEW_TABLE NATURAL, JOIN (T2 NEW_TABLE2 NATURAL, T1
NEW_TABLE NATURAL)))

Prepare time = 16ms
Execute time = 40s 750ms
Avg fetch time = 1.455,36 ms
Current memory = 137.242.916
Max memory = 157.081.992
Memory buffers = 8.192
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 222.991.689

Again, viewing performance reads summary, it says:

Non indexed reads:
NEW_TABLE: 110092224
NEW_TABLE2: 929402

110092224? 929402? WHY? CTE EXPLICITY SAYS 100 and 10!

I've repeat the test with postgresql (same machine, postgres 9.5, same
standard installation and same table data) and average execution time is
about 270ms. It shows me the effective cte scan:

HashAggregate (cost=2088.17..2089.16 rows=99 width=20)
Group Key: t1.id, t1.field1, t1.field2, t1.field3, t1.field4
CTE t2cte
-> Limit (cost=0.00..190.61 rows=10 width=24)
-> Seq Scan on new_table2 (cost=0.00..1887.00 rows=99 width=24)
Filter: (field1 = 1)
CTE t1cte
-> Limit (cost=0.00..1887.00 rows=99 width=24)
-> Seq Scan on new_table (cost=0.00..1887.00 rows=99 width=24)
Filter: (field1 = 1)
-> Hash Full Join (cost=4.36..9.33 rows=99 width=20)
Hash Cond: ((t1.field1 = t1x.field1) AND (t1.field2 =
t1x.field2) AND (t1.field3 = t1x.field3) AND (t1.field4 = t1x.field4))
-> Hash Full Join (cost=0.40..3.87 rows=99 width=20)
Hash Cond: ((t1.field1 = t2.field1) AND (t1.field2 =
t2.field2) AND (t1.field3 = t2.field3) AND (t1.field4 = t2.field4))
-> CTE Scan on t1cte t1 (cost=0.00..1.98 rows=99 width=20)
-> Hash (cost=0.20..0.20 rows=10 width=16)
-> CTE Scan on t2cte t2 (cost=0.00..0.20 rows=10
width=16)
-> Hash (cost=1.98..1.98 rows=99 width=16)
-> CTE Scan on t1cte t1x (cost=0.00..1.98 rows=99 width=16)

Seems (i'm not a master of known universe in firebird) that the firebird
performs a full table scan and joins mutch more data instead of the CTE
results.

Any ideas about why fb take too long time to execute the query (and
scans so mutch rows)? I've omitted some CTE sintax to force fb to use it?

The question is not about the query optimization (full outer join can be
omitted, i know), but about why fb performs many reads insetad of 100 +
10 cte reads.

Please correct me because i'm sure that i've done something erroneusly.

Best regards,

Agharta_.___

 

---