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

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

From: Anssi Kääriäinen <anssi(dot)kaariainen(at)thl(dot)fi>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: So, is COUNT(*) fast now?
Date: 2011-10-31 13:51:39
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On 10/31/2011 02:44 PM, Robert Haas wrote:
> 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.
Maybe I should have left the analysis part out of the post,
I don't know the internals, so my analysis is likely to be wrong.
Now that I think of it, claiming that the cache effect is 50%
of the runtime is likely a little wrong...

However the part about clustering being important is still correct.
According to the test, you can get 50% overhead because of
random access to the VM.

Stupid question, but why not keep the whole VM pinned?

  - Anssi

In response to


pgsql-hackers by date

Next:From: Robert HaasDate: 2011-10-31 14:03:00
Subject: Re: So, is COUNT(*) fast now?
Previous:From: Andrew DunstanDate: 2011-10-31 13:14:48
Subject: Re: Thoughts on "SELECT * EXCLUDING (...) FROM ..."?

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