Re: [ADMIN] foreign tables query performance using postgres_fdw - Mailing list pgsql-admin

Great explanation. Thank you so much Laurenz


Armand
> On Nov 16, 2017, at 1:13 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> armand pirvu wrote:
>> I am facing fhe following issue and trying to understand what is wrong. My setup below
>>
>> CREATE EXTENSION postgres_fdw;
>>
>> CREATE FOREIGN TABLE dim_item
>> (
> [...]
>>    item_id character varying(100) NOT NULL,
> [...]
>> )
>> SERVER birst_levreg OPTIONS (table_name 'dim_item');
>> grant select,update,delete,insert on  dim_item_birst to public;
>>
>> analyze dim_item;
>>
>> On remote server I already have
>>
>> CREATE TABLE dim_item (
>>    item_id character varying(100) NOT NULL,
>> );
>> ALTER TABLE ONLY dim_item
>>    ADD CONSTRAINT dim_item_pkey PRIMARY KEY (item_id);
>> CREATE INDEX dim_item_idx ON dim_item USING btree (client_id, update_datetime);
>>
>>
>> on remote server
>> explain analyze select * from dim_item where item_id='156GIEPE14CX-B';
>> Index Scan using dim_item_pkey on dim_item  (cost=0.42..2.44 rows=1 width=157) (actual time=0.134..0.135 rows=1
loops=1)
>>   Index Cond: ((item_id)::text = '156GIEPE14CX-B'::text)
>> Planning time: 1.836 ms
>> Execution time: 0.333 ms
>>
>> on local server-foreign table
>> explain (analyze,verbose)  select * from dim_item where item_id='156GIEPE14CX-B';
>>                                                                                                              QUERY
PLAN                       
>>
>>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> -------------------------------------------------------------------------------------------------------------------
>> Foreign Scan on csischema.dim_item_birst  (cost=100.00..2731.33 rows=1 width=157) (actual time=1.053..1.054 rows=1
loops=1)
>>   Output: show_id, client_id, item_id, item_type, item_code, item_name, item_group, location_id, session_start_date,
session_end_date,credit_hours, total_allotted, upda 
>> te_datetime, is_deleted, item_source
>>   Remote SQL: SELECT show_id, client_id, item_id, item_type, item_code, item_name, item_group, location_id,
session_start_date,session_end_date, credit_hours, total_all 
>> otted, update_datetime, is_deleted, item_source FROM csischema.dim_item WHERE ((item_id = '156GIEPE14CX-B'::text))
>> Planning time: 0.222 ms
>> Execution time: 1.842 ms
>> (5 rows)
> [...]
>> So what am I missing ? Why do I get a scan using foreign table as opposed to a pkey scan ?
>
> Everything is in perfect order, and the index will be used (you see
> that the execution time is low).
>
> A foreign scan is different from a sequential scan.
> It does not describe *how* the query is executed on the foreign server, it
> only indicates *that* a query is executed on the foreign server.
> Since you can see the WHERE condition in remote query inthe EXPLAIN (VERBOSE)
> output, it is pushed down to the foreign server, and there is every reason to
> assume that an index scan will be used there.
>
> Yours,
> Laurenz Albe



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin