Optimizer sorting an already sorted result
От | Gurjeet Singh |
---|---|
Тема | Optimizer sorting an already sorted result |
Дата | |
Msg-id | 65937bea0804291825oce87906o25d86bab10bd996a@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Optimizer sorting an already sorted result
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
<span style="font-family: courier new,monospace;">In the plan below, we can see that the optimizer is sorting an alreadysorted result. It seems to forget the sort order across the UNIQUE node. My question is, do we make any attempts inthe optimizer to remember the sort order of a result, to avoid any further sorting on same sort-key? If not, can we dosomething about it?<br /><br />postgres=# explain select * from del where ctid in ( select ('''(' || i || ',' || j || ')''')::tidfrom generate_series( 0, 1) s1(i), generate_series( 1, 1 ) s2(j) );</span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;"> QUERY PLAN </span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;">------------------------------------------------------------------------------------------------------------------------</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> Merge Join (cost=177447.07..182043.29rows=40000 width=97)</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;"> Merge Cond: ((((((('''('::text || (s1.i)::text) || ','::text) || (s2.j)::text)|| ')'''::text))::tid) = del.ctid)</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;"> -> Sort (cost=155639.89..155739.89 rows=40000 width=8)</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> Sort Key: (((((('''('::text|| (s1.i)::text) || ','::text) || (s2.j)::text) || ')'''::text))::tid)</span><br style="font-family: couriernew,monospace;" /><span style="font-family: courier new,monospace;"> -> Unique (cost=147032.84..152032.84rows=40000 width=8)</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;"> -> Sort (cost=147032.84..149532.84 rows=1000000 width=8)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> Sort Key: (((((('''('::text || (s1.i)::text) || ','::text) || (s2.j)::text) || ')'''::text))::tid)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Nested Loop (cost=13.50..20026.00 rows=1000000 width=8)</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Function Scan on generate_series s1 (cost=0.00..12.50 rows=1000 width=4)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Materialize (cost=13.50..23.50 rows=1000 width=4)</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Function Scan on generate_series s2 (cost=0.00..12.50 rows=1000 width=4)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> -> Materialize (cost=21807.19..23055.61 rows=99874 width=103)</span><br style="font-family: courier new,monospace;" /><spanstyle="font-family: courier new,monospace;"> -> Sort (cost=21807.19..22056.87 rows=99874 width=103)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> Sort Key: del.ctid</span><br style="font-family: courier new,monospace;" /><span style="font-family:courier new,monospace;"> -> Seq Scan on del (cost=0.00..2586.74 rows=99874 width=103)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">(15 rows)</span><brstyle="font-family: courier new,monospace;" /><br clear="all" style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">Best regards,</span><br /><br style="font-family: couriernew,monospace;" /><span style="font-family: courier new,monospace;">-- </span><br style="font-family: courier new,monospace;"/><span style="font-family: courier new,monospace;">gurjeet[.singh]@EnterpriseDB.com</span><br style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">singh.gurjeet@{ gmail | hotmail| indiatimes | yahoo }.com</span><br style="font-family: courier new,monospace;" /><br style="font-family: couriernew,monospace;" /><span style="font-family: courier new,monospace;">EnterpriseDB <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a></span><brstyle="font-family: courier new,monospace;" /><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">Mail sent from my BlackLaptopdevice </span>
В списке pgsql-hackers по дате отправления: