pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query 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