pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing LIMIT only on primary table
7+ messages / 5 participants
[nested] [flat]
* using LIMIT only on primary table
@ 2002-03-02 22:35 Dan Langille <dan@langille.org>
0 siblings, 4 replies; 7+ messages in thread
From: Dan Langille @ 2002-03-02 22:35 UTC (permalink / raw)
To: pgsql-sql
If I want the last 100 orders:
SELECT * FROM orders LIMIT 100;
If I want all the items on the last 100 orders, I'd start like this:
SELECT *
from orders, order_items
where order_items.order_id = orders.id
LIMIT 100
But that will only give me the last 100 items, not 100 orders.
What I really want is
SELECT *
from orders, order_items
where order_items.order_id = orders.id
and exists
(SELECT * from orders order by ID DESC limit 100);
But that gives me all orders, not just the first 100.
Adding a LIMIT 100 to the above doesn't work either. It equates to the
first example.
Clues? cheers
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] using LIMIT only on primary table
@ 2002-03-02 23:20 daq <daq@ugyvitelszolgaltato.hu>
parent: Dan Langille <dan@langille.org>
3 siblings, 0 replies; 7+ messages in thread
From: daq @ 2002-03-02 23:20 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
Try this:
SELECT *
from order_items
where order_id in (select ID from orders order by ID desc limit 100);
DAQ
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: using LIMIT only on primary table
@ 2002-03-02 23:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dan Langille <dan@langille.org>
3 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2002-03-02 23:22 UTC (permalink / raw)
To: dan@langille.org; +Cc: pgsql-sql
"Dan Langille" <dan@langille.org> writes:
> If I want all the items on the last 100 orders, I'd start like this:
> SELECT *
> from orders, order_items
> where order_items.order_id = orders.id
> LIMIT 100
I think you want
SELECT *
from
(SELECT * from orders order by ID DESC limit 100) as recent_orders,
order_items
where order_items.order_id = recent_orders.id
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: using LIMIT only on primary table
@ 2002-03-02 23:40 Dan Langille <dan@langille.org>
parent: Dan Langille <dan@langille.org>
3 siblings, 0 replies; 7+ messages in thread
From: Dan Langille @ 2002-03-02 23:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On 2 Mar 2002 at 18:22, Tom Lane wrote:
> "Dan Langille" <dan@langille.org> writes:
> > If I want all the items on the last 100 orders, I'd start like this:
>
> > SELECT *
> > from orders, order_items
> > where order_items.order_id = orders.id
> > LIMIT 100
>
> I think you want
>
> SELECT *
> from
> (SELECT * from orders order by ID DESC limit 100) as recent_orders,
> order_items
> where order_items.order_id = recent_orders.id
That is it Tom. Thank you. As soon as I saw your solution, I thought of
creating a view to do this.
CREATE VIEW recent orders AS
SELECT * from orders
order by ID DESC
limit 100;
Which makes the query:
SELECT *
from recent_orders, order_items
where order_items.order_id = recent_orders.id
Cheers
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: using LIMIT only on primary table
@ 2002-03-03 06:00 Masaru Sugawara <rk73@sea.plala.or.jp>
parent: Dan Langille <dan@langille.org>
3 siblings, 0 replies; 7+ messages in thread
From: Masaru Sugawara @ 2002-03-03 06:00 UTC (permalink / raw)
To: dan@langille.org; +Cc: pgsql-sql
On Sat, 2 Mar 2002 17:35:31 -0500
"Dan Langille" <dan@langille.org> wrote:
> If I want the last 100 orders:
>
> SELECT * FROM orders LIMIT 100;
>
> If I want all the items on the last 100 orders, I'd start like this:
>
> SELECT *
> from orders, order_items
> where order_items.order_id = orders.id
> LIMIT 100
>
> But that will only give me the last 100 items, not 100 orders.
>
> What I really want is
>
> SELECT *
> from orders, order_items
> where order_items.order_id = orders.id
> and exists
> (SELECT * from orders order by ID DESC limit 100);
This probably gives you all the items on the last 100 orders.
select *
from (select * from orders order by ID desc limit 10) as o
inner join order_items as oi
on (oi.order_id = o.order_id)
;
>
> But that gives me all orders, not just the first 100.
>
> Adding a LIMIT 100 to the above doesn't work either. It equates to the
> first example.
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 7+ messages in thread
* using LIMIT only on primary table
@ 2002-07-27 21:26 Dan Langille <dan@langille.org>
0 siblings, 1 reply; 7+ messages in thread
From: Dan Langille @ 2002-07-27 21:26 UTC (permalink / raw)
To: pgsql-sql
This is an extension of the problem solved by
http://archives.postgresql.org/pgsql-sql/2002-03/msg00020.php but
with a slightly different complication.
I want to get the last 100 port commits from the database. Commits
are stored in
commit_log and commit_log_ports relates commits to ports. A given
commit may
affect more than one port (i.e. there is a 1-N relationship between
commit_log and
commit_log_ports).
This gives me the last 100 commits:
SELECT commit_log.*
FROM commit_log
ORDER BY commit_date DESC, id
LIMIT 100;
So a starting point for the last 100 port commits is:
explain analyze
SELECT distinct commit_log.*
FROM commit_log_ports, commit_log
WHERE commit_log.id = commit_log_ports.commit_log_id
ORDER BY commit_log.commit_date DESC, commit_log_ports.commit_log_id
LIMIT 100;
But has a very high cost:
Limit (cost=11275.92..11283.42 rows=100 width=55) (actual
time=5769.07..5771.92 rows=100 loops=1)
-> Unique (cost=11275.92..11643.73 rows=4904 width=55) (actual
time=5769.05..5770.93 rows=101 loops=1)
-> Sort (cost=11275.92..11275.92 rows=49042 width=55)
(actual time=5769.04..5769.68 rows=112 loops=1)
-> Hash Join (cost=3478.15..6387.22 rows=49042
width=55) (actual time=1263.69..4319.53 rows=49042 loops=1)
-> Seq Scan on commit_log_ports
(cost=0.00..825.42 rows=49042 width=4) (actual time=0.12..346.32
rows=49042 loops=1)
-> Hash (cost=1749.51..1749.51 rows=42951
width=51) (actual time=1074.15..1074.15 rows=0 loops=1)
-> Seq Scan on commit_log
(cost=0.00..1749.51 rows=42951 width=51) (actual time=0.14..396.99
rows=42953 loops=1)
Total runtime: 6158.10 msec
I'd like to get that time down. I suspect it is high this is because
the entire table
is being joined, then the limit occurs. When I add "commit_log.id =
1" to the WHERE clause,
the query plan changes to:
Limit (cost=30.42..30.43 rows=1 width=55) (actual time=0.96..0.99
rows=1 loops=1)
-> Unique (cost=30.42..30.43 rows=1 width=55) (actual
time=0.95..0.97 rows=1 loops=1)
-> Sort (cost=30.42..30.42 rows=1 width=55) (actual
time=0.94..0.94 rows=1 loops=1)
-> Nested Loop (cost=0.00..30.41 rows=1 width=55)
(actual time=0.61..0.64 rows=1 loops=1)
-> Index Scan using commit_log_pkey on
commit_log (cost=0.00..5.93 rows=1 width=51) (actual time=0.31..0.32
rows=1 loops=1)
-> Index Scan using commit_log_ports_cli on
commit_log_ports (cost=0.00..24.41 rows=6 width=4) (actual
time=0.25..0.27 rows=1 loops=1)
I think that confirms that my indexes and PK/FK are set correctly.
I could do something like:
SELECT distinct commit_log_id
FROM commit_log_ports
ORDER BY commit_log_id desc
LIMIT 100;
But that doesn't take into consider the fact that commits can be
added out of order
and are not necessarily added in commit_date order.
Any clues please?
thanks
--
Dan Langille
And yes, I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: using LIMIT only on primary table
@ 2002-07-28 18:13 Lee Harr <missive@frontiernet.net>
parent: Dan Langille <dan@langille.org>
0 siblings, 0 replies; 7+ messages in thread
From: Lee Harr @ 2002-07-28 18:13 UTC (permalink / raw)
To: pgsql-sql
In article <3D42D7AA.27447.3EE190A0@localhost>, "Dan Langille" wrote:
> This is an extension of the problem solved by
> http://archives.postgresql.org/pgsql-sql/2002-03/msg00020.php but
> with a slightly different complication.
>
> I want to get the last 100 port commits from the database. Commits
> are stored in
> commit_log and commit_log_ports relates commits to ports. A given
> commit may
> affect more than one port (i.e. there is a 1-N relationship between
> commit_log and
> commit_log_ports).
>
> So a starting point for the last 100 port commits is:
>
> explain analyze
> SELECT distinct commit_log.*
> FROM commit_log_ports, commit_log
> WHERE commit_log.id = commit_log_ports.commit_log_id
> ORDER BY commit_log.commit_date DESC, commit_log_ports.commit_log_id
> LIMIT 100;
>
I am not sure if this will help, but how about a subselect?
SELECT DISTINCT commit_log.*
FROM commit_log_ports,
(SELECT commit_log.id
FROM commit_log
ORDER BY commit_log.commit_date DESC
LIMIT 100) AS commit_log
WHERE commit_log.id = commit_log_ports.commit_log_id
ORDER BY commit_log.commit_date DESC, commit_log.id
LIMIT 100;
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-07-28 18:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-02 22:35 using LIMIT only on primary table Dan Langille <dan@langille.org>
2002-03-02 23:20 ` daq <daq@ugyvitelszolgaltato.hu>
2002-03-02 23:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-02 23:40 ` Dan Langille <dan@langille.org>
2002-03-03 06:00 ` Masaru Sugawara <rk73@sea.plala.or.jp>
2002-07-27 21:26 using LIMIT only on primary table Dan Langille <dan@langille.org>
2002-07-28 18:13 ` Lee Harr <missive@frontiernet.net>
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