C.htm
text/html
<P>Thanks, I'll try those suggestions. But...
<P>Why can't PG just use an index? Say, look at the index for 'url', go to entry 90000, then get the next 100 entries? I was suprised that it retrieves *all* records then sorts them (when there's already a sorted index). I'm trying to switch from mysql - the same exact query with it is very fast with 100-500K+ rows, and a large offset doesn't seem to affect the query's speed.
<P>John
<P> <B><I>Richard Huxton <dev@archonet.com></I></B> wrote:
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">On Friday 07 Feb 2003 5:01 am, John Smith wrote:<BR>> There are 90K-100K records in each of two tables. This simple join is<BR>> really slow and the larger the offset, the longer it takes. Anything I can<BR>> do to speed it up (a lot)? I've double-checked and there are indexes on<BR>> everything used for joins and ordering.<BR><BR>> QUERY PLAN<BR>> ---------------------------------------------------------------------------<BR>>----------------------- <BR>Limit (cost=19546.62..19546.87 rows=100 width=62)<BR>> (actual time=20557.00..20558.00 rows=100 loops=1)<BR>> -> Sort (cost=19321.62..19571.32 rows=99881 width=62) (actual<BR>> time=19775.00..20410.00 rows=90101 loops=1) <BR>> Sort Key: l.url<BR>> -> Hash Join (cost=2471.00..7662.54 rows=99881 width=62) (actual<BR>> time=3013.00..12002.00 rows=100000 loops=1) Hash Cond: ("outer".id =<BR>> "in
ner".link_id)<BR><BR>It's the join and sort that's getting you. PG has to fetch and sort all the <BR>rows so it can discard 90,000 of them. I can't think of a good way for it to <BR>optimise this, though you might want to check your sort_mem is set high <BR>enough.<BR><BR>> explain analyze select l.id, l.url<BR>> from links l<BR>> inner join stats s<BR>> on l.id = s.link_id<BR>> and s.referrer_id = 1<BR>> order by l.url<BR>> limit 100<BR>> offset 90000;<BR><BR>There are three options you might want to look at:<BR><BR>1. Use a temporary table, then select from that for each page.<BR>2. Use a cursor, and just fetch 100 records at a time from it.<BR>3. Cheat and fetch where l.url>=X, remembering X as the highest url from the <BR>last set of results. This of course means pages of results will overlap.<BR><BR>-- <BR>Richard Huxton</BLOCKQUOTE><p><br><hr size=1>Do you Yahoo!?<br>
<a href="http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com">Yahoo! Mail Plus</a> - Powerful. Affordable. <a href="http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com">Sign up now</a>