pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery question
5+ messages / 4 participants
[nested] [flat]
* Query question
@ 2003-11-13 04:09 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-11-13 05:20 ` Re: Query question Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Christopher Kings-Lynne @ 2003-11-13 04:09 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query question
2003-11-13 04:09 Query question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-11-13 05:20 ` Josh Berkus <josh@agliodbs.com>
2003-11-14 16:07 ` Re: Query question Neil Conway <neilc@samurai.com>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2003-11-13 05:20 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query question
2003-11-13 04:09 Query question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-11-13 05:20 ` Re: Query question Josh Berkus <josh@agliodbs.com>
@ 2003-11-14 16:07 ` Neil Conway <neilc@samurai.com>
2003-11-14 16:40 ` Re: Query question Tom Lane <tgl@sss.pgh.pa.us>
2003-11-15 03:35 ` Re: Query question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 2 replies; 5+ messages in thread
From: Neil Conway @ 2003-11-14 16:07 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query question
2003-11-13 04:09 Query question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-11-13 05:20 ` Re: Query question Josh Berkus <josh@agliodbs.com>
2003-11-14 16:07 ` Re: Query question Neil Conway <neilc@samurai.com>
@ 2003-11-14 16:40 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-11-14 16:40 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query question
2003-11-13 04:09 Query question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-11-13 05:20 ` Re: Query question Josh Berkus <josh@agliodbs.com>
2003-11-14 16:07 ` Re: Query question Neil Conway <neilc@samurai.com>
@ 2003-11-15 03:35 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 0 replies; 5+ messages in thread
From: Christopher Kings-Lynne @ 2003-11-15 03:35 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
>>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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-11-15 03:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-13 04:09 Query question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-11-13 05:20 ` Josh Berkus <josh@agliodbs.com>
2003-11-14 16:07 ` Neil Conway <neilc@samurai.com>
2003-11-14 16:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-15 03:35 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox