Great explanation. Thank you so much Laurenz Armand > On Nov 16, 2017, at 1:13 AM, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
