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

Re: Weird index or sort behaviour

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Weird index or sort behaviour
Date: 2009-08-19 11:00:32
Message-ID: alpine.DEB.2.00.0908191155150.19472@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, 18 Aug 2009, Tom Lane wrote:
>>                 ->  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?

Oh, definitely. I have run this test so many times, it's all going to be 
in the cache. Luckily, that's what we are looking at as a normal situation 
in production. Also, since the table is clustered on that index, I would 
expect the performance when it is out of cache to be fairly snappy anyway.

For reference, the table is 350 MB, the index is 238 MB, and the RAM in 
the machine is 4GB (although it's my desktop so it'll have all sorts of 
other rubbish using that up). Our servers have 16GB to 32GB of RAM, so no 
problem there.

Matthew

-- 
 I'm always interested when [cold callers] try to flog conservatories.
 Anyone who can actually attach a conservatory to a fourth floor flat
 stands a marginally better than average chance of winning my custom.
 (Seen on Usenet)

In response to

Responses

pgsql-performance by date

Next:From: Phoenix KiulaDate: 2009-08-19 13:33:20
Subject: PG 8.3 and server load
Previous:From: Ivan VorasDate: 2009-08-19 10:19:17
Subject: Re: SQL Query Performance - what gives?

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