Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 91A81632D79 for ; Wed, 8 Apr 2009 19:49:14 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 59892-08 for ; Wed, 8 Apr 2009 19:49:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.vanten.com (doc.vanten.com [203.216.141.136]) by mail.postgresql.org (Postfix) with ESMTP id 6634C6325BD for ; Wed, 8 Apr 2009 19:49:12 -0300 (ADT) Received: from hexagon.office.vanten.com (hexagon.office.vanten.com [192.168.220.16]) by mail.vanten.com (Postfix) with ESMTP id AB39C4111 for ; Thu, 9 Apr 2009 07:49:08 +0900 (JST) Received: from Espresso (unknown [192.168.220.80]) by hexagon.office.vanten.com (Postfix) with ESMTP id 6D6603C735 for ; Thu, 9 Apr 2009 07:49:08 +0900 (JST) From: "Rainer Mager" To: "'Rainer Mager'" , References: <003001c9b645$e8a7cec0$b9f76c40$@com> <003501c9b656$a9c73fe0$fd55bfa0$@com> <7371.1238981597@sss.pgh.pa.us> In-Reply-To: Subject: Re: difficulties with time based queries Date: Thu, 9 Apr 2009 07:49:39 +0900 Message-ID: <000501c9b89c$4cb4b510$e61e1f30$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0006_01C9B8E7.BC9C5D10" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Acm2V67taS6N+dZOSV+76Ry0403szgAD8gPgACs3BPA= Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200904/163 X-Sequence-Number: 33530 This is a multipart message in MIME format. ------=_NextPart_000_0006_01C9B8E7.BC9C5D10 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit So, I defragged my disk and reran my original query and it got a little better, but still far higher than I'd like. I then rebuilt (dropped and recreated) the ad_log_date_all index and reran the query and it is quite a bit better: # explain analyze select * from ad_log where date(start_time) < date('2009-03-31') and date(start_time) >= date('2009-03-30'); QUERY PLAN ---------------------------------------------------------------------------- ------------------------------------------------------------------ Bitmap Heap Scan on ad_log (cost=64770.21..3745596.62 rows=2519276 width=32) (actual time=1166.479..13862.107 rows=2275167 loops=1) Recheck Cond: ((date(start_time) < '2009-03-31'::date) AND (date(start_time) >= '2009-03-30'::date)) -> Bitmap Index Scan on ad_log_date_all (cost=0.00..64140.39 rows=2519276 width=0) (actual time=1143.582..1143.582 rows=2275167 loops=1) Index Cond: ((date(start_time) < '2009-03-31'::date) AND (date(start_time) >= '2009-03-30'::date)) Total runtime: 14547.885 ms During the query the disk throughput peaked at 30MB/s and was mostly at around 20MB/s, much better. So, a few questions: What can I do to prevent the index from getting bloated, or in whatever state it was in? What else can I do to further improve queries on this table? Someone suggested posting details of my conf file. Which settings are most likely to be useful for this? Any other suggestions? Thanks, --Rainer ------=_NextPart_000_0006_01C9B8E7.BC9C5D10 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

So, I defragged my disk and reran my original query and it got a little = better, but still far higher than I'd like. I then rebuilt (dropped and recreated) = the ad_log_date_all index and reran the query and it is quite a bit = better:

 

=

# = explain analyze select * from ad_log where date(start_time) < date('2009-03-31') and date(start_time) >=3D date('2009-03-30');

           &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;      QUERY PLAN

-------------------------------------------------------------------= -------------------------------------------------------------------------= --

 Bitmap Heap Scan on ad_log  (cost=3D64770.21..3745596.62 rows=3D2519276 = width=3D32) (actual time=3D1166.479..13862.107 rows=3D2275167 = loops=3D1)

   Recheck Cond: ((date(start_time) < '2009-03-31'::date) AND = (date(start_time) >=3D '2009-03-30'::date))

   ->  Bitmap Index Scan on ad_log_date_all  = (cost=3D0.00..64140.39 rows=3D2519276 width=3D0) (actual time=3D1143.582..1143.582 = rows=3D2275167 loops=3D1)

         Index Cond: ((date(start_time) < '2009-03-31'::date) AND = (date(start_time) >=3D '2009-03-30'::date))

 Total runtime: 14547.885 ms

 

=

 

During the query the disk throughput peaked at 30MB/s and = was mostly at around 20MB/s, much better.

 

So, a few questions:

 

What can I do to prevent the index from getting bloated, or = in whatever state it was in?

 

What else can I do to further improve queries on this = table? Someone suggested posting details of my conf file. Which settings are most = likely to be useful for this?

 

Any other suggestions?

 

 

Thanks,

 

--Rainer

------=_NextPart_000_0006_01C9B8E7.BC9C5D10--