From: | "Cassiano, Marco" <mcassiano(at)manord(dot)com> |
---|---|
To: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Postgresql Foreign Data Wrapper & Query plan |
Date: | 2015-01-22 16:09:39 |
Message-ID: | 1361CEF686657C41A139AD8C3145632B44B3BB90@E2010-MB1.manord.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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....
From | Date | Subject | |
---|---|---|---|
Next Message | Khangelani Gama | 2015-01-23 08:19:22 | PostgreSQL 9.2.4 - please assist with the query |
Previous Message | David Johnston | 2015-01-22 15:57:57 | Re: Re: pg_basebackup bug: base backup is double the size of the database |