From: Andrus <kobruleht2@hot.ee>
To: pgsql-performance@postgresql.org
Subject: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed
Date: Wed, 12 Nov 2008 19:02:10 +0200
Message-ID: <gff26k$2etc$1@news.hub.org> (raw)
There are columns
kuupaev date, cr char(10), db char(10)
and regular indexes for all those fields.
bilkaib table contains large number of rows.
The following query takes too much time.
How to make it faster ?
I think PostgreSql should use multiple indexes as bitmaps to speed it.
I can re-write this query in any way or split to multiple statements if this
makes it faster.
Andrus.
explain analyze select max(kuupaev) from bilkaib where
kuupaev<=date'2008-11-01' and (cr='00' or db='00')
"Result (cost=339.75..339.76 rows=1 width=0) (actual
time=52432.256..52432.260 rows=1 loops=1)"
" InitPlan"
" -> Limit (cost=0.00..339.75 rows=1 width=4) (actual
time=52432.232..52432.236 rows=1 loops=1)"
" -> Index Scan Backward using bilkaib_kuupaev_idx on bilkaib
(cost=0.00..1294464.73 rows=3810 width=4) (actual time=52432.222..52432.222
rows=1 loops=1)"
" Index Cond: (kuupaev <= '2008-11-01'::date)"
" Filter: ((kuupaev IS NOT NULL) AND ((cr = '00'::bpchar) OR
(db = '00'::bpchar)))"
"Total runtime: 52432.923 ms"
"PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0,
pie-8.7.9)"
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: kobruleht2@hot.ee
Subject: Re: Increasing select max(datecol) from bilkaib where datecol<=date'2008-11-01' and (cr='00' or db='00') speed
In-Reply-To: <gff26k$2etc$1@news.hub.org>
* 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