pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedforeign tables query performance using postgres_fdw
3+ messages / 2 participants
[nested] [flat]
* foreign tables query performance using postgres_fdw
@ 2017-11-15 20:59 armand pirvu <armand.pirvu@gmail.com>
2017-11-16 07:13 ` Re: foreign tables query performance using postgres_fdw Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 3+ messages in thread
From: armand pirvu @ 2017-11-15 20:59 UTC (permalink / raw)
To: pgsql-admin
Hi
I apologize beore hand if it is in the wrong list and if please advise which one to post in.
I am facing fhe following issue and trying to understand what is wrong. My setup below
1 -
CREATE EXTENSION postgres_fdw;
2 -
CREATE SERVER birst_levreg
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '172.16.26.4', dbname 'birstdb');
CREATE USER MAPPING FOR csidba
SERVER birst_levreg
OPTIONS (user 'csidba', password 'secretpwd');
CREATE FOREIGN TABLE dim_item
(
show_id character varying(100),
client_id integer,
item_id character varying(100) NOT NULL,
item_type character varying(100),
item_code character varying(100),
item_name character varying(250),
item_group character varying(100),
location_id character varying(100),
session_start_date timestamp without time zone,
session_end_date timestamp without time zone,
credit_hours numeric,
total_allotted integer,
update_datetime timestamp without time zone,
is_deleted character varying(10),
item_source character varying(1)
)
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 (
show_id character varying(100),
client_id integer,
item_id character varying(100) NOT NULL,
item_type character varying(100),
item_code character varying(100),
item_name character varying(250),
item_group character varying(100),
location_id character varying(100),
session_start_date timestamp without time zone,
session_end_date timestamp without time zone,
credit_hours numeric,
total_allotted integer,
update_datetime timestamp without time zone,
is_deleted character varying(10),
item_source character varying(1)
);
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);
3 -
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 select * from dim_item where item_id='156GIEPE14CX-B';
Foreign Scan on dim_item_birst (cost=100.00..105.53 rows=1 width=1934) (actual time=1.049..1.050 rows=1 loops=1)
Planning time: 0.226 ms
Execution time: 1.626 ms
Even if I use use_remote_estimate 'true' in the server and foreign table definition the same result
I did also
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)
And on remote server
explain analyze
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_allotted, update_datetime, is_deleted, item_source FROM csischema.dim_item WHERE ((item_id = '156GIEPE14CX-B'::text))
--------------------------------------------------------------------------------------------------------------------------
Index Scan using dim_item_pkey on dim_item (cost=0.42..2.44 rows=1 width=157) (actual time=0.146..0.147 rows=1 loops=1)
Index Cond: ((item_id)::text = '156GIEPE14CX-B'::text)
Planning time: 1.949 ms
Execution time: 0.456 ms
(4 rows)
So what am I missing ? Why do I get a scan using foreign table as opposed to a pkey scan ?
Shall I understand that in fact the plan I am seing on the local server I should just ignore it, since the processing is in fact done on the remote(foreign) server and the difference between costs is nothing else but the overhead in between ?
From the manual I got this
"When use_remote_estimate is true, postgres_fdw obtains row count and cost estimates from the remote server and then adds fdw_startup_cost and fdw_tuple_cost to the cost estimates. When use_remote_estimate is false, postgres_fdw performs local row count and cost estimation and then adds fdw_startup_cost and fdw_tuple_cost to the cost estimates. This local estimation is unlikely to be very accurate unless local copies of the remote table's statistics are available. Running ANALYZE on the foreign table is the way to update the local statistics; this will perform a scan of the remote table and then calculate and store statistics just as though the table were local. Keeping local statistics can be a useful way to reduce per-query planning overhead for a remote table — but if the remote table is frequently updated, the local statistics will soon be obsolete.
"
Many thanks
Armand
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: foreign tables query performance using postgres_fdw
2017-11-15 20:59 foreign tables query performance using postgres_fdw armand pirvu <armand.pirvu@gmail.com>
@ 2017-11-16 07:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
2017-11-16 13:26 ` Re: foreign tables query performance using postgres_fdw armand pirvu <armand.pirvu@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2017-11-16 07:13 UTC (permalink / raw)
To: armand pirvu <armand.pirvu@gmail.com>; pgsql-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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: foreign tables query performance using postgres_fdw
2017-11-15 20:59 foreign tables query performance using postgres_fdw armand pirvu <armand.pirvu@gmail.com>
2017-11-16 07:13 ` Re: foreign tables query performance using postgres_fdw Laurenz Albe <laurenz.albe@cybertec.at>
@ 2017-11-16 13:26 ` armand pirvu <armand.pirvu@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: armand pirvu @ 2017-11-16 13:26 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-11-16 13:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-15 20:59 foreign tables query performance using postgres_fdw armand pirvu <armand.pirvu@gmail.com>
2017-11-16 07:13 ` Laurenz Albe <laurenz.albe@cybertec.at>
2017-11-16 13:26 ` armand pirvu <armand.pirvu@gmail.com>
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