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
