From: Rainer Mager <rainer@vanten.com>
To: 'Rainer Mager' <rainer@vanten.com>
To: pgsql-performance@postgresql.org
Subject: Re: difficulties with time based queries
Date: Thu, 9 Apr 2009 07:49:39 +0900
Message-ID: <000501c9b89c$4cb4b510$e61e1f30$@com> (raw)
References: <003001c9b645$e8a7cec0$b9f76c40$@com>
<e7f9235d0904051646j7e8374afncf79e3708ae40c20@mail.gmail.com>
<003501c9b656$a9c73fe0$fd55bfa0$@com>
<7371.1238981597@sss.pgh.pa.us>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rainer@vanten.com
Subject: Re: difficulties with time based queries
In-Reply-To: <000501c9b89c$4cb4b510$e61e1f30$@com>
* 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