{"action":"opened","issue":{"url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/87","repository_url":"https://api.github.com/repos/laurenz/oracle_fdw","labels_url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/87/labels{/name}","comments_url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/87/comments","events_url":"https://api.github.com/repos/laurenz/oracle_fdw/issues/87/events","html_url":"https://github.com/laurenz/oracle_fdw/issues/87","id":153813871,"number":87,"title":"pushdown in where/join conditions and passthrough queries","user":{"login":"3kkani","id":19228097,"avatar_url":"https://avatars.githubusercontent.com/u/19228097?v=3","gravatar_id":"","url":"https://api.github.com/users/3kkani","html_url":"https://github.com/3kkani","followers_url":"https://api.github.com/users/3kkani/followers","following_url":"https://api.github.com/users/3kkani/following{/other_user}","gists_url":"https://api.github.com/users/3kkani/gists{/gist_id}","starred_url":"https://api.github.com/users/3kkani/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/3kkani/subscriptions","organizations_url":"https://api.github.com/users/3kkani/orgs","repos_url":"https://api.github.com/users/3kkani/repos","events_url":"https://api.github.com/users/3kkani/events{/privacy}","received_events_url":"https://api.github.com/users/3kkani/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2016-05-09T16:17:26Z","updated_at":"2016-05-09T16:17:26Z","closed_at":null,"body":"Hi Laurenz,\r\n\r\nthanks for the great work you are doing (BTW: I think I have seen/meet you briefly at the pgconf 2015 in Vienna).\r\n\r\nI have a number of questions that I would like to check with you, perhaps resulting in some enhancement requests.\r\n\r\nI am testing oracle FDW in a proof of concept that is meant to let us evaluate postgresql capabilities before migrating some Oracle DBs to PG.\r\n\r\nOne of the test case involves getting/joining data across 3 Oracle DBs - let us say OA, OB and OC - and the postgresql candidate database PP resulting from the migration.\r\n\r\nI managed to setup a solution that is perfoming in an acceptable way as follow:\r\n\r\n1. run a query to identify all the keys we need to process in PP (~few thousands-1M rows)\r\n2. insert into a driving_table@OA the keys resulting from 1 (for large data sets improving insert with arryays or even better cursor binding - would be a substantial improvement)\r\n3. while connected to OA, run a number of distributed queries against OA, OB and OC (via oracle dblinks) and materialize the results in a couple of \"transient\" tables\r\n4. run the query retrieving the final results (\"transient\" tables joined with others) abnd/or materialize as a CTAS in PP\r\n\r\nI tried in many ways to use oracle fdw/postgresql as the main driving point of the querying activity but I couldn't find a way to pushdown to oracle the variables, column values, arrays or cursors and execute the remote query/joins needed to check/retrieve the data.\r\n\r\nDue to the size of the tables accessed in Oracle (~800M rows and ~100-200M in other cases) being forced to pull down all the rows and execute the join in PP is very penalizing.\r\n\r\nBeing able to pushdown to oracle the keys and run the query remotely relying on the oracle optimizer would be much better. \r\n\r\nJust as an example, one of the attempt was to map a remote query as follow:\r\n\r\n`CREATE FOREIGN TABLE ora_user1.my_pidn_v (\r\n\"primaryacc#\" character varying(15),\r\nprotein_acc character varying(15)\r\n) SERVER ENAUNI OPTIONS (table '(\r\n  SELECT d.primaryacc#, c.protein_acc\r\n    FROM dbentry d JOIN cdsfeature c\r\n      ON (d.bioseqid = c.bioseqid)\r\n  WHERE d.statusid = 4 AND c.seqlen > 0\r\n)');`\r\n\r\nI can see the pushdown works beautifully when I use:\r\n\r\n`select cdsf_my.protein_acc\r\n   from ora_user1.my_pidn_v cdsf_my\r\n where cdsf_my.\"primaryacc#\" = 'constant'`\r\n\r\nbut it doesn't when I try to \"force\" nested loops/index scan on the oracle side as follow:\r\n\r\n`CREATE OR REPLACE FUNCTION get_my_pidn (ora_user1.my_pidn_v.\"primaryacc#\"%type) RETURNS SETOF ora_user1.my_pidn_v.protein_acc%type AS\r\n$BODY$\r\nDECLARE\r\n    r_bioseqid ora_user1.dbentry.bioseqid%type;\r\nBEGIN\r\n\r\n    CASE \r\n    WHEN LENGTH ($1) < 12 THEN\r\n    \r\n        RETURN QUERY \r\n          select cdsf_my.protein_acc\r\n            from ora_user1.my_pidn_v cdsf_my\r\n           where cdsf_my.\"primaryacc#\" = $1;\r\n               \r\n    WHEN LENGTH ($1) between 12 and 13 THEN\r\n              \r\n        RETURN QUERY \r\n          select cdsf_my.protein_acc\r\n            from ora_user1.my_pidn_v cdsf_my\r\n           where cdsf_my.\"primaryacc#\" like substr ($1, 1, 6) || '%';\r\n             \r\n    ELSE\r\n      NULL;\r\n    END CASE;\r\n\r\n    RETURN;\r\n END\r\n$BODY$\r\nLANGUAGE plpgsql;`\r\n\r\nPlease let me know if you have any suggestion and/or if there is any chance to improve the pushdown of variables, arrays (or a result set as a cursor variable!) in order to allow the query to be solved remotely.\r\n\r\nMany thanks again for your committment and effort.\r\nMDG."}}