CREATE TABLE a AS (SELECT gs AS x FROM generate_series(1,10) AS gs);

CREATE TABLE b AS (SELECT gs AS x FROM generate_series(1,100) AS gs);

--

-- Returns string-by-string explain of a query. Made for removing some strings

-- from the explain output.

--

CREATE OR REPLACE FUNCTION expln(query_string text) RETURNS SETOF text AS $$

BEGIN

RETURN QUERY

EXECUTE format('EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) %s', query_string);

RETURN;

END;

$$ LANGUAGE PLPGSQL;

--

-- A LEFT JOIN B isn't equal B LEFT JOIN A.

--

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)

SELECT * FROM a LEFT JOIN b USING (x);

QUERY PLAN

SELECT str AS result

FROM expln('

SELECT * FROM a LEFT JOIN b USING (x);') AS str

WHERE str NOT LIKE '%Memory%';

result

-----------------------------------------------------

Merge Left Join (actual rows=10 loops=1)

AQO not used

Merge Cond: (a.x = b.x)

-> Sort (actual rows=10 loops=1)

AQO not used

Sort Key: a.x

Sort Method: quicksort Memory: 25kB

-> Seq Scan on a (actual rows=10 loops=1)

AQO not used

-> Sort (actual rows=11 loops=1)

AQO not used

Sort Key: b.x

Sort Method: quicksort Memory: 30kB

-> Seq Scan on b (actual rows=100 loops=1)

AQO not used

Using aqo: true

AQO mode: LEARN

JOINS: 0

(18 rows)

(16 rows)

-- TODO: Using method of other classes neighbours we get a bad estimation.

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)

SELECT * FROM b LEFT JOIN a USING (x);

QUERY PLAN

------------------------------------------------------

SELECT str AS result

FROM expln('

SELECT * FROM b LEFT JOIN a USING (x);') AS str

WHERE str NOT LIKE '%Memory%';

result

----------------------------------------------------

Hash Left Join (actual rows=100 loops=1)

AQO: rows=10, error=-900%

Hash Cond: (b.x = a.x)

-> Seq Scan on b (actual rows=100 loops=1)

AQO: rows=100, error=0%

-> Hash (actual rows=10 loops=1)

AQO not used

Buckets: 1024 Batches: 1 Memory Usage: 9kB

-> Seq Scan on a (actual rows=10 loops=1)

AQO: rows=10, error=0%

Using aqo: true

AQO mode: LEARN

JOINS: 0

(13 rows)

(12 rows)

-- Look into the reason: two JOINs from different classes have the same FSS.

SELECT to_char(d1.targets[1], 'FM999.00') AS target FROM aqo_data d1

JOIN aqo_data d2 ON (d1.fs <> d2.fs AND d1.fss = d2.fss)

WHERE 'a'::regclass = ANY (d1.oids) AND 'b'::regclass = ANY (d1.oids);

WHERE 'a'::regclass = ANY (d1.oids) AND 'b'::regclass = ANY (d1.oids) order by target;

target

--------

2.30

CREATETABLEbAS (SELECT gsAS xFROM generate_series(1,100)AS gs);

CREATE OR REPLACEFUNCTIONexpln(query_stringtext) RETURNS SETOFtextAS $$

RETURN QUERY

EXECUTE format('EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) %s', query_string);

RETURN;

END;

$$ LANGUAGE PLPGSQL;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)

SELECT*FROM aLEFT JOIN b USING (x);

SELECT strAS result

FROM expln('

SELECT * FROM a LEFT JOIN b USING (x);')AS str

WHERE str NOTLIKE'%Memory%';

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)

SELECT*FROM bLEFT JOIN a USING (x);

SELECT strAS result

FROM expln('

SELECT * FROM b LEFT JOIN a USING (x);')AS str

WHERE str NOTLIKE'%Memory%';

SELECT to_char(d1.targets[1],'FM999.00')AS targetFROM aqo_data d1

JOIN aqo_data d2ON (d1.fs<>d2.fsANDd1.fss=d2.fss)

WHERE'a'::regclass= ANY (d1.oids)AND'b'::regclass= ANY (d1.oids);

WHERE'a'::regclass= ANY (d1.oids)AND'b'::regclass= ANY (d1.oids)order by target;

DROPTABLE a,b CASCADE;

SELECT trueFROM aqo_reset();