Hi all,
I'd need some help to understand how Postgresql Foreign Data Wrapper chooses the plan to execute a query, since the
executiontime I get is 95 times more than the execution time on the local DB.
I have one Postgresql DB Server (v. 9.3.5) where I defined a VIEW.
If I execute locally this simple query :
INFOLOG=# select * from public.v_mdn_colli_testata where collo='U0019502';
-[ RECORD 1 ]-------+------------------------------
........
........
Time: 104.907 ms
I get, as you can see, an execution time of about 100 msecs
On a second Postgresql DB Server (v 9.3.5), on the same LAN, I defined a foreign table which points to this view.
The same query on the foreign table takes 95 times more... :
mdn=# select * from logimat.v_mdn_colli_testata where collo='U0019502';
-[ RECORD 1 ]-------+------------------------------
......
......
Time: 9887.533 ms
***************************************************************
I repeated the queries several times to get rid of the overhead caused by database connection opening and disk access
time.
I have enabled statements logging and I can see that Postgres FDW chooses to use a CURSOR to execute the query on the
foreigntable.
2015-01-14 13:53:31 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG: statement: START TRANSACTION ISOLATION
LEVELREPEATABLE READ
2015-01-14 13:53:31 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG: execute <unnamed>: DECLARE c1 CURSOR
FORSELECT id, collo, stato, id_spedizione, id_es_rientro, peso, volume, ordine, data, capoconto, conto, causale,
descrizione,tipo, capoconto_v, conto_v, magazzino, tipo_spedizione, data_spedizione, consegna_spedizione, documento,
data_documento,borderau, data_borderau FROM public.v_mdn_colli_testata WHERE ((collo = 'U0019502'::text))
2015-01-14 13:53:31 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG: statement: FETCH 100 FROM c1
2015-01-14 13:53:41 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG: duration: 9887.533 ms
2015-01-14 13:53:41 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG: statement: CLOSE c1
2015-01-14 13:53:41 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG: statement: COMMIT TRANSACTION
I think that the problem is caused by this choice.....
Could you please help me to find how tell Postgres to use a different and faster behaviour?
Thank you all in advance
Marco
P.S. On the local DB I tried to import the data of the view in a unlogged table (create unlogged table as select [view
definition])and then I made the foreign table point to this unlogged table.
In this case the performance was fine, so this makes me think that the cursor is choosen as a method only if the
foreigntable points to a VIEW.
Unfortunately, I do need a VIEW....