To: "Luke Lonergan" <llonergan@greenplum.com>
Cc: "Pavan Deolasee" <pavan.deolasee@gmail.com>,
	"Greg Smith" <gsmith@gregsmith.com>,
	"Alvaro Herrera" <alvherre@commandprompt.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: I/O on select count(*)
In-Reply-To: <C451C651.5EE8B%llonergan@greenplum.com> (Luke Lonergan's message
	of "Thu\, 15 May 2008 10\:52\:01 +0800")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <C451C651.5EE8B%llonergan@greenplum.com>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Thu, 15 May 2008 23:30:41 +0100
Message-ID: <87hcczqjxq.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

"Luke Lonergan" <llonergan@greenplum.com> writes:

> BTW =AD we=B9ve removed HINT bit checking in Greenplum DB and improved the
> visibility caching which was enough to provide performance at the same le=
vel
> as with the HINT bit optimization, but avoids this whole =B3write the dat=
a,
> write it to the log also, then write it again just for good measure=B2
> behavior.
>
> For people doing data warehousing work like the poster, this Postgres
> behavior is miserable.  It should be fixed for 8.4 for sure (volunteers?)

For people doing data warehousing I would think the trick would be to do
something like what we do to avoid WAL logging for tables created in the sa=
me
transaction.=20

That is, if you're loading a lot of data at the same time then all of that
data is going to be aborted or committed and that will happen at the same
time. Ideally we would find a way to insert the data with the hint bits
already set to committed and mark the section of the table as being only
provisionally extended so other transactions wouldn't even look at those pa=
ges
until the transaction commits.

This is similar to the abortive attempt to have the abovementioned WAL logg=
ing
trick insert the records pre-frozen. I recall there were problems with that
idea though but I don't recall if they were insurmountable or just required
more work.

--=20
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!