Re: foreign tables query performance using postgres_fdw

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