Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 66CB864FCD8
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 13:29:02 -0400 (AST)
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 66205-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 13:28:55 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.29])
	by mail.postgresql.org (Postfix) with ESMTP id ECA82650014
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 13:28:54 -0400 (AST)
Received: by yx-out-2324.google.com with SMTP id 8so221126yxb.73
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 09:28:54 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to
	:subject:cc:in-reply-to:mime-version:content-type:references;
	bh=WYTz1ptcPd8ySG21pCcaBKb27ESgIX8Mi+pYnWMDLJ8=;
	b=dPxmk1tUZ1F99cAQCYJW2VNnfCTdU9V5bZtVIfJ9gK44lHjag8wa4qamE4YtpsIT3W
	HgUa0k7ZdD/XWCpwqLto4SSV8ipC0GPPF+8BAQDdcjckfL9UADbt2wxXjU849YdfoQir
	63SjNUuiySN8C7f+qYRr4kGr1Kh50eEk9PusQ=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:references;
	b=VtufIMfjY/Ubq8/ccEEKoel6hNVVmvbKCfV8/u1+jR4Ctx9taOH9aCtf68zGf2nBF6
	sLsDUeV76VyVq2K15wVr//bjteUNzChJwGSNDftyfrpJab7b6DKvh7UxRPk9cZBNWdo+
	K7Q1KJNGRyzSKX/pKbQcydJIiUdAgfM0XFDzo=
Received: by 10.142.161.13 with SMTP id j13mr3578179wfe.123.1226510933804;
	Wed, 12 Nov 2008 09:28:53 -0800 (PST)
Received: by 10.142.79.21 with HTTP; Wed, 12 Nov 2008 09:28:53 -0800 (PST)
Message-ID: <1d709ecc0811120928n3c183df5kb7b7d50dee6f73ed@mail.gmail.com>
Date: Wed, 12 Nov 2008 09:28:53 -0800
From: "Vladimir Sitnikov" <sitnikov.vladimir@gmail.com>
To: Andrus <kobruleht2@hot.ee>
Subject: Re: Increasing select max(datecol) from bilkaib where
	datecol<=date'2008-11-01' and (cr='00' or db='00') speed
Cc: pgsql-performance@postgresql.org
In-Reply-To: <gff26k$2etc$1@news.hub.org>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_15028_5754301.1226510933756"
References: <gff26k$2etc$1@news.hub.org>
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: 200811/99
X-Sequence-Number: 31830

------=_Part_15028_5754301.1226510933756
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Wed, Nov 12, 2008 at 9:02 AM, Andrus <kobruleht2@hot.ee> wrote:

> 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 am afraid I do not see a way to use bitmaps to get any improvement here:
the server will still need to read the whole indices to figure out the
answer.

I suggest you to create two more indices:

create index date_with_zero_cr on bilkaib(date) where cr='00';
create index date_with_zero_db on bilkaib(date) where db='00';

And rewrite query as follows:
select greatest(
   (select max(date) from bilkaib where datecol<=date'2008-11-01' and
cr='00'),
   (select max(date) from bilkaib where datecol<=date'2008-11-01' and
db='00'))


Regards,
Vladimir Sitnikov

------=_Part_15028_5754301.1226510933756
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<br><br><div class="gmail_quote">On Wed, Nov 12, 2008 at 9:02 AM, Andrus <span dir="ltr">&lt;<a href="mailto:kobruleht2@hot.ee">kobruleht2@hot.ee</a>&gt;</span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
There are columns<br>
kuupaev date, &nbsp;cr char(10), db char(10)<br>
and regular indexes &nbsp;for all those fields.<br>
bilkaib table contains large number of rows.<br>
<br>
The following query takes too much time.<br>
How to make it faster ?<br>
I think PostgreSql should use multiple indexes as bitmaps to speed it.</blockquote><div>I am afraid I do not see a way to use bitmaps to get any improvement here: the server will still need to read the whole indices to figure out the answer.<br>
<br>I suggest you to create two more indices:<br><br><span style="font-family: courier new,monospace;">create index date_with_zero_cr on bilkaib(date) where cr=&#39;00&#39;;</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">create index date_with_zero_db on bilkaib(date) where db=&#39;00&#39;;</span><br><br>And rewrite query as follows:<br><span style="font-family: courier new,monospace;">select greatest(</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">&nbsp;&nbsp; (select max(date) from bilkaib where datecol&lt;=date&#39;2008-11-01&#39; and cr=&#39;00&#39;), </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">&nbsp;&nbsp; (select max(date) from bilkaib where datecol&lt;=date&#39;2008-11-01&#39; and db=&#39;00&#39;))</span><br>
<br><br>
Regards,<br>Vladimir Sitnikov<br></div></div><br>

------=_Part_15028_5754301.1226510933756--