search

credativ / informix_fdw

Foreign Data Wrapper for Informix Databases
Other
28 stars 5 forks source link

Is it possible to include the fetch_size option in the server or table definition in informix_fdw? #30

Closed 346052 closed 2 years ago

346052 commented 3 years ago

I'm working with a 36 million records table in Informix into PostgreSQL 11 with informix_fdw REL0_5_0. At the PostgreSQL side, I defined a local table with partitions by range, pointing to foreign tables of Informix. There are indexes on both sides: Informix table and PostgreSQL table, but the querie's time consume up to 3 minutes.

Is it possible to include the "fetch_size" option in the server or table definition in order to obtain performance improvements?

Or, do you have any suggestions to improve performance?

Kind Regards Javier

psoo commented 3 years ago

Hmm, what i can imagine is that the queries pushed down to the foreign server aren't qualified and retrieve all tuples to be scanned on the local side. You can provide the EXPLAIN ANALYZE here, which should show us if predicate pushdown is an option here or not.

That said, i'm not sure i get what you are after. A fetch size buffer usually protects against memory overflows when selecting a large number of tuples. It doesn't help you performance wise. There is the fetch array in ESQL/C which helps to decrease the network latency when fetching large batches of tuples, not sure if that's what you mean.