PostgreSQL too slow when using oracle_fdw comparison operations in Oracle's queries

Question

When Oracle date type is varchar2, when executing a query in PostgreSQL through oracle_fdw, querying using comparison operator >=, index cannot be used in this condition.

Index cannot be used:

>= to_char(now() - interval '7' day, 'YYYYMMDD')

Output: a.date, a.cusno
Filter: ((a.date)::date >= ((now() - '07 days'::interval day))::date)
                    Oracle query: SELECT /*a05802d21e2d4cec93da21f1abf9ffbb*/ r1."DATE", r1."CUSNO" FROM "ORACLEDB"."CXXXINFO" r1 WHERE (r1."CUSNO" = '12345')
                    Oracle plan: SELECT STATEMENT
                    Oracle plan:   TABLE ACCESS FULL CXXXINFO  (filter "R1"."CUSNO"='12345')

But = in this condition, index is used.

Index be used:

= to_char(now() - interval '7' day, 'YYYYMMDD')

Output: a.date, a.cusno
Oracle query: SELECT /*b7552642e64dd971ba5293a42d581661*/ r1."DATE", r1."CUSNO"
WHERE (r1."CUSNO" = '12345') AND (r1."DATE" = to_char(((CAST (:now AS
TIMESTAMP WITH TIME ZONE)) - INTERVAL '07 00:00:00.000000' DAY(9) TO
SECOND(6)), 'YYYYMMDD'))
Oracle plan: SELECT STATEMENT
Oracle plan:   TABLE ACCESS BY INDEX ROWID CXXXINFO
Oracle plan:     INDEX SKIP SCAN CXXXINFO_01 (condition "R1"."CUSNO"='12345' AND "R1"."DATE"=TO_CHAR(CAST(:NOW AS
TIMESTAMP WITH TIME ZONE)-INTERVAL'+000000007 00:00:00.000000' DAY(9)
TO SECOND(6),'YYYYMMDD'))(filter "R1"."DATE"=TO_CHAR(CAST(:NOW AS TIMESTAMP WITH TIME > ZONE)-INTERVAL'+000000007 00:00:00.000000' DAY(9) TO
> SECOND(6),'YYYYMMDD'))

May I know what is the difference?

I am currently using version Oracle 19.0.2

PostgreSQl oracle_fdw 2.2

and PostgreSQL 12.7

Answer 1

The problem is that you store your dates as strings.

oracle_fdw will push down = and <> comparisons, but not <, <=, > and >=. This is because such comparisons depend on the collation, and collations can be different in Oracle and PostgreSQL, even if they are both English. That could mean that the query result might be different, depending on whether <= is pushed down or not, which is not acceptable.

Use DATE instead of VARCHAR2. Perhaps you can create an Oracle view that converts the string to a DATE.