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

Re: So, is COUNT(*) fast now?

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Kääriäinen Anssi <anssi(dot)kaariainen(at)thl(dot)fi>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: So, is COUNT(*) fast now?
Date: 2011-10-31 12:44:16
Message-ID: CA+TgmoZKCULEZDNPU008rCGtsvTZHC4r0BaykAjH6UrjfTVqVg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sun, Oct 30, 2011 at 8:02 AM, Kääriäinen Anssi
<anssi(dot)kaariainen(at)thl(dot)fi> wrote:
> Table size is around 600MB, index size is around 350MB and VM on-disk
> size is 16kB with default fillfactor. With fillfactor = 10, the VM size is 104
> KB, and table size is around 6GB.  The index size is the same.

What I think you're probably measuring here (oprofile would tell us
for sure) is that once the size of the table goes beyond about half a
gigabyte, it will have more than one page in the visibility map.  The
index-only scan code keeps the most recently used visibility map page
pinned to save on overhead, but if you're bouncing back and forth
between data in the first ~500MB of the table and data in the last
~100MB, each switch will result in dropping the current pin and
getting a new one, which figures to be fairly expensive.  With the
table is only a little over 500GB, you're probably only changing VM
pages every couple of tuples, but with a 6GB table just about every
tuple will switch to a new VM page.

Now, maybe you're right and the CPU caches are the more significant
effect.  But I wouldn't like to bet on it without seeing how much the
drop-and-get-new-pin operations are costing us.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Responses

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2011-10-31 13:14:48
Subject: Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?
Previous:From: Pavel StehuleDate: 2011-10-31 12:24:09
Subject: Re: pgsql_fdw, FDW for PostgreSQL server

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