|
| 1 | +-- Tests on cardinality estimation of FDW-queries: |
| 2 | +-- simple ForeignScan. |
| 3 | +-- JOIN push-down (check push of baserestrictinfo and joininfo) |
| 4 | +-- Aggregate push-down |
| 5 | +-- Push-down of groupings with HAVING clause. |
| 6 | + |
| 7 | +CREATE EXTENSION aqo; |
| 8 | +CREATE EXTENSION postgres_fdw; |
| 9 | +SETaqo.mode='learn'; |
| 10 | +SETaqo.details='true';-- show AQO info for each node and entire query. |
| 11 | +SETaqo.show_hash='false';-- a hash value is system-depended. Ignore it. |
| 12 | + |
| 13 | +DO $d$ |
| 14 | +BEGIN |
| 15 | + EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw |
| 16 | + OPTIONS (dbname'$$||current_database()||$$', |
| 17 | + port'$$||current_setting('port')||$$' |
| 18 | + )$$; |
| 19 | + END; |
| 20 | +$d$; |
| 21 | + |
| 22 | +CREATEUSERMAPPING FOR PUBLIC SERVER loopback; |
| 23 | + |
| 24 | +CREATETABLElocal (xint); |
| 25 | +CREATE FOREIGN TABLE frgn(xint) SERVER loopback OPTIONS (table_name'local'); |
| 26 | +INSERT INTO frgn (x)VALUES (1); |
| 27 | +ANALYZE local; |
| 28 | + |
| 29 | +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)SELECT xFROM frgn; |
| 30 | +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)SELECT xFROM frgn; |
| 31 | + |
| 32 | +-- Push down base filters. |
| 33 | +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)SELECT xFROM frgnWHERE x<10; |
| 34 | +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)SELECT xFROM frgnWHERE x<10; |
| 35 | +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)SELECT xFROM frgnWHERE x<-10;-- AQO ignores constants |
| 36 | + |
| 37 | +DROP EXTENSION aqo; |
| 38 | + |