Issue with NULLS LAST, with postgres_fdw sort pushdown - Mailing list pgsql-hackers
| From | Rajkumar Raghuwanshi |
|---|---|
| Subject | Issue with NULLS LAST, with postgres_fdw sort pushdown |
| Date | |
| Msg-id | CAKcux6mEaA-Ltu5VjwmjaGrSuKTCCdyC7XNjiwi+-nJv-P+izg@mail.gmail.com Whole thread Raw |
| Responses |
Re: Issue with NULLS LAST, with postgres_fdw sort pushdown
|
| List | pgsql-hackers |
Hi,
I am testing postgres_fdw sort pushdown feature for PostgreSQL 9.6 DB, and I observed below issue.
I am testing postgres_fdw sort pushdown feature for PostgreSQL 9.6 DB, and I observed below issue.
for testing, I have a table "fdw_sort_test" in foreign server for which postgres_fdw, foreign table created in local server.
db2=# select * from fdw_sort_test ;
id | name
----+------
1 | xyz
id | name
----+------
1 | xyz
3 |
2 | abc
4 | pqr
(4 rows)
(4 rows)
on version 9.6 :
db1=# select * from fdw_sort_test order by name desc nulls last;
id | name
----+------
3 |
db1=# select * from fdw_sort_test order by name desc nulls last;
id | name
----+------
3 |
1 | xyz
4 | pqr
2 | abc
(4 rows)
db1=# explain verbose select * from fdw_sort_test order by name desc nulls last;
QUERY PLAN
db1=# explain verbose select * from fdw_sort_test order by name desc nulls last;
QUERY PLAN
--------------------------------------------------------------------------------
Foreign Scan on public.fdw_sort_test (cost=100.00..129.95 rows=561 width=122)
Output: id, name
Foreign Scan on public.fdw_sort_test (cost=100.00..129.95 rows=561 width=122)
Output: id, name
Remote SQL: SELECT id, name FROM public.fdw_sort_test ORDER BY name DESC
(3 rows)
on version 9.5 :
on version 9.5 :
db1=# select * from fdw_sort_test order by name desc nulls last;
id | name
----+------
1 | xyz
id | name
----+------
1 | xyz
4 | pqr
2 | abc
3 |
(4 rows)
db1=# explain verbose select * from fdw_sort_test order by name desc nulls last;
QUERY PLAN
--------------------------------------------------------------------------------------
Sort (cost=152.44..153.85 rows=561 width=122)
db1=# explain verbose select * from fdw_sort_test order by name desc nulls last;
QUERY PLAN
--------------------------------------------------------------------------------------
Sort (cost=152.44..153.85 rows=561 width=122)
Output: id, name
-> Foreign Scan on public.fdw_sort_test (cost=100.00..126.83 rows=561 width=122)
Output: id, name
Remote SQL: SELECT id, name FROM public.fdw_sort_test
steps to reproduce :
\c postgres postgres
--create role and database db1, will act as local server
create role db1 password 'db1' superuser login;
create database db1 owner=db1;
grant all on database db1 to db1;
--create role and database db2, will act as foreign server
create role db2 password 'db2' superuser login;
create database db2 owner=db2;
grant all on database db2 to db2;
--connect to db2 and create a table
\c db2 db2
create table fdw_sort_test (id integer, name varchar(50));
insert into fdw_sort_test values (1,'xyz');
insert into fdw_sort_test values (3,null);
insert into fdw_sort_test values (2,'abc');
insert into fdw_sort_test values (4,'pqr');
--connect to db1 and create postgres_fdw
\c db1 db1
create extension postgres_fdw;
create server db2_link_server foreign data wrapper postgres_fdw options (host 'db2_machine_ip', dbname 'db2', port 'db_machine_port_no');
create user mapping for db1 server db2_link_server options (user 'db2', password 'db2');
--create a foreign table
create foreign table fdw_sort_test (id integer, name varchar(50)) server db2_link_server;
--run the below query and checkout the output
select * from fdw_sort_test order by name desc nulls last;
--check the explain plan
explain plan select * from fdw_sort_test order by name desc nulls last;
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
pgsql-hackers by date: