Re: foreign tables query performance using postgres_fdw

Great explanation. Thank you so much Laurenz

Armand
> On Nov 16, 2017, at 1:13 AM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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