Thread: Query question

Hi,

I have coded some improvements to phpPgAdmin that I think are pretty
cool.  Basicaly, once you are browsing the results of an arbitrary
SELECT query, you can still sort by columns, regardless of the
underlying ORDER BY of the SELECT.

I do this like this:

SELECT * FROM (arbitrary subquery) AS sub ORDER BY 1,3;

Now, this all works fine, but I want to know if this is efficient or not.

Does doing a select of a select cause serious performance degradation?

Chris

Chris,

> SELECT * FROM (arbitrary subquery) AS sub ORDER BY 1,3;
>
> Now, this all works fine, but I want to know if this is efficient or not.
>
> Does doing a select of a select cause serious performance degradation?

It would be better if you could strip out the inner sort, but I can understand
why that might not be possible in all cases.

The only thing you're adding to the query is a second SORT step, so it
shouldn't require any more time/memory than the query's first SORT did.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:
> The only thing you're adding to the query is a second SORT step, so it
> shouldn't require any more time/memory than the query's first SORT
> did.

Interesting -- I wonder if it would be possible for the optimizer to
detect this and avoid the redundant inner sort ... (/me muses to
himself)

-Neil

Neil Conway <neilc@samurai.com> writes:
> Interesting -- I wonder if it would be possible for the optimizer to
> detect this and avoid the redundant inner sort ... (/me muses to
> himself)

I think the ability to generate two sort steps is a feature, not a bug.
This has been often requested in connection with user-defined
aggregates, where it's handy to be able to control the order of arrival
of rows at the aggregation function.  If the optimizer suppressed the
inner sort then we'd lose that ability.

            regards, tom lane

>>The only thing you're adding to the query is a second SORT step, so it
>>shouldn't require any more time/memory than the query's first SORT
>>did.
>
>
> Interesting -- I wonder if it would be possible for the optimizer to
> detect this and avoid the redundant inner sort ... (/me muses to
> himself)

That's somethign I've wondered myself as well.  Also - I wonder if the
optimiser could be made smart enough to push down the outer LIMIT and
OFFSET clauses into the subquery.

Chris