Another problem with inserting Sort node into the plan

[Taras]

Hi,

I have another anomaly when inserting Sort node into the plan (unrelated
to the previous post). In the following query:

select emp.ename from emp, manages where emp.eno = manages.eno;

I correctly identify interesting orders, however, when I am trying to
insert Sort node into the plan, and than display the resulting plan
using "explain" - it shows that the sort key is "emp.ename" - which is
not in the list of pathkeys, and so shouldn't be the sort key at all. In
this query, Hash join method is used.


Thanks,
Taras

[Ken Salem]

Again, I'm not sure what's going on. I tried your query on my
test db (based on the A2 schema) and got this plan:

mytest=# explain select emp.ename from emp, manages where emp.eno =
manages.eno;
QUERY PLAN
-------------------------------------------------------------------
Hash Join (cost=1.06..2.46 rows=5 width=14)
Hash Cond: ("outer".eno = "inner".eno)
-> Seq Scan on emp (cost=0.00..1.20 rows=20 width=18)
-> Hash (cost=1.05..1.05 rows=5 width=4)
-> Seq Scan on manages (cost=0.00..1.05 rows=5 width=4)
(5 rows)


It is not using any of the sorted sequential access paths that I
generated. There are no sorts at all.

-KMS

Post by Taras
Hi,
I have another anomaly when inserting Sort node into the plan (unrelated
select emp.ename from emp, manages where emp.eno = manages.eno;
I correctly identify interesting orders, however, when I am trying to
insert Sort node into the plan, and than display the resulting plan
using "explain" - it shows that the sort key is "emp.ename" - which is
not in the list of pathkeys, and so shouldn't be the sort key at all. In
this query, Hash join method is used.
Thanks,
Taras

[Taras]

I have disabled in the code creation of the original paths for single
relations - for testing purposes, thats why the sorts appear in my plan.

Taras

Post by Ken Salem
Again, I'm not sure what's going on. I tried your query on my
mytest=# explain select emp.ename from emp, manages where emp.eno =
manages.eno;
QUERY PLAN
-------------------------------------------------------------------
Hash Join (cost=1.06..2.46 rows=5 width=14)
Hash Cond: ("outer".eno = "inner".eno)
-> Seq Scan on emp (cost=0.00..1.20 rows=20 width=18)
-> Hash (cost=1.05..1.05 rows=5 width=4)
-> Seq Scan on manages (cost=0.00..1.05 rows=5 width=4)
(5 rows)
It is not using any of the sorted sequential access paths that I
generated. There are no sorts at all.
-KMS

Post by Taras
Hi,
I have another anomaly when inserting Sort node into the plan (unrelated
select emp.ename from emp, manages where emp.eno = manages.eno;
I correctly identify interesting orders, however, when I am trying to
insert Sort node into the plan, and than display the resulting plan
using "explain" - it shows that the sort key is "emp.ename" - which is
not in the list of pathkeys, and so shouldn't be the sort key at all. In
this query, Hash join method is used.
Thanks,
Taras