\pset border 1 \pset linestyle ascii \set VERBOSITY terse SET client_min_messages = INFO; /* analyze table for reliable output */ ANALYZE typetest1; /* * Cases that should be pushed down. */ SELECT t1.id, t2.id FROM typetest1 t1, typetest1 t2 WHERE t1.c = t2.c ORDER BY t1.id, t2.id; id | id ----+---- 1 | 1 3 | 3 4 | 4 (3 rows) EXPLAIN (COSTS off) SELECT t1.id, t2.id FROM typetest1 t1, typetest1 t2 WHERE t1.c = t2.c; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Oracle query: SELECT /*e4c88236bdfe66c8af8d114b6f7fa761*/ r1."ID", r2."ID" FROM ("TYPETEST1" r1 INNER JOIN "TYPETEST1" r2 ON (r1."C" = r2."C")) (2 rows) SELECT length(t1.lb), length(t2.lc) FROM typetest1 t1 JOIN typetest1 t2 ON (t1.id + t2.id = 2) ORDER BY t1.id, t2.id; length | length --------+-------- 5 | 22 (1 row) EXPLAIN (COSTS off) SELECT length(t1.lb), length(t2.lc) FROM typetest1 t1 JOIN typetest1 t2 ON (t1.id + t2.id = 2); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Oracle query: SELECT /*41dba429f976416ced3083fc16f51daf*/ r1."LB", r2."LC" FROM ("TYPETEST1" r1 INNER JOIN "TYPETEST1" r2 ON ((r1."ID" + r2."ID") = 2)) (2 rows) /* ORDER BY does not get pushed down */ SELECT t1.id, t2.id FROM typetest1 t1 JOIN typetest1 t2 USING (ts, num) ORDER BY t1.id ORDER BY t1.id, t2.id; ERROR: syntax error at or near "ORDER" at character 128 EXPLAIN (COSTS off) SELECT t1.id, t2.id FROM typetest1 t1 JOIN typetest1 t2 USING (ts, num) ORDER BY t1.id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort Sort Key: t1.id -> Foreign Scan Oracle query: SELECT /*240ca65fa32ea3de4d92ab6d67da14a3*/ r1."ID", r2."ID" FROM ("TYPETEST1" r1 INNER JOIN "TYPETEST1" r2 ON (r1."TS" = r2."TS") AND (r1."NUM" = r2."NUM")) (4 rows) /* natural join */ SELECT id FROM typetest1 NATURAL JOIN shorty ORDER BY id; id ---- 1 3 4 (3 rows) EXPLAIN (COSTS off) SELECT id FROM typetest1 NATURAL JOIN shorty; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan Oracle query: SELECT /*02cefdbe3ebea3a0287ae1c534120aec*/ r1."ID" FROM ("TYPETEST1" r1 INNER JOIN "TYPETEST1" r2 ON (r1."ID" = r2."ID") AND (r1."C" = r2."C")) (2 rows) /* table with column that does not exist in Oracle (should become NULL) */ SELECT t1.id, t2.x FROM typetest1 t1 JOIN longy t2 ON t1.c = t2.c ORDER BY t1.id, t2.x; WARNING: column number 19 of foreign table "longy" does not exist in foreign Oracle table, will be replaced by NULL id | x ----+--- 1 | 3 | 4 | (3 rows) EXPLAIN (COSTS off) SELECT t1.id, t2.x FROM typetest1 t1 JOIN longy t2 ON t1.c = t2.c; WARNING: column number 19 of foreign table "longy" does not exist in foreign Oracle table, will be replaced by NULL QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan Oracle query: SELECT /*8081bf2a10cd5ae8f08211f18a58ca48*/ r1."ID" FROM ("TYPETEST1" r1 INNER JOIN "TYPETEST1" r2 ON (r1."C" = r2."C")) (2 rows) /* * Cases that should not be pushed down. */ /* join expression cannot be pushed down */ EXPLAIN (COSTS off) SELECT t1.id, t2.id FROM typetest1 t1, typetest1 t2 WHERE t1.lc = t2.lc; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Hash Join Hash Cond: (t1.lc = t2.lc) -> Foreign Scan on typetest1 t1 Oracle query: SELECT /*642c23ec045edf416286adc5ac67d720*/ r1."ID", r1."LC" FROM "TYPETEST1" r1 -> Hash -> Foreign Scan on typetest1 t2 Oracle query: SELECT /*9f21ed71d712604b726bed252205dc37*/ r2."ID", r2."LC" FROM "TYPETEST1" r2 (7 rows) /* only one join condition cannot be pushed down */ EXPLAIN (COSTS off) SELECT t1.id, t2.id FROM typetest1 t1 JOIN typetest1 t2 ON t1.vc = t2.vc AND t1.lb = t2.lb; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Hash Join Hash Cond: (((t1.vc)::text = (t2.vc)::text) AND (t1.lb = t2.lb)) -> Foreign Scan on typetest1 t1 Oracle query: SELECT /*16547ec2089bc2efd2875cfa888d4952*/ r1."ID", r1."VC", r1."LB" FROM "TYPETEST1" r1 -> Hash -> Foreign Scan on typetest1 t2 Oracle query: SELECT /*cf65033e53306af698c0b198d445b45a*/ r2."ID", r2."VC", r2."LB" FROM "TYPETEST1" r2 (7 rows) /* condition on one table needs to be evaluated locally */ EXPLAIN (COSTS off) SELECT max(t1.id), min(t2.id) FROM typetest1 t1 JOIN typetest1 t2 ON t1.fl = t2.fl WHERE t1.vc || 'x' = 'shortx'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Aggregate -> Hash Join Hash Cond: (t2.fl = t1.fl) -> Foreign Scan on typetest1 t2 Oracle query: SELECT /*dba88384ad64524d02589fd3bdcedd4e*/ r2."ID", r2."FL" FROM "TYPETEST1" r2 -> Hash -> Foreign Scan on typetest1 t1 Filter: (((vc)::text || 'x'::text) = 'shortx'::text) Oracle query: SELECT /*f0e10b1797a5d532ee14127a96556d46*/ r1."ID", r1."VC", r1."FL" FROM "TYPETEST1" r1 (9 rows) /* outer joins */ EXPLAIN (COSTS off) SELECT t1.id, t2.id FROM typetest1 t1 LEFT JOIN typetest1 t2 ON t1.d = t2.d; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Hash Left Join Hash Cond: (t1.d = t2.d) -> Foreign Scan on typetest1 t1 Oracle query: SELECT /*ad51e4762dd1a5d210370c477b8596af*/ r1."ID", r1."D" FROM "TYPETEST1" r1 -> Hash -> Foreign Scan on typetest1 t2 Oracle query: SELECT /*ec272bdf979278cf22710fbd2595dd78*/ r2."ID", r2."D" FROM "TYPETEST1" r2 (7 rows) EXPLAIN (COSTS off) SELECT t1.id, t2.id FROM typetest1 t1 RIGHT JOIN typetest1 t2 ON t1.d = t2.d; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Hash Left Join Hash Cond: (t2.d = t1.d) -> Foreign Scan on typetest1 t2 Oracle query: SELECT /*ec272bdf979278cf22710fbd2595dd78*/ r2."ID", r2."D" FROM "TYPETEST1" r2 -> Hash -> Foreign Scan on typetest1 t1 Oracle query: SELECT /*ad51e4762dd1a5d210370c477b8596af*/ r1."ID", r1."D" FROM "TYPETEST1" r1 (7 rows) EXPLAIN (COSTS off) SELECT t1.id, t2.id FROM typetest1 t1 FULL JOIN typetest1 t2 ON t1.d = t2.d; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Hash Full Join Hash Cond: (t1.d = t2.d) -> Foreign Scan on typetest1 t1 Oracle query: SELECT /*ad51e4762dd1a5d210370c477b8596af*/ r1."ID", r1."D" FROM "TYPETEST1" r1 -> Hash -> Foreign Scan on typetest1 t2 Oracle query: SELECT /*ec272bdf979278cf22710fbd2595dd78*/ r2."ID", r2."D" FROM "TYPETEST1" r2 (7 rows) /* cross join */ EXPLAIN (COSTS off) SELECT t1.id, t2.id FROM typetest1 t1 CROSS JOIN typetest1 t2; QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop -> Foreign Scan on typetest1 t1 Oracle query: SELECT /*709894645e7b46ee7c82f53e6eeb7cac*/ r1."ID" FROM "TYPETEST1" r1 -> Materialize -> Foreign Scan on typetest1 t2 Oracle query: SELECT /*6d4435ad754d35ca0ba5c53fccf72be5*/ r2."ID" FROM "TYPETEST1" r2 (6 rows) /* semi-join */ EXPLAIN (COSTS off) SELECT t1.id FROM typetest1 t1 WHERE EXISTS (SELECT 1 FROM typetest1 t2 WHERE t1.d = t2.d); QUERY PLAN ------------------------------------------------------------------------------------------------------- Hash Semi Join Hash Cond: (t1.d = t2.d) -> Foreign Scan on typetest1 t1 Oracle query: SELECT /*ad51e4762dd1a5d210370c477b8596af*/ r1."ID", r1."D" FROM "TYPETEST1" r1 -> Hash -> Foreign Scan on typetest1 t2 Oracle query: SELECT /*82066679003dba5f6669f772ada483df*/ r2."D" FROM "TYPETEST1" r2 (7 rows) /* anti-join */ EXPLAIN (COSTS off) SELECT t1.id FROM typetest1 t1 WHERE NOT EXISTS (SELECT 1 FROM typetest1 t2 WHERE t1.d = t2.d); QUERY PLAN ------------------------------------------------------------------------------------------------------- Hash Anti Join Hash Cond: (t1.d = t2.d) -> Foreign Scan on typetest1 t1 Oracle query: SELECT /*ad51e4762dd1a5d210370c477b8596af*/ r1."ID", r1."D" FROM "TYPETEST1" r1 -> Hash -> Foreign Scan on typetest1 t2 Oracle query: SELECT /*82066679003dba5f6669f772ada483df*/ r2."D" FROM "TYPETEST1" r2 (7 rows) /* UPDATE statement */ EXPLAIN (COSTS off) UPDATE typetest1 t1 SET c = NULL FROM typetest1 t2 WHERE t1.vc = t2.vc AND t2.num = 3.14159; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Update on typetest1 t1 Oracle statement: UPDATE "TYPETEST1" SET "C" = :p3 WHERE "ID" = :k1 -> Hash Join Hash Cond: ((t1.vc)::text = (t2.vc)::text) -> Foreign Scan on typetest1 t1 Oracle query: SELECT /*5acd3c53c4e9053a5bdcc9573c1042a7*/ r1."ID", r1."NC", r1."VC", r1."NVC", r1."LC", r1."R", r1."U", r1."LB", r1."LR", r1."B", r1."NUM", r1."FL", r1."DB", r1."D", r1."TS", r1."IDS", r1."IYM" FROM "TYPETEST1" r1 FOR UPDATE -> Hash -> Foreign Scan on typetest1 t2 Oracle query: SELECT /*68723df18097bd201061b3198f3aad61*/ r2."ID", r2."C", r2."NC", r2."VC", r2."NVC", r2."LC", r2."R", r2."U", r2."LB", r2."LR", r2."B", r2."NUM", r2."FL", r2."DB", r2."D", r2."TS", r2."IDS", r2."IYM" FROM "TYPETEST1" r2 WHERE (r2."NUM" = 3.14159) (9 rows) /* only part of a three-way join will be pushed down */ EXPLAIN (COSTS off) SELECT t1.id, t3.id FROM typetest1 t1 JOIN typetest1 t2 USING (nvc) JOIN typetest1 t3 ON t2.db = t3.db; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join Hash Cond: (t2.db = t3.db) -> Foreign Scan Oracle query: SELECT /*7f0912cff1bb323a900a82da91e0ccc6*/ r1."ID", r2."DB" FROM ("TYPETEST1" r1 INNER JOIN "TYPETEST1" r2 ON (r1."NVC" = r2."NVC")) -> Hash -> Foreign Scan on typetest1 t3 Oracle query: SELECT /*fe17cf22393ed6558ecef577b153a7fe*/ r4."ID", r4."DB" FROM "TYPETEST1" r4 (7 rows) /* * Cost estimates. */ /* delete statistics */ DELETE FROM pg_statistic WHERE starelid = 'typetest1'::regclass; UPDATE pg_class SET relpages = 0, reltuples = 0.0 WHERE oid = 'typetest1'::regclass; /* default costs */ EXPLAIN SELECT t1.id, t2.id FROM typetest1 t1, typetest1 t2 WHERE t1.c = t2.c; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan (cost=10000.00..20000.00 rows=1000 width=8) Oracle query: SELECT /*e4c88236bdfe66c8af8d114b6f7fa761*/ r1."ID", r2."ID" FROM ("TYPETEST1" r1 INNER JOIN "TYPETEST1" r2 ON (r1."C" = r2."C")) (2 rows) /* gather statistics */ ANALYZE typetest1; /* costs with statistics */ EXPLAIN SELECT t1.id, t2.id FROM typetest1 t1, typetest1 t2 WHERE t1.c = t2.c; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan (cost=10000.00..10030.00 rows=3 width=8) Oracle query: SELECT /*e4c88236bdfe66c8af8d114b6f7fa761*/ r1."ID", r2."ID" FROM ("TYPETEST1" r1 INNER JOIN "TYPETEST1" r2 ON (r1."C" = r2."C")) (2 rows) EXPLAIN SELECT t1.id, t2.id FROM typetest1 t1, typetest1 t2 WHERE t1.c <> t2.c; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan (cost=10000.00..10060.00 rows=6 width=8) Oracle query: SELECT /*ce3ff3c34bea17a42733aa0eaa5b183e*/ r1."ID", r2."ID" FROM ("TYPETEST1" r1 INNER JOIN "TYPETEST1" r2 ON (r1."C" <> r2."C")) (2 rows)