From: Decibel! <decibel@decibel.org>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: I/O on select count(*)
Date: Sat, 24 May 2008 15:06:56 -0400
Message-ID: <E18CFDAC-00FC-4107-ADD2-53AC09041BEE@decibel.org> (raw)
In-Reply-To: <Pine.GSO.4.64.0805180113450.8667@westnet.com>
References: <920841.71690.qm@web44808.mail.sp1.yahoo.com>
<482B1D5F.EE98.0025.0@wicourts.gov>
<Pine.GSO.4.64.0805142133250.27290@westnet.com>
<20080515020553.GN9838@alvh.no-ip.org>
<Pine.LNX.4.64.0805151245360.16756@aragorn.flymine.org>
<20080515155232.GC8221@alvh.no-ip.org>
<Pine.GSO.4.64.0805161118210.4987@westnet.com>
<20080516201528.GJ13061@alvh.no-ip.org>
<18382.1210992302@sss.pgh.pa.us>
<Pine.GSO.4.64.0805180113450.8667@westnet.com>
On May 18, 2008, at 1:28 AM, Greg Smith wrote:
> I just collected all the good internals information included in
> this thread and popped it onto http://wiki.postgresql.org/wiki/
> Hint_Bits where I'll continue to hack away at the text until it's
> readable. Thanks to everyone who answered my questions here,
> that's good progress toward clearing up a very underdocumented area.
>
> I note a couple of potential TODO items not on the official list
> yet that came up during this discussion:
>
> -Smooth latency spikes when switching commit log pages by
> preallocating cleared pages before they are needed
>
> -Improve bulk loading by setting "frozen" hint bits for tuple
> inserts which occur within the same database transaction as the
> creation of the table into which they're being inserted
>
> Did I miss anything? I think everything brought up falls either
> into one of those two or the existing "Consider having the
> background writer update the transaction status hint bits..." TODO.
-Evaluate impact of improved caching of CLOG per Greenplum:
Per Luke Longergan:
I'll find out if we can extract our code that did the work. It was
simple but scattered in a few routines. In concept it worked like this:
1 - Ignore if hint bits are unset, use them if set. This affects
heapam and vacuum I think.
2 - implement a cache for clog lookups based on the optimistic
assumption that the data was inserted in bulk. Put the cache one
call away from heapgetnext()
I forget the details of (2). As I recall, if we fall off of the
assumption, the penalty for long scans get large-ish (maybe 2X), but
since when do people full table scan when they're updates/inserts are
so scattered across TIDs? It's an obvious big win for DW work.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: decibel@decibel.org, gsmith@gregsmith.com
Subject: Re: I/O on select count(*)
In-Reply-To: <E18CFDAC-00FC-4107-ADD2-53AC09041BEE@decibel.org>
References: <920841.71690.qm@web44808.mail.sp1.yahoo.com> <482B1D5F.EE98.0025.0@wicourts.gov> <Pine.GSO.4.64.0805142133250.27290@westnet.com> <20080515020553.GN9838@alvh.no-ip.org> <Pine.LNX.4.64.0805151245360.16756@aragorn.flymine.org> <20080515155232.GC8221@alvh.no-ip.org> <Pine.GSO.4.64.0805161118210.4987@westnet.com> <20080516201528.GJ13061@alvh.no-ip.org> <18382.1210992302@sss.pgh.pa.us> <Pine.GSO.4.64.0805180113450.8667@westnet.com> <E18CFDAC-00FC-4107-ADD2-53AC09041BEE@decibel.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox