From klodoma@ar-sd.net Thu Jan  20 08:52:50 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2A1193A4110
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 20 Jan 2005 08:52:50 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 54393-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 20 Jan 2005 08:52:42 +0000 (GMT)
Received: from ar-sd.net (unknown [82.77.155.72])
	by svr1.postgresql.org (Postfix) with ESMTP id 6D93C3A1D6B
	for <pgsql-sql@postgresql.org>; Thu, 20 Jan 2005 08:52:42 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
	by ar-sd.net (Postfix) with ESMTP id 423AA2130D
	for <pgsql-sql@postgresql.org>; Thu, 20 Jan 2005 10:48:20 +0200 (EET)
Received: from ar-sd.net ([127.0.0.1])
	by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 01695-01 for <pgsql-sql@postgresql.org>;
	Thu, 20 Jan 2005 10:48:17 +0200 (EET)
Received: from forge (unknown [192.168.0.11])
	by ar-sd.net (Postfix) with SMTP id 6DF3C1E9BC
	for <pgsql-sql@postgresql.org>; Thu, 20 Jan 2005 10:48:16 +0200 (EET)
Message-ID: <006b01c4fecd$6d37c9e0$0b00a8c0@forge>
From: "Andrei Bintintan" <klodoma@ar-sd.net>
To: <pgsql-sql@postgresql.org>
Subject: SQL timestamp to date cast
Date: Thu, 20 Jan 2005 10:52:50 +0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0068_01C4FEDE.2F2671E0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at ar-sd.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.048 tagged_above=0 required=5 tests=AWL, HTML_50_60,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200501/157
X-Sequence-Number: 19945

This is a multi-part message in MIME format.

------=_NextPart_000_0068_01C4FEDE.2F2671E0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,=20

I have the following query:

SELECT DISTINCT(nummer) FROM user_action WHERE =
datetime::date=3D'11/11/2004' AND id_action IN (5,6,9)

Now, datetime has the type timestamp. How can I make an index or write =
different this query so that it runs faster? It really takes some time =
sometimes. Usually about 3-4secs. user_action has about 300k rows and =
increasing ~ 5-10k a day.=20


Explain analyze SELECT DISTINCT(nummer) FROM user_action WHERE =
datetime::date=3D'11/11/2004' AND id_action IN (5,6,9)

Unique  (cost=3D18141.71..18143.72 rows=3D45 width=3D4) (actual =
time=3D418.122..418.340 rows=3D85 loops=3D1)
  ->  Sort  (cost=3D18141.71..18142.72 rows=3D402 width=3D4) (actual =
time=3D418.119..418.194 rows=3D192 loops=3D1)
        Sort Key: nummer
        ->  Seq Scan on user_action  (cost=3D0.00..18124.33 rows=3D402 =
width=3D4) (actual time=3D366.240..417.890 rows=3D192 loops=3D1)
              Filter: (((datetime)::date =3D '2004-11-11'::date) AND =
((id_action =3D 5) OR (id_action =3D 6) OR (id_action =3D 9)))
Total runtime: 418.419 ms


Best regards.
Andy.
------=_NextPart_000_0068_01C4FEDE.2F2671E0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2523" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi, </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have the following =
query:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>SELECT DISTINCT(nummer) FROM =
user_action WHERE=20
datetime::date=3D'11/11/2004' AND id_action IN (5,6,9)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>Now, datetime has the type timestamp. =
How can I=20
make an index or write different this query so that it runs faster? It =
really=20
takes some time sometimes. Usually about 3-4secs. user_action has about =
300k=20
rows and increasing ~ 5-10k a day. </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Explain </FONT><FONT face=3DArial =
size=3D2>analyze=20
SELECT DISTINCT(nummer) FROM user_action WHERE =
datetime::date=3D'11/11/2004' AND=20
id_action IN (5,6,9)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></DIV>
<DIV><FONT face=3DArial size=3D2>Unique&nbsp; (cost=3D18141.71..18143.72 =
rows=3D45=20
width=3D4) (actual time=3D418.122..418.340 rows=3D85 =
loops=3D1)<BR>&nbsp; -&gt;&nbsp;=20
Sort&nbsp; (cost=3D18141.71..18142.72 rows=3D402 width=3D4) (actual=20
time=3D418.119..418.194 rows=3D192=20
loops=3D1)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key:=20
nummer<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq =
Scan on=20
user_action&nbsp; (cost=3D0.00..18124.33 rows=3D402 width=3D4) (actual=20
time=3D366.240..417.890 rows=3D192=20
loops=3D1)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;=20
Filter: (((datetime)::date =3D '2004-11-11'::date) AND ((id_action =3D =
5) OR=20
(id_action =3D 6) OR (id_action =3D 9)))<BR>Total runtime: 418.419=20
ms<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Best regards.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Andy.</FONT></DIV></BODY></HTML>

------=_NextPart_000_0068_01C4FEDE.2F2671E0--




