[HACKERS] Optimizer sorting an already sorted result

[Gurjeet Singh]

In the plan below, we can see that the optimizer is sorting an already
sorted result. It seems to forget the sort order across the UNIQUE node. My
question is, do we make any attempts in the optimizer to remember the sort
order of a result, to avoid any further sorting on same sort-key? If not,
can we do something about it?

postgres=# explain select * from del where ctid in ( select ('''(' || i ||
',' || j || ')''')::tid from generate_series( 0, 1) s1(i), generate_series(
1, 1 ) s2(j) );
   QUERY
PLAN

 Merge Join  (cost=177447.07..182043.29 rows=4 width=97)
   Merge Cond: ((('''('::text || (s1.i)::text) || ','::text) ||
(s2.j)::text) || ')'''::text))::tid) = del.ctid)
   -  Sort  (cost=155639.89..155739.89 rows=4 width=8)
 Sort Key: (('''('::text || (s1.i)::text) || ','::text) ||
(s2.j)::text) || ')'''::text))::tid)
 -  Unique  (cost=147032.84..152032.84 rows=4 width=8)
   -  Sort  (cost=147032.84..149532.84 rows=100 width=8)
 Sort Key: (('''('::text || (s1.i)::text) ||
','::text) || (s2.j)::text) || ')'''::text))::tid)
 -  Nested Loop  (cost=13.50..20026.00 rows=100
width=8)
   -  Function Scan on generate_series s1
(cost=0.00..12.50 rows=1000 width=4)
   -  Materialize  (cost=13.50..23.50 rows=1000
width=4)
 -  Function Scan on generate_series s2
(cost=0.00..12.50 rows=1000 width=4)
   -  Materialize  (cost=21807.19..23055.61 rows=99874 width=103)
 -  Sort  (cost=21807.19..22056.87 rows=99874 width=103)
   Sort Key: del.ctid
   -  Seq Scan on del  (cost=0.00..2586.74 rows=99874
width=103)
(15 rows)

Best regards,

-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [HACKERS] Optimizer sorting an already sorted result

[Tom Lane]

Gurjeet Singh [EMAIL PROTECTED] writes:
 In the plan below, we can see that the optimizer is sorting an already
 sorted result. It seems to forget the sort order across the UNIQUE node. My
 question is, do we make any attempts in the optimizer to remember the sort
 order of a result, to avoid any further sorting on same sort-key? If not,
 can we do something about it?

Per the comment in create_unique_path:

/*
 * Treat the output as always unsorted, since we don't necessarily have
 * pathkeys to represent it.
 */
pathnode-path.pathkeys = NIL;

No doubt this could be improved, but I'm unsure about the effort/reward
ratio.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers