pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
trivial problem
9+ messages / 8 participants
[nested] [flat]

* trivial problem
@ 1999-10-29 12:55 Frank Joerdens <frank@x9media.com>
  1999-10-29 14:27 ` Re: [SQL] trivial problem Oleg Bartunov <oleg@sai.msu.su>
  1999-10-29 14:32 ` Re: [SQL] trivial problem Mathijs Brands <mathijs@ilse.nl>
  0 siblings, 2 replies; 9+ messages in thread

From: Frank Joerdens @ 1999-10-29 12:55 UTC (permalink / raw)
  To: pgsql-sql

this sounds as if there must be a rather trivial solution:

i am looking for the 50 rows with the largest value for a particular
column. i could create a view with 

create view as select * from table order by column; 

and then somehow only retrieve the top 50 rows . . . but how?

thanks

frank



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] trivial problem
  1999-10-29 12:55 trivial problem Frank Joerdens <frank@x9media.com>
@ 1999-10-29 14:27 ` Oleg Bartunov <oleg@sai.msu.su>
  1999-10-29 17:07   ` Re: [SQL] trivial problem Roderick A. Anderson <raanders@altoplanos.net>
  1 sibling, 1 reply; 9+ messages in thread

From: Oleg Bartunov @ 1999-10-29 14:27 UTC (permalink / raw)
  To: Frank Joerdens <frank@x9media.com>; +Cc: pgsql-sql

select * from table order by column limit 50

	Oleg

On Fri, 29 Oct 1999, Frank Joerdens wrote:

> Date: Fri, 29 Oct 1999 14:55:29 +0200
> From: Frank Joerdens <frank@x9media.com>
> To: pgsql-sql@postgreSQL.org
> Subject: [SQL] trivial problem
> 
> this sounds as if there must be a rather trivial solution:
> 
> i am looking for the 50 rows with the largest value for a particular
> column. i could create a view with 
> 
> create view as select * from table order by column; 
> 
> and then somehow only retrieve the top 50 rows . . . but how?
> 
> thanks
> 
> frank
> 
> ************
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] trivial problem
  1999-10-29 12:55 trivial problem Frank Joerdens <frank@x9media.com>
  1999-10-29 14:27 ` Re: [SQL] trivial problem Oleg Bartunov <oleg@sai.msu.su>
@ 1999-10-29 17:07   ` Roderick A. Anderson <raanders@altoplanos.net>
  1999-10-29 18:12     ` Re: [SQL] trivial problem tjk@tksoft.com <tjk@tksoft.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Roderick A. Anderson @ 1999-10-29 17:07 UTC (permalink / raw)
  To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: pgsql-sql

On Fri, 29 Oct 1999, Oleg Bartunov wrote:

> select * from table order by column limit 50
> 

It used to be and could still be in Oracle (sorry to pull them into this)
that limit applied to the selection clause so the first 'n' tulples
selected,not the top/bottom 'n', would be reported.
   Would this be a feature of the SQL standard or of Oracles
implementation?  And how does PostgreSQL handle limits?

Rod
--
Roderick A. Anderson
raanders@altoplanos.net               Altoplanos Information Systems, Inc.
Voice: 208.765.6149                            212 S. 11th Street, Suite 5
FAX: 208.664.5299                                  Coeur d'Alene, ID 83814




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] trivial problem
  1999-10-29 12:55 trivial problem Frank Joerdens <frank@x9media.com>
  1999-10-29 14:27 ` Re: [SQL] trivial problem Oleg Bartunov <oleg@sai.msu.su>
  1999-10-29 17:07   ` Re: [SQL] trivial problem Roderick A. Anderson <raanders@altoplanos.net>
@ 1999-10-29 18:12     ` tjk@tksoft.com <tjk@tksoft.com>
  1999-10-29 20:02       ` Re: [SQL] trivial problem Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  0 siblings, 1 reply; 9+ messages in thread

From: tjk@tksoft.com @ 1999-10-29 18:12 UTC (permalink / raw)
  To: raanders@altoplanos.net; +Cc: oleg@sai.msu.su; pgsql-sql

If limit didn't mean "get first 50," there would be no time/effort
saved by specifying a limit. 

I.e. when a limit is specified, the backend looks for matches until it
finds 50 records, and then stops. 


Troy


> 
> On Fri, 29 Oct 1999, Oleg Bartunov wrote:
> 
> > select * from table order by column limit 50
> > 
> 
> It used to be and could still be in Oracle (sorry to pull them into this)
> that limit applied to the selection clause so the first 'n' tulples
> selected,not the top/bottom 'n', would be reported.
>    Would this be a feature of the SQL standard or of Oracles
> implementation?  And how does PostgreSQL handle limits?
> 
> Rod
> --
> Roderick A. Anderson
> raanders@altoplanos.net               Altoplanos Information Systems, Inc.
> Voice: 208.765.6149                            212 S. 11th Street, Suite 5
> FAX: 208.664.5299                                  Coeur d'Alene, ID 83814
> 
> 
> ************
> 
> 




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] trivial problem
  1999-10-29 12:55 trivial problem Frank Joerdens <frank@x9media.com>
  1999-10-29 14:27 ` Re: [SQL] trivial problem Oleg Bartunov <oleg@sai.msu.su>
  1999-10-29 17:07   ` Re: [SQL] trivial problem Roderick A. Anderson <raanders@altoplanos.net>
  1999-10-29 18:12     ` Re: [SQL] trivial problem tjk@tksoft.com <tjk@tksoft.com>
@ 1999-10-29 20:02       ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  1999-10-29 20:19         ` Re: [SQL] trivial problem Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Moray McConnachie @ 1999-10-29 20:02 UTC (permalink / raw)
  To: tjk@tksoft.com; Roderick A. Anderson <raanders@altoplanos.net>; +Cc: oleg@sai.msu.su; pgsql-sql


----- Original Message -----
From: <tjk@tksoft.com>
To: Roderick A. Anderson <raanders@altoplanos.net>
Cc: <oleg@sai.msu.su>; <pgsql-sql@postgreSQL.org>
Sent: Friday, October 29, 1999 7:12 PM
Subject: Re: [SQL] trivial problem


> If limit didn't mean "get first 50," there would be no time/effort
> saved by specifying a limit.
>
> I.e. when a limit is specified, the backend looks for matches until it
> finds 50 records, and then stops.

Eh? I think the questioner meant effectively "in a query with LIMIT and
ORDER BY, which takes precedence?". To which the answer appears  to be ORDER
BY, which surprised me, but is of course very useful.

I.e. all sorting is completed, and then the first fifty records are
returned.

Does that mean that the seek time for a limited, ordered by query is the
same as that for an unlimited query? The extra speed gained from the limit
only achieved by not having to return the data? Or is there some kind of
magic going on to allow it to ignore certain things about the query when a
LIMIT is in place?




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] trivial problem
  1999-10-29 12:55 trivial problem Frank Joerdens <frank@x9media.com>
  1999-10-29 14:27 ` Re: [SQL] trivial problem Oleg Bartunov <oleg@sai.msu.su>
  1999-10-29 17:07   ` Re: [SQL] trivial problem Roderick A. Anderson <raanders@altoplanos.net>
  1999-10-29 18:12     ` Re: [SQL] trivial problem tjk@tksoft.com <tjk@tksoft.com>
  1999-10-29 20:02       ` Re: [SQL] trivial problem Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
@ 1999-10-29 20:19         ` Bruce Momjian <maillist@candle.pha.pa.us>
  1999-10-29 23:37           ` Re: [SQL] trivial problem Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Bruce Momjian @ 1999-10-29 20:19 UTC (permalink / raw)
  To: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>; +Cc: tjk@tksoft.com, "Roderick A. Anderson" <raanders@altoplanos.net>; oleg@sai.msu.su; pgsql-sql

FAQ says:

See the <I>fetch</I> manual page, or use SELECT ... LIMIT....<P>

This only prevents all row results from being transferred to the client.
The entire query must be evaluated, even if you only want just the first
few rows. Consider a query that has an <I>order by.</I>  There is no way
to return any rows until the entire query is evaluated and sorted.<P>

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 
> ----- Original Message -----
> From: <tjk@tksoft.com>
> To: Roderick A. Anderson <raanders@altoplanos.net>
> Cc: <oleg@sai.msu.su>; <pgsql-sql@postgreSQL.org>
> Sent: Friday, October 29, 1999 7:12 PM
> Subject: Re: [SQL] trivial problem
> 
> 
> > If limit didn't mean "get first 50," there would be no time/effort
> > saved by specifying a limit.
> >
> > I.e. when a limit is specified, the backend looks for matches until it
> > finds 50 records, and then stops.
> 
> Eh? I think the questioner meant effectively "in a query with LIMIT and
> ORDER BY, which takes precedence?". To which the answer appears  to be ORDER
> BY, which surprised me, but is of course very useful.
> 
> I.e. all sorting is completed, and then the first fifty records are
> returned.
> 
> Does that mean that the seek time for a limited, ordered by query is the
> same as that for an unlimited query? The extra speed gained from the limit
> only achieved by not having to return the data? Or is there some kind of
> magic going on to allow it to ignore certain things about the query when a
> LIMIT is in place?
> 
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] trivial problem
  1999-10-29 12:55 trivial problem Frank Joerdens <frank@x9media.com>
  1999-10-29 14:27 ` Re: [SQL] trivial problem Oleg Bartunov <oleg@sai.msu.su>
  1999-10-29 17:07   ` Re: [SQL] trivial problem Roderick A. Anderson <raanders@altoplanos.net>
  1999-10-29 18:12     ` Re: [SQL] trivial problem tjk@tksoft.com <tjk@tksoft.com>
  1999-10-29 20:02       ` Re: [SQL] trivial problem Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  1999-10-29 20:19         ` Re: [SQL] trivial problem Bruce Momjian <maillist@candle.pha.pa.us>
@ 1999-10-29 23:37           ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-10-30 00:19             ` Re: [SQL] trivial problem Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 1999-10-29 23:37 UTC (permalink / raw)
  To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>; tjk@tksoft.com, "Roderick A. Anderson" <raanders@altoplanos.net>; oleg@sai.msu.su; pgsql-sql

Bruce Momjian <maillist@candle.pha.pa.us> writes:
> FAQ says:
> See the <I>fetch</I> manual page, or use SELECT ... LIMIT....<P>

> This only prevents all row results from being transferred to the client.
> The entire query must be evaluated, even if you only want just the first
> few rows. Consider a query that has an <I>order by.</I>  There is no way
> to return any rows until the entire query is evaluated and sorted.<P>

That FAQ entry is not right.  It might be right for FETCH, but not for
SELECT ... LIMIT.  With a LIMIT, the executor will stop once it has
generated the requested number of rows.  Of course, how much computation
is needed to reach that point depends greatly on the query and the query
plan.

If an explicit sort step is being used to implement ORDER BY, then the
FAQ is correct.  But if the ORDER BY is implemented by an index scan,
rather than an explicit sort step, then presto: we just run the
indexscan for the first N tuples and stop.  (6.5.* is not very bright
about avoiding a sort step, but current sources are much better.)

Still to be done: modify the optimizer to be aware of this fact so that
it will be more likely to choose an indexscan when a small LIMIT is
present.  Right now it chooses indexscan vs. explicit sort on the basis
of cost to return all the tuples, which is bogus if there's a LIMIT.
Ideally it'd also take LIMIT into account when choosing join types,
etc...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] trivial problem
  1999-10-29 12:55 trivial problem Frank Joerdens <frank@x9media.com>
  1999-10-29 14:27 ` Re: [SQL] trivial problem Oleg Bartunov <oleg@sai.msu.su>
  1999-10-29 17:07   ` Re: [SQL] trivial problem Roderick A. Anderson <raanders@altoplanos.net>
  1999-10-29 18:12     ` Re: [SQL] trivial problem tjk@tksoft.com <tjk@tksoft.com>
  1999-10-29 20:02       ` Re: [SQL] trivial problem Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
  1999-10-29 20:19         ` Re: [SQL] trivial problem Bruce Momjian <maillist@candle.pha.pa.us>
  1999-10-29 23:37           ` Re: [SQL] trivial problem Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-10-30 00:19             ` Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Bruce Momjian @ 1999-10-30 00:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>; tjk@tksoft.com, "Roderick A. Anderson" <raanders@altoplanos.net>; oleg@sai.msu.su; pgsql-sql

> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > FAQ says:
> > See the <I>fetch</I> manual page, or use SELECT ... LIMIT....<P>
> 
> > This only prevents all row results from being transferred to the client.
> > The entire query must be evaluated, even if you only want just the first
> > few rows. Consider a query that has an <I>order by.</I>  There is no way
> > to return any rows until the entire query is evaluated and sorted.<P>
> 
> That FAQ entry is not right.  It might be right for FETCH, but not for
> SELECT ... LIMIT.  With a LIMIT, the executor will stop once it has
> generated the requested number of rows.  Of course, how much computation
> is needed to reach that point depends greatly on the query and the query
> plan.

New FAQ text is:

   The entire query may have to be evaluated, even if you only want the
   first few rows. Consider a query that has an ORDER BY. If there is an
   index that matches the ORDER BY, PostgreSQL may be able to evaluate
   only the first few records requested, or the entire query may have to
   be evaluated until the desired rows have been generated.
   

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] trivial problem
  1999-10-29 12:55 trivial problem Frank Joerdens <frank@x9media.com>
@ 1999-10-29 14:32 ` Mathijs Brands <mathijs@ilse.nl>
  1 sibling, 0 replies; 9+ messages in thread

From: Mathijs Brands @ 1999-10-29 14:32 UTC (permalink / raw)
  To: Frank Joerdens <frank@x9media.com>; +Cc: pgsql-sql

On Fri, Oct 29, 1999 at 02:55:29PM +0200, Frank Joerdens allegedly wrote:
> this sounds as if there must be a rather trivial solution:
> 
> i am looking for the 50 rows with the largest value for a particular
> column. i could create a view with 
> 
> create view as select * from table order by column; 
> 
> and then somehow only retrieve the top 50 rows . . . but how?
> 
> thanks
> 
> frank
> 
> ************

Use limit?

Mathijs



^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~1999-10-30 00:19 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-29 12:55 trivial problem Frank Joerdens <frank@x9media.com>
1999-10-29 14:27 ` Oleg Bartunov <oleg@sai.msu.su>
1999-10-29 17:07   ` Roderick A. Anderson <raanders@altoplanos.net>
1999-10-29 18:12     ` tjk@tksoft.com <tjk@tksoft.com>
1999-10-29 20:02       ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
1999-10-29 20:19         ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-10-29 23:37           ` Tom Lane <tgl@sss.pgh.pa.us>
1999-10-30 00:19             ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-10-29 14:32 ` Mathijs Brands <mathijs@ilse.nl>

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