Number of fetched rows limit is pushed down incorrectly

[gytune74]

Following query result is inaccurate:

SELECT "acfilt111"."ID", "acfilt111"."CKL"
FROM "OGYREV"."ONAL" "acfilt111"
         LEFT OUTER JOIN "OGYREV"."ONTIP" "B0" ON "acfilt111"."TIP_ID" = "B0"."ID"
         LEFT OUTER JOIN "OGYREV"."ONTIP" "C0" ON "B0"."FKOD_ID" = "C0"."ID"
WHERE "acfilt111"."CKL" = 42
  AND (((((STRPOS(LOWER("C0"."KOD"), '9433') - 1 + 1) - 1) >=  0 ) OR
        (((STRPOS(LOWER("acfilt111"."CIM"), '9433') - 1 + 1) - 1) >=  0 )) OR
       ("acfilt111"."IZON" =  9433 ))
  AND EXISTS(SELECT *
             FROM "OGYREV"."BIZONAL" "bizottsagAltalTargyaltIromany"
             WHERE "bizottsagAltalTargyaltIromany"."ONAL_ID" = "acfilt111"."ID"
               AND "bizottsagAltalTargyaltIromany"."BIZ_ID" =  2742536 )
    FETCH NEXT 2 ROWS ONLY;

as limit is pushed down into the EXISTS expression. Every table is Oracle foreign table.

|Limit  (cost=30000.00..30098.15 rows=2 width=10) (actual time=32.022..32.023 rows=0 loops=1)                                                                                                                               |
|  ->  Nested Loop Left Join  (cost=30000.00..372164.09 rows=6972 width=10) (actual time=32.020..32.021 rows=0 loops=1)                                                                                                     |
|        Join Filter: ("ONTIP"."FKOD_ID" = "ONTIP_1"."ID")                                                                                                                                                                  |
|        Rows Removed by Join Filter: 4                                                                                                                                                                                     |
|        Filter: (((((strpos(lower(("ONTIP_1"."KOD")::text), '9433'::text) - 1) + 1) - 1) >= 0) OR ((((strpos(lower(("ONAL"."CIM")::text), '9433'::text) - 1) + 1) - 1) >= 0) OR ("ONAL"."IZON" = 9433))                    |
|        Rows Removed by Filter: 2                                                                                                                                                                                          |
|        ->  Nested Loop Semi Join  (cost=20000.00..183411.59 rows=2500 width=538) (actual time=16.643..31.573 rows=2 loops=1)                                                                                              |
|              Join Filter: ("ONAL"."ID" = "BIZONAL"."ONAL_ID")                                                                                                                                                             |
|              Rows Removed by Join Filter: 18947                                                                                                                                                                           |
|              ->  Foreign Scan  (cost=10000.00..60000.00 rows=5000 width=538) (actual time=10.236..28.303 rows=9475 loops=1)                                                                                               |
|                    Oracle query: SELECT /*eeac0ccb9abf5eb6*/ r7."ID", r7."CKL", r7."CIM", r7."IZON", r8."FKOD_ID" FROM ("OGYREV"."ONAL" r7 LEFT JOIN "OGYREV"."ONTIP" r8 ON (r7."TIP_ID" = r8."ID")) WHERE (r7."CKL" = 42)|
|              ->  Materialize  (cost=10000.00..20005.00 rows=1000 width=8) (actual time=0.000..0.000 rows=2 loops=9475)                                                                                                    |
|                    ->  Foreign Scan on "BIZONAL"  (cost=10000.00..20000.00 rows=1000 width=8) (actual time=0.505..0.515 rows=2 loops=1)                                                                                   |
|                          Oracle query: SELECT /*873020336d7a8837*/ r10."BIZ_ID", r10."ONAL_ID" FROM "OGYREV"."BIZONAL" r10 WHERE (r10."BIZ_ID" = 2742536) FETCH FIRST 2 ROWS ONLY                                         |
|        ->  Materialize  (cost=10000.00..20005.00 rows=1000 width=20) (actual time=0.217..0.218 rows=2 loops=2)                                                                                                            |
|              ->  Foreign Scan on "ONTIP" "ONTIP_1"  (cost=10000.00..20000.00 rows=1000 width=20) (actual time=0.432..0.434 rows=2 loops=1)                                                                                |
|                    Oracle query: SELECT /*4cf82c2503ee19fd*/ r9."ID", r9."KOD" FROM "OGYREV"."ONTIP" r9 FETCH FIRST 2 ROWS ONLY                                                                                           |
|Planning Time: 3.279 ms                                                                                                                                                                                                    |
|Execution Time: 32.129 ms                                                                                                                                                                                                  |

The problem is here:
| Oracle query: SELECT /873020336d7a8837/ r10."BIZ_ID", r10."ONAL_ID" FROM "OGYREV"."BIZONAL" r10 WHERE (r10."BIZ_ID" = 2742536) FETCH FIRST 2 ROWS ONLY |

Also the LEFT JOIN is problematic as well.

The version info:

oracle_fdw 2.6.0, PostgreSQL 16.3, Oracle client 19.15.0.0.0, ORACLE_HOME=/usr/lib/oracle/client

Although 2.7 may fix this as there is no existing order by clause in the EXISTS expression this is not the correct way of pushing down the limit. It is suspicious that every subquery is limited by the outer limit but we did not check it.

[laurenz]

Nasty. I will investigate that.

[laurenz]

Ah, you are using an old version of oracle_fdw. Yes, that won't reproduce with the current version, since there is no ORDER BY. I'll try to find a reproducer with ORDER BY. Perhaps you can try as well...

[laurenz]

I found a reproducer with the regression test database:

EXPLAIN (COSTS OFF)
SELECT c
FROM typetest1 AS t1
WHERE EXISTS (SELECT FROM typetest1 AS t2
              WHERE t2.id = t1.id
              ORDER BY t2.id)
ORDER BY t1.id
LIMIT 2;
                                                                         QUERY PLAN                                                                         
════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 Limit
   ->  Merge Semi Join
         Merge Cond: (t1.id = t2.id)
         ->  Foreign Scan on typetest1 t1
               Oracle query: SELECT /*399a7079bb5b1d6*/ r1."ID", r1."C" FROM "SCOTT"."TYPETEST1" r1 ORDER BY r1."ID" ASC NULLS LAST FETCH FIRST 2 ROWS ONLY
         ->  Foreign Scan on typetest1 t2
               Oracle query: SELECT /*806b804e56a28540*/ r2."ID" FROM "SCOTT"."TYPETEST1" r2 ORDER BY r2."ID" ASC NULLS LAST FETCH FIRST 2 ROWS ONLY
(7 rows)

[gytune74]

Thank you for the quick answer. We will try the new (2.7) version.

[laurenz]

I couldn't come up with a better fix than removing ORDER BY pushdown altogether.

[laurenz]

It is enough to disable LIMIT pushdown...