Re: updated SORT/LIMIT patch - Mailing list pgsql-patches

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <stark@enterprisedb.com> writes:
>> What does the executor do differently in the case of a subplan with a
>> parameter that makes it re-execute the plan from scratch and not just do a
>> simple rescan?
>
> Look at the chgParam signaling.  Since a Sort node itself has no
> parameters, it historically has only had to re-sort if its input node
> suffers a parameter change, which it checks in ExecReScanSort.  But now
> the bound effectively acts like a parameter, and has to force a
> recomputation.

Hm, that all makes sense now. But then there's something mysterious going on
still as the regression test I tried to write for this actually does work:

edb=# select (select ROW(int_array_aggregate(empno::integer),min(sal),round(avg(sal)),max(sal)) as sal from (select *
fromemp order by sal offset 3*bucket limit 3) as x) from generate_series(0,(select count(*) from emp)/3) as bucket; 
LOG:  begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f
LOG:  switching to bounded heap sort at 7 tuples
LOG:  performsort starting: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  doing unheapify of 3 tuples
LOG:  performsort done: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  internal sort ended, 17 KB used: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f
LOG:  switching to bounded heap sort at 13 tuples
LOG:  performsort starting: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  doing unheapify of 6 tuples
LOG:  performsort done: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  internal sort ended, 17 KB used: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f
LOG:  performsort starting: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  doing qsort of 14 tuples
LOG:  performsort done: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  internal sort ended, 18 KB used: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f
LOG:  performsort starting: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  doing qsort of 14 tuples
LOG:  performsort done: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  internal sort ended, 18 KB used: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f
LOG:  performsort starting: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  doing qsort of 14 tuples
LOG:  performsort done: CPU 0.00s/0.00u sec elapsed 0.00 sec
LOG:  internal sort ended, 18 KB used: CPU 0.00s/0.00u sec elapsed 0.00 sec
                 ?column?
-------------------------------------------
 ("{7369,7900,7876}",800.00,950,1100.00)
 ("{7654,7521,7934}",1250.00,1267,1300.00)
 ("{7844,7499,7782}",1500.00,1850,2450.00)
 ("{7698,7566,7902}",2850.00,2942,3000.00)
 ("{7788,7839}",3000.00,4000,5000.00)
(5 rows)

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com