selects with large offset really slow

Поиск
Список
Период
Сортировка

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

В списке pgsql-general по дате отправления:

Предыдущее
От: Chris Johnson
Дата:
Сообщение: Re: FreeBSD: SMP and PostgreSQL
Следующее
От: Medi Montaseri
Дата:
Сообщение: Re: AllocSetAlloc() error message