From: Laurenz Albe <laurenz.albe@cybertec.at>
To: armand pirvu <armand.pirvu@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: foreign tables query performance using postgres_fdw
Date: Thu, 16 Nov 2017 08:13:20 +0100
Message-ID: <1510816400.2639.9.camel@cybertec.at> (raw)
In-Reply-To: <69F15382-0FDE-4424-A63A-7D42431D0795@gmail.com>
References: <69F15382-0FDE-4424-A63A-7D42431D0795@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, armand.pirvu@gmail.com
Subject: Re: foreign tables query performance using postgres_fdw
In-Reply-To: <1510816400.2639.9.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox