pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (54+ messages)  latest in thread

Message-ID: <E18CFDAC-00FC-4107-ADD2-53AC09041BEE@decibel.org>
Permalink:  ../E18CFDAC-00FC-4107-ADD2-53AC09041BEE@decibel.org/
Also on:    postgresql.org/message-id/E18CFDAC-00FC-4107-ADD2-53AC09041BEE@decibel.org

 · 

reply

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