# Re: [HACKERS] EXPLAIN ANALYZE output weird for Top-N Sort

```Tom Lane-2 wrote
> Simon Riggs &lt;

> simon@```
```
> &gt; writes:
>> Limit  (cost=.... rows=20 width=175) (actual time=.... rows=20 loops=1)
>>    ->  Sort  (cost=.... rows=568733 width=175) (actual time=....
>> rows=20 loops=1)
>>          Sort Method: top-N heapsort
>
>> The Sort estimate shows 568733 rows, whereas the actual rows are 20.
>
> [ shrug... ]  The estimated value is the planner's estimate of what would
> happen *if you ran the node to completion*, which in practice doesn't
> happen because of the LIMIT.  The actual value is, well, the actual value.
> We certainly should not munge around the actual value.
>
> We could imagine munging the reported estimates to account for the parent
> LIMIT, but that would make it a lot harder to understand the planner's
> "thought processes", because the reported estimates would have that much
> less to do with the numbers actually used in the internal calculations.

Is it even possible for a sort node directly under a limit to output (as
nebulous as that term is in this context) more rows that desired by the
limit?

The interesting thing about a sort node is not its output but its input -
i.e., the number of rows being fed to it via the node nested under it.
Which prompts the question whether it would be good to show that value as an
attribute of the sort node during EXPLAIN ANALYZE instead of having to scan
down to the child node.  I guess you can argue that we are currently since
that is the same value as the estimated rows returned.  If you were to
change that to reflect the impact of the parent limit node you'd probably
want to add something else to reflect the child input size (in rows, not
memory).

>From a pure theory standpoint having the estimated rows reflect the input
size instead of the output size seems wrong.  In the presence of limit it
won't output more than N rows whereas in all other cases the input and the
output will be identical.  That said I am only pondering this concept
because of this thread - it would help to know what sparked all of this in
the first place.  From a practical perspective the current behavior captures
the most important aspect of the sort - the size of the input - and the user
knowing of the limit isn't likely to wonder whether we are somehow being
wasteful by "returning" the extra rows; which are not returned so much as
scanned over in place by the parent node.

David J.

--
View this message in context:
http://postgresql.nabble.com/EXPLAIN-ANALYZE-output-weird-for-Top-N-Sort-tp5826922p5826935.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

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