Skip site navigation (1) Skip section navigation (2)

Re: Weird index or sort behaviour

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matthew Wakeling <matthew(at)flymine(dot)org>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Weird index or sort behaviour
Date: 2009-08-18 19:09:52
Message-ID: 27347.1250622592@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Matthew Wakeling <matthew(at)flymine(dot)org> writes:
>                 ->  Index Scan using locationbin8000__subjectobjectbin on locationbin8000 l1
>                       (cost=0.00..71635.23 rows=657430 width=20)
>                       (actual time=0.056..170.857 rows=664588 loops=1)
>                       Index Cond: (subjecttype = 'GeneFlankingRegion'::text)
>                 ->  Index Scan using locationbin8000__subjectobjectbin on locationbin8000 l2
>                       (cost=0.00..71635.23 rows=657430 width=20)
>                       (actual time=0.020..9594.466 rows=38231659 loops=1)
>                       Index Cond: (l2.subjecttype = 'GeneFlankingRegion'::text)

>  ... So on average, we will be rewinding by 57 rows each time.

As indeed is reflected in those actual rowcounts.  (The estimated
counts and costs don't include re-fetching, but the actuals do.)

Even more interesting, the actual runtime is about 56x different too,
which implies that Matthew's re-fetches are not noticeably cheaper than
the original fetches.  I'd be surprised if that were true in an
indexscan pulling from disk (you'd expect recently-touched rows to stay
cached for awhile).  But it could easily be true if the whole table were
cached already.  Matthew, how big is this table compared to your RAM?
Were you testing a case in which it'd be in cache?

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Karl DenningerDate: 2009-08-18 19:45:11
Subject: SQL Query Performance - what gives?
Previous:From: Matthew WakelingDate: 2009-08-18 18:40:18
Subject: Re: Weird index or sort behaviour

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group