From mike@fuhr.org Thu Jan  20 09:34:09 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id C25333A4353
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 20 Jan 2005 09:34:09 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 61015-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 20 Jan 2005 09:34:02 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 979E93A44B7
	for <pgsql-sql@postgresql.org>; Thu, 20 Jan 2005 09:34:02 +0000 (GMT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j0K9XWZY006176
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Thu, 20 Jan 2005 02:33:34 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j0K9XVIQ037715;
	Thu, 20 Jan 2005 02:33:32 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j0K9XVkf037714;
	Thu, 20 Jan 2005 02:33:31 -0700 (MST) (envelope-from mfuhr)
Date: Thu, 20 Jan 2005 02:33:31 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL timestamp to date cast
Message-ID: <20050120093331.GA37672@winnie.fuhr.org>
References: <006b01c4fecd$6d37c9e0$0b00a8c0@forge>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <006b01c4fecd$6d37c9e0$0b00a8c0@forge>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200501/158
X-Sequence-Number: 19946

On Thu, Jan 20, 2005 at 10:52:50AM +0200, Andrei Bintintan wrote:

> SELECT DISTINCT(nummer)
> FROM user_action
> WHERE datetime::date='11/11/2004' AND id_action IN (5,6,9)
> 
> Now, datetime has the type timestamp. How can I make an index or write
> different this query so that it runs faster?

You could create an index on datetime and rewrite your queries:

CREATE INDEX user_action_datetime_idx ON user_action (datetime);

SELECT DISTINCT(nummer)
FROM user_action
WHERE datetime >= '11/11/2004' AND datetime < '11/12/2004'
  AND id_action IN (5,6,9);

Another possibility would be to create a functional index on datetime:

CREATE INDEX user_action_date_idx ON user_action (date(datetime));

SELECT DISTINCT(nummer)
FROM user_action
WHERE date(datetime) = '11/11/2004' AND id_action IN (5,6,9);

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



From klodoma@ar-sd.net Thu Jan  20 09:41:44 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D00123A432A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 20 Jan 2005 09:41:44 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 63353-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 20 Jan 2005 09:41:30 +0000 (GMT)
Received: from ar-sd.net (unknown [82.77.155.72])
	by svr1.postgresql.org (Postfix) with ESMTP id C198D3A40AB
	for <pgsql-sql@postgresql.org>; Thu, 20 Jan 2005 09:41:31 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
	by ar-sd.net (Postfix) with ESMTP id AE5C31E9BC;
	Thu, 20 Jan 2005 11:37:07 +0200 (EET)
Received: from ar-sd.net ([127.0.0.1])
	by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 02819-09; Thu, 20 Jan 2005 11:37:06 +0200 (EET)
Received: from forge (unknown [192.168.0.11])
	by ar-sd.net (Postfix) with SMTP id 4B8641E9B9;
	Thu, 20 Jan 2005 11:37:06 +0200 (EET)
Message-ID: <010a01c4fed4$3fba1ca0$0b00a8c0@forge>
From: "Andrei Bintintan" <klodoma@ar-sd.net>
To: "Michael Fuhr" <mike@fuhr.org>
Cc: <pgsql-sql@postgresql.org>
References: <006b01c4fecd$6d37c9e0$0b00a8c0@forge>
	<20050120093331.GA37672@winnie.fuhr.org>
Subject: Re: SQL timestamp to date cast
Date: Thu, 20 Jan 2005 11:41:41 +0200
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at ar-sd.net
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.016 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200501/160
X-Sequence-Number: 19948

> Another possibility would be to create a functional index on datetime:
>
> CREATE INDEX user_action_date_idx ON user_action (date(datetime));

GREAT!!! I thought it is possible but I didn't knew how to make such 
indexes. :))

Thank you !!!
Andy.

----- Original Message ----- 
From: "Michael Fuhr" <mike@fuhr.org>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, January 20, 2005 11:33 AM
Subject: Re: [SQL] SQL timestamp to date cast


> On Thu, Jan 20, 2005 at 10:52:50AM +0200, Andrei Bintintan wrote:
>
>> SELECT DISTINCT(nummer)
>> FROM user_action
>> WHERE datetime::date='11/11/2004' AND id_action IN (5,6,9)
>>
>> Now, datetime has the type timestamp. How can I make an index or write
>> different this query so that it runs faster?
>
> You could create an index on datetime and rewrite your queries:
>
> CREATE INDEX user_action_datetime_idx ON user_action (datetime);
>
> SELECT DISTINCT(nummer)
> FROM user_action
> WHERE datetime >= '11/11/2004' AND datetime < '11/12/2004'
>  AND id_action IN (5,6,9);
>
> Another possibility would be to create a functional index on datetime:
>
> CREATE INDEX user_action_date_idx ON user_action (date(datetime));
>
> SELECT DISTINCT(nummer)
> FROM user_action
> WHERE date(datetime) = '11/11/2004' AND id_action IN (5,6,9);
>
> -- 
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
> 




From mike@fuhr.org Thu Jan  20 09:51:48 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id F40553A427D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 20 Jan 2005 09:51:48 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 64036-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 20 Jan 2005 09:51:36 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 16A3B3A4467
	for <pgsql-sql@postgresql.org>; Thu, 20 Jan 2005 09:51:37 +0000 (GMT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j0K9pHqD006193
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Thu, 20 Jan 2005 02:51:19 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j0K9pHmw037797;
	Thu, 20 Jan 2005 02:51:17 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j0K9pGec037796;
	Thu, 20 Jan 2005 02:51:16 -0700 (MST) (envelope-from mfuhr)
Date: Thu, 20 Jan 2005 02:51:16 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL timestamp to date cast
Message-ID: <20050120095116.GA37770@winnie.fuhr.org>
References: <006b01c4fecd$6d37c9e0$0b00a8c0@forge>
	<20050120093331.GA37672@winnie.fuhr.org>
	<010a01c4fed4$3fba1ca0$0b00a8c0@forge>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <010a01c4fed4$3fba1ca0$0b00a8c0@forge>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200501/161
X-Sequence-Number: 19949

On Thu, Jan 20, 2005 at 11:41:41AM +0200, Andrei Bintintan wrote:
> >Another possibility would be to create a functional index on datetime:
> >
> >CREATE INDEX user_action_date_idx ON user_action (date(datetime));
> 
> GREAT!!! I thought it is possible but I didn't knew how to make such 
> indexes. :))

See the "Indexes on Expressions" section in the "Indexes" chapter
of the documentation.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/