Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 1D7F22E0047 for ; Wed, 14 May 2008 23:53:08 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 37815-02 for ; Wed, 14 May 2008 23:52:48 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from exprod6og106.obsmtp.com (exprod6og106.obsmtp.com [64.18.1.191]) by developer.postgresql.org (Postfix) with SMTP id 2B9DF2E004F for ; Wed, 14 May 2008 23:52:54 -0300 (ADT) Received: from source ([63.240.6.3]) (using TLSv1) by exprod6ob106.postini.com ([64.18.5.12]) with SMTP; Wed, 14 May 2008 19:52:51 PDT Received: from d01smtp06.Mi8.com ([172.16.1.239]) by Outbound02.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 14 May 2008 22:55:32 -0400 Received: from MI8NYCMAIL08.Mi8.com ([172.16.1.164]) by d01smtp06.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 14 May 2008 22:55:32 -0400 Received: from 222.128.199.82 ([222.128.199.82]) by MI8NYCMAIL08.Mi8.com ([172.16.1.190]) via Exchange Front-End Server mi8owa.mi8.com ([172.16.1.106]) with Microsoft Exchange Server HTTP-DAV ; Thu, 15 May 2008 02:54:49 +0000 User-Agent: Microsoft-Entourage/12.1.0.080305 Date: Thu, 15 May 2008 10:52:01 +0800 Subject: Re: I/O on select count(*) From: Luke Lonergan To: Pavan Deolasee , Greg Smith CC: Alvaro Herrera , Message-ID: Thread-Topic: [PERFORM] I/O on select count(*) Thread-Index: Aci2NY6smkjS3IfDSluaZeatjw9/XwAARdYc In-Reply-To: <2e78013d0805141940x50c3f51u439019e8e592959@mail.gmail.com> Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3293693521_4061378" X-OriginalArrivalTime: 15 May 2008 02:55:32.0678 (UTC) FILETIME=[24321E60:01C8B637] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.419 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, MIME_QP_LONG_LINE=1.819, RCVD_IN_DNSWL_MED=-4, RCVD_NUMERIC_HELO=2.599 X-Spam-Level: X-Archive-Number: 200805/194 X-Sequence-Number: 30162 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3293693521_4061378 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable 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 leve= l as with the HINT bit optimization, but avoids this whole =B3write the data, 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?) BTW =AD for the poster=B9s benefit, you should implement partitioning by date, then load each partition and VACUUM ANALYZE after each load. You probably won=B9t need the date index anymore =AD so your load times will vastly improve (no indexes), you=B9ll store less data (no indexes) and you=B9ll be able to do simpler data management with the partitions. You may also want to partition AND index if you do a lot of short range selective date predicates. Example would be: partition by day, index on date field, queries selective on date ranges by hour will then select out only the day needed, then index scan to get the hourly values. Typically time-oriented data is nearly time sorted anyway, so you=B9ll also get the benefit of a clustered index. - Luke On 5/15/08 10:40 AM, "Pavan Deolasee" wrote: > On Thu, May 15, 2008 at 7:51 AM, Greg Smith wrote: >> > >> > >> > So is vacuum helpful here because it will force all that to happen in = one >> > batch? To put that another way: if I've run a manual vacuum, is it t= rue >> > that it will have updated all the hint bits to XMIN_COMMITTED for all = the >> > tuples that were all done when the vacuum started? >> > >=20 > Yes. For that matter, even a plain SELECT or count(*) on the entire > table is good enough. That will check every tuple for visibility and > set it's hint bits. >=20 > Another point to note is that the hint bits are checked and set on a > per tuple basis. So especially during index scan, the same heap page > may get rewritten many times. I had suggested in the past that > whenever we set hint bits for a tuple, we should check all other > tuples in the page and set their hint bits too to avoid multiple > writes of the same page. I guess the idea got rejected because of lack > of benchmarks to prove the benefit. >=20 > Thanks, > Pavan >=20 > -- > Pavan Deolasee > EnterpriseDB http://www.enterprisedb.com >=20 > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org= ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >=20 --B_3293693521_4061378 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Re: [PERFORM] I/O on select count(*) BTW – we’ve removed HINT bit checking in Greenplum DB an= d improved the visibility caching which was enough to provide performance at= the same level as with the HINT bit optimization, but avoids this whole = 220;write the data, write it to the log also, then write it again just for g= ood measure” behavior.

For people doing data warehousing work like the poster, this Postgres behav= ior is miserable.  It should be fixed for 8.4 for sure (volunteers?)
BTW – for the poster’s benefit, you should implement partitioni= ng by date, then load each partition and VACUUM ANALYZE after each load. &nb= sp;You probably won’t need the date index anymore – so your load= times will vastly improve (no indexes), you’ll store less data (no in= dexes) and you’ll be able to do simpler data management with the parti= tions.

You may also want to partition AND index if you do a lot of short range sel= ective date predicates.  Example would be: partition by day, index on d= ate field, queries selective on date ranges by hour will then select out onl= y the day needed, then index scan to get the hourly values.  Typically = time-oriented data is nearly time sorted anyway, so you’ll also get th= e benefit of a clustered index.

- Luke


On 5/15/08 10:40 AM, "Pavan Deolasee" <pavan.deolasee@gmail.co= m> wrote:

On Thu, May 15, 2008 at 7:51 AM, Gr= eg Smith <gsmith@gregsmith.com> wrote:
>
>
> So is vacuum helpful here because it will force all that to happen in = one
> batch?  To put that another way:  if I've run a manual vacuu= m, is it true
> that it will have updated all the hint bits to XMIN_COMMITTED for all = the
> tuples that were all done when the vacuum started?
>

Yes. For that matter, even a plain SELECT or count(*) on the entire
table is good enough. That will check every tuple for visibility and
set it's hint bits.

Another point to note is that the hint bits are checked and set on a
per tuple basis. So especially during index scan, the same heap page
may get rewritten many times. I had suggested in the past that
whenever we set hint bits for a tuple, we should check all other
tuples in the page and set their hint bits too to avoid multiple
writes of the same page. I guess the idea got rejected because of lack
of benchmarks to prove the benefit.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.= com

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)<= BR> To make changes to your subscription:
http://www.p= ostgresql.org/mailpref/pgsql-performance

--B_3293693521_4061378--