[HACKERS] An issue in remote query optimization
От | Abbas Butt |
---|---|
Тема | [HACKERS] An issue in remote query optimization |
Дата | |
Msg-id | CALtH27eUC5pZbrcMCDRv9jFdQwxQH+FQPywX=MwP7Tj6Rb-zaw@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: [HACKERS] An issue in remote query optimization
(Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>)
Re: [HACKERS] An issue in remote query optimization (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
Hi,
Postgres_fdw optimizes remote queries by pushing down the where clause.Create the table on the foreign server and insert some rows in it
create table numbers(a int, b varchar(255));
insert into numbers values(1, 'One');
insert into numbers values(2, 'Two');
insert into numbers values(3, 'Three');
insert into numbers values(4, 'Four');
insert into numbers values(5, 'Five');
insert into numbers values(6, 'Six');
insert into numbers values(7, 'Seven');
insert into numbers values(8, 'Eight');
insert into numbers values(9, 'Nine');
create table numbers(a int, b varchar(255));
insert into numbers values(1, 'One');
insert into numbers values(2, 'Two');
insert into numbers values(3, 'Three');
insert into numbers values(4, 'Four');
insert into numbers values(5, 'Five');
insert into numbers values(6, 'Six');
insert into numbers values(7, 'Seven');
insert into numbers values(8, 'Eight');
insert into numbers values(9, 'Nine');
Step 2:
Create the following objects on the local server
CREATE SERVER pg_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5552', dbname 'test');
CREATE USER MAPPING FOR abbasbutt SERVER pg_server OPTIONS (user 'abbasbutt', password 'abc123');
CREATE FOREIGN TABLE foreign_numbers(a int, b varchar(255)) SERVER pg_server OPTIONS (table_name 'numbers');
create or replace function test_pg_fdw() returns void as $$
DECLARE
n varchar;
BEGIN
FOR x IN 1..9 LOOP
select b into n from foreign_numbers where a=x;
raise notice 'Found number %', n;
end loop;
return;
END
$$ LANGUAGE plpgsql;
CREATE SERVER pg_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5552', dbname 'test');
CREATE USER MAPPING FOR abbasbutt SERVER pg_server OPTIONS (user 'abbasbutt', password 'abc123');
CREATE FOREIGN TABLE foreign_numbers(a int, b varchar(255)) SERVER pg_server OPTIONS (table_name 'numbers');
create or replace function test_pg_fdw() returns void as $$
DECLARE
n varchar;
BEGIN
FOR x IN 1..9 LOOP
select b into n from foreign_numbers where a=x;
raise notice 'Found number %', n;
end loop;
return;
END
$$ LANGUAGE plpgsql;
Step 3:
Run the test:
select test_pg_fdw();
select test_pg_fdw();
Step 4:
Check the output of auto_explain in the local server log
2017-01-31 00:39:25 PST LOG: duration: 8.388 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..111.91 rows=1 width=516)
Output: b
Remote SQL: SELECT b FROM public.numbers WHERE ((a = 1))
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.315 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..111.91 rows=1 width=516)
Output: b
Remote SQL: SELECT b FROM public.numbers WHERE ((a = 2))
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.250 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..111.91 rows=1 width=516)
Output: b
Remote SQL: SELECT b FROM public.numbers WHERE ((a = 3))
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.257 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..111.91 rows=1 width=516)
Output: b
Remote SQL: SELECT b FROM public.numbers WHERE ((a = 4))
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.271 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..111.91 rows=1 width=516)
Output: b
Remote SQL: SELECT b FROM public.numbers WHERE ((a = 5))
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.251 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..114.91 rows=1 width=516)
Output: b
Filter: (foreign_numbers.a = $3)
Remote SQL: SELECT a, b FROM public.numbers
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.246 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..114.91 rows=1 width=516)
Output: b
Filter: (foreign_numbers.a = $3)
Remote SQL: SELECT a, b FROM public.numbers
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.226 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..114.91 rows=1 width=516)
Output: b
Filter: (foreign_numbers.a = $3)
Remote SQL: SELECT a, b FROM public.numbers
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.223 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..114.91 rows=1 width=516)
Output: b
Filter: (foreign_numbers.a = $3)
Remote SQL: SELECT a, b FROM public.numbers
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 13.963 ms plan:
Query Text: select test_pg_fdw();
Result (cost=0.00..0.26 rows=1 width=0)
Output: test_pg_fdw()
2017-01-31 00:39:25 PST LOG: duration: 8.388 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..111.91 rows=1 width=516)
Output: b
Remote SQL: SELECT b FROM public.numbers WHERE ((a = 1))
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.315 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..111.91 rows=1 width=516)
Output: b
Remote SQL: SELECT b FROM public.numbers WHERE ((a = 2))
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.250 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..111.91 rows=1 width=516)
Output: b
Remote SQL: SELECT b FROM public.numbers WHERE ((a = 3))
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.257 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..111.91 rows=1 width=516)
Output: b
Remote SQL: SELECT b FROM public.numbers WHERE ((a = 4))
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.271 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..111.91 rows=1 width=516)
Output: b
Remote SQL: SELECT b FROM public.numbers WHERE ((a = 5))
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.251 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..114.91 rows=1 width=516)
Output: b
Filter: (foreign_numbers.a = $3)
Remote SQL: SELECT a, b FROM public.numbers
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.246 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..114.91 rows=1 width=516)
Output: b
Filter: (foreign_numbers.a = $3)
Remote SQL: SELECT a, b FROM public.numbers
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.226 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..114.91 rows=1 width=516)
Output: b
Filter: (foreign_numbers.a = $3)
Remote SQL: SELECT a, b FROM public.numbers
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 0.223 ms plan:
Query Text: select b from foreign_numbers where a=x
Foreign Scan on public.foreign_numbers (cost=100.00..114.91 rows=1 width=516)
Output: b
Filter: (foreign_numbers.a = $3)
Remote SQL: SELECT a, b FROM public.numbers
2017-01-31 00:39:25 PST CONTEXT: SQL statement "select b from foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG: duration: 13.963 ms plan:
Query Text: select test_pg_fdw();
Result (cost=0.00..0.26 rows=1 width=0)
Output: test_pg_fdw()
Note that the remote query does not contain the WHERE clause after the 5th invocation.
I understand that this is because PostgreSQL starts using generic plan with pulled up where clause after the 5th invocation hoping that it would be faster since we have skiped planning the query on each invocation, but in this case this decision is causing the query to slow down.
How should we fix this problem?
--
Abbas
Follow us on Twitter
@EnterpriseDB
Visit EnterpriseDB for tutorials, webinars, whitepapers and more
Abbas
Architect
Skype ID: gabbasb
www.enterprisedb.comFollow us on Twitter
@EnterpriseDB
Visit EnterpriseDB for tutorials, webinars, whitepapers and more
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Craig RingerДата:
Сообщение: Re: [HACKERS] logical decoding of two-phase transactions
Следующее
От: Konstantin KnizhnikДата:
Сообщение: Re: [HACKERS] logical decoding of two-phase transactions