BUG #17162: order by clause is not pushed down to foreign scans when a WHERE clause is given in query
От | PG Bug reporting form |
---|---|
Тема | BUG #17162: order by clause is not pushed down to foreign scans when a WHERE clause is given in query |
Дата | |
Msg-id | 17162-d69d57bc36cb53f5@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17162: order by clause is not pushed down to foreign scans when a WHERE clause is given in query
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 17162 Logged by: Chetan Suttraway Email address: chetansuttraway@gmail.com PostgreSQL version: 13.0 Operating system: windows Description: I am assuming that order by clause needs to be pushed to foreign scans irrespective whether the parent node is append, sort or any parallel node. 1. setup tables using inheritance wherein the derived tables are foreign tables postgres=# create table base(a int, b int , c int); CREATE TABLE postgres=# create table src1(a int, b int, c int); CREATE TABLE postgres=# create table src2(a int, b int, c int); CREATE TABLE postgres=# insert into src1 values(generate_series(1,1000000), generate_series(1, 1000000), generate_series(1, 1000000)); INSERT 0 1000000 postgres=# insert into src2 values(generate_series(1,1000000), generate_series(1, 1000000), generate_series(1, 1000000)); INSERT 0 1000000 postgres=# create foreign table derived_1() inherits(base) SERVER myserver OPTIONS ( table_name 'src1') ; CREATE FOREIGN TABLE postgres=# create foreign table derived_2() inherits(base) SERVER myserver OPTIONS ( table_name 'src2') ; CREATE FOREIGN TABLE 2. Check for ORDER BY without WHERE clause. In this case, you can see the ORDER BY clause being pushed into the remote sql. postgres=# explain verbose select a from base order by a; QUERY PLAN ---------------------------------------------------------------------------------------- Merge Append (cost=200.03..486.86 rows=5851 width=4) Sort Key: base.a -> Sort (cost=0.01..0.02 rows=1 width=4) Output: base_1.a Sort Key: base_1.a -> Seq Scan on public.base base_1 (cost=0.00..0.00 rows=1 width=4) Output: base_1.a -> Foreign Scan on public.derived_1 base_2 (cost=100.00..205.60 rows=2925 width=4) Output: base_2.a Remote SQL: SELECT a FROM public.src1 ORDER BY a ASC NULLS LAST -> Foreign Scan on public.derived_2 base_3 (cost=100.00..205.60 rows=2925 width=4) Output: base_3.a Remote SQL: SELECT a FROM public.src2 ORDER BY a ASC NULLS LAST (13 rows) 3. check with ORDER BY and WHERE clause. In this plan, you can see that the order by clause is not pushed into remote sql. postgres=# explain verbose select a from base where a>100 and a <200000 order by a; QUERY PLAN -------------------------------------------------------------------------------------------- Sort (cost=309.27..309.35 rows=31 width=4) Output: base.a Sort Key: base.a -> Append (cost=0.00..308.50 rows=31 width=4) -> Seq Scan on public.base base_1 (cost=0.00..0.00 rows=1 width=4) Output: base_1.a Filter: ((base_1.a > 100) AND (base_1.a < 200000)) -> Foreign Scan on public.derived_1 base_2 (cost=100.00..154.18 rows=15 width=4) Output: base_2.a Remote SQL: SELECT a FROM public.src1 WHERE ((a > 100)) AND ((a < 200000)) -> Foreign Scan on public.derived_2 base_3 (cost=100.00..154.18 rows=15 width=4) Output: base_3.a Remote SQL: SELECT a FROM public.src2 WHERE ((a > 100)) AND ((a < 200000)) (13 rows) Wouldn't it be good to push order by clause even when where clause is given in query?
В списке pgsql-bugs по дате отправления:
Предыдущее
От: PG Bug reporting formДата:
Сообщение: BUG #17161: Assert failed on opening a relation that exists in two schemas via the LANGUAGE SQL function