selects with large offset really slow

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

Responses

Browse pgsql-general by date

  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