Speed issue about oracle_fdw HOT 5 CLOSED

Options are FDW-specific, so you cannot use use_remote_estimate (and it wouldn't help in this case).

You need an ORDER BY if you want to push down LIMIT, e.g.

SELECT * FROM ftab ORDER BY id LIMIT 1;

But I think it would be simpler to query for a primary key that doesn't exist:

SELECT * FROM ftab WHERE id < 0;

from oracle_fdw.

thank you @laurenz for your quick response.
I've also tried this before:
SELECT * FROM ftab ORDER BY id LIMIT 1;
but I was surprised that the time needed is the same as
SELECT * FROM ftab LIMIT 1; - around 45 seconds

Any ideas why?

The last option works - it is fast, but you need to know that there is an id field in the table, unfortunatelly most of my tables do not have id column but the name can be almost anything - so this is not universal for all my tables.

from oracle_fdw.

It may be that the reason it doesn't work is that you are using an old version of oracle_fdw.
Or id is a string column.

from oracle_fdw.

Yes, I'm using 2.5.0 because there is a bug in 2.6.0, which you planed to resolve in 2.7.0

    The memory for LOB locators didn't get released before the end of the
    transaction, so running many statements in a single transaction could cause
    out-of-memory errors and server crashes.
    Report by "JosefMachytkaNetApp".```

I even tried to install the master branch about a month ago, but the issue was still there. So upgrade is not an option for now.

Anyway, thanks for you explanations and help, I'm closing this now since this is a very specific issue. 

from oracle_fdw.

You can use the current development version.
But be warned that there is a problem with Oracle 21, see #643.
Doesn't look like I'll be able to work around that one.

from oracle_fdw.