Skip site navigation (1) Skip section navigation (2)

Optimizer sorting an already sorted result

From: "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>
To: "PGSQL Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: Optimizer sorting an already sorted result
Date: 2008-04-30 01:25:30
Message-ID: 65937bea0804291825oce87906o25d86bab10bd996a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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=40000 width=97)
   Merge Cond: ((((((('''('::text || (s1.i)::text) || ','::text) ||
(s2.j)::text) || ')'''::text))::tid) = del.ctid)
   ->  Sort  (cost=155639.89..155739.89 rows=40000 width=8)
         Sort Key: (((((('''('::text || (s1.i)::text) || ','::text) ||
(s2.j)::text) || ')'''::text))::tid)
         ->  Unique  (cost=147032.84..152032.84 rows=40000 width=8)
               ->  Sort  (cost=147032.84..149532.84 rows=1000000 width=8)
                     Sort Key: (((((('''('::text || (s1.i)::text) ||
','::text) || (s2.j)::text) || ')'''::text))::tid)
                     ->  Nested Loop  (cost=13.50..20026.00 rows=1000000
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,

-- 
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-04-30 02:02:04
Subject: Re: Optimizer sorting an already sorted result
Previous:From: Josh BerkusDate: 2008-04-30 01:20:36
Subject: Re: Protection from SQL injection

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group