From: | John Smith <john_smith_45678(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | selects with large offset really slow |
Date: | 2003-02-07 05:01:14 |
Message-ID: | 20030207050114.45798.qmail@web40711.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
There are 90K-100K records in each of two tables. This simple join is really slow and the larger the offset, the longer it takes. Anything I can do to speed it up (a lot)? I've double-checked and there are indexes on everything used for joins and ordering.
############################
explain analyze select l.id, l.url
from links l
inner join stats s
on l.id = s.link_id
and s.referrer_id = 1
order by l.url
limit 100
offset 90000;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Limit (cost=19546.62..19546.87 rows=100 width=62) (actual time=20557.00..20558.00 rows=100 loops=1)
-> Sort (cost=19321.62..19571.32 rows=99881 width=62) (actual time=19775.00..20410.00 rows=90101 loops=1)
Sort Key: l.url
-> Hash Join (cost=2471.00..7662.54 rows=99881 width=62) (actual time=3013.00..12002.00 rows=100000 loops=1)
Hash Cond: ("outer".id = "inner".link_id)
-> Seq Scan on links l (cost=0.00..2444.81 rows=99881 width=42) (actual time=65.00..1790.00 rows=99881 loops=1)
-> Hash (cost=2221.00..2221.00 rows=100000 width=20) (actual time=2946.00..2946.00 rows=0 loops=1)
-> Seq Scan on stats s (cost=0.00..2221.00 rows=100000 width=20) (actual time=36.00..1936.00 rows=100000 loops=1)
Filter: (referrer_id = 1)
Total runtime: 20571.00 msec
(10 rows)
---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
From | Date | Subject | |
---|---|---|---|
Next Message | Medi Montaseri | 2003-02-07 05:31:28 | Re: AllocSetAlloc() error message |
Previous Message | Chris Johnson | 2003-02-07 04:55:49 | Re: FreeBSD: SMP and PostgreSQL |