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--