How to quickly delete Foreign Table in postgres?

Question

PostgreSQL

Hello!

The task is to connect and configure data exchange from Oracle to Postgresql. For this, I decided to use oracle fwd , which implements the link through Foreign Table. There are 2 issues globally:

1) Can't remove foreign table from postgres with query

DROP FOREIGN TABLE IF EXISTS название таблицы CASCADE

The request hangs for more than 1 hour and, as a result, the table is not deleted. Is there a way to drop a table? This behavior is observed only on those tables that have already been used and received more than 1 million. lines.

2) If you make an elementary request , then everything is executed in 600ms. And if you add any ORDER BY to this query, then the query execution takes 10 or more minutes (there are more than 20 million rows in the table). Why is this happening? And how can you speed things up? PS or2pg - not used due to data customization during transfer and the need to start the exchange on a certain condition with an additional data sample. SELECT * FROM fwd_orders LIMIT 500

Answer 1

Can't remove foreign table from postgres with query

Look in pg_blocking_pids to see who's in the way. Do something with these connections (pg_terminate_backend for example)

If you make an elementary query SELECT * FROM fwd_orders LIMIT 500, then everything is executed in 600ms. And if any ORDER BY is added to this query, then the query execution takes 10 or more minutes (there are more than 20 million rows in the table). Why is this happening? And how can you speed things up?

View explain (verbose) for what is considered local and what is passed to the remote base.
In particular, check with the oracle fdw extension you are using to see if push-down for order by is implemented there. According to the description, it seems that there is no such thing, and therefore the entire table is subtracted and sorted locally.