From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rainer Mager <rainer@vanten.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: difficulties with time based queries
Date: Sun, 05 Apr 2009 20:12:39 -0400
Message-ID: <5053.1238976759@sss.pgh.pa.us> (raw)
In-Reply-To: <003001c9b645$e8a7cec0$b9f76c40$@com>
References: <003001c9b645$e8a7cec0$b9f76c40$@com>
"Rainer Mager" <rainer@vanten.com> writes:
> explain analyze select * from ad_log where date(start_time) <
> date('2009-03-31') and date(start_time) >= date('2009-03-30');
> Bitmap Heap Scan on ad_log (cost=73372.57..3699152.24 rows=2488252
> width=32) (actual time=49792.862..64611.255 rows=2268490 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..72750.51
> rows=2488252 width=0) (actual time=49776.332..49776.332 rows=2268490
> loops=1)
> Index Cond: ((date(start_time) < '2009-03-31'::date) AND
> (date(start_time) >= '2009-03-30'::date))
> Total runtime: 65279.352 ms
Hmm ... it's pretty unusual to see the index fetch portion of a bitmap
scan take the bulk of the runtime. Usually that part is fast and where
the pain comes is in fetching from the heap. I wonder whether that
index has become bloated. How big are the table and the index
physically? (Look at pg_class.relpages, or if you want a really
accurate number try pg_relation_size().)
What Postgres version is this, exactly?
BTW, I think you've gone way overboard in your indexing of this table;
those indexes are certainly consuming well more space than the table
does, and a lot of them are redundant.
regards, tom lane
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: tgl@sss.pgh.pa.us, rainer@vanten.com
Subject: Re: difficulties with time based queries
In-Reply-To: <5053.1238976759@sss.pgh.pa.us>
* 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