Hi all, I'd need some help to understand how Postgresql Foreign Data Wrapper chooses the plan to execute a query, since the execution time 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 foreign table. 2015-01-14 13:53:31 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG: statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ 2015-01-14 13:53:31 GMT 192.168.2.31(58031) mdn INFOLOG 0 54b64297.327c - LOG: execute <unnamed>: DECLARE c1 CURSOR FOR SELECT 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 foreign table points to a VIEW. Unfortunately, I do need a VIEW.... -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
