Speed issue

[mastnym]

Hi there, I have an oracle_fdw like this:

                                      oracle_diag                                       
----------------------------------------------------------------------------------------
 oracle_fdw 2.5.0, PostgreSQL 15.5, Oracle client 21.12.0.0.0, Oracle server 21.0.0.0.0

my problem is when I issue this query (table is a foreign table): select * from sch.table where id = 1; it takes about 4ms to complete, but this query select * from sch.table limit 1; takes about 45 seconds to complete.

I kind of understand why. I used explain and the second query returns all the rows (circa 65k) and the first one only the row with id = 1

I don't have any control over the source oracle database and the owner sometimes changes the structure of the tables, column type etc. so I thought I could make a probe which goes thru all my foreign tables and executes a universal select to see if the foreign table works. The problem is that I have a 100 foreign tables and each select * from sch.table limit 1; takes more than 10 secs making this very slow.

I've tried to ANALYZE each table, but it did not help with the speed at all. Also use_remote_estimate option of remote server is not supported on oracle_fdw.

Does anybody have any suggestions, how to have a universal query for all 100+ tables, which will be fast?

[laurenz]

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;

[mastnym]

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.

[laurenz]

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.

[mastnym]

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. 

[laurenz]

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.