X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 36C1352886 for ; Wed, 22 Jun 2005 06:10:52 -0300 (ADT) 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 65425-03 for ; Wed, 22 Jun 2005 09:10:43 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.201]) by svr1.postgresql.org (Postfix) with ESMTP id D4F1B52923 for ; Wed, 22 Jun 2005 06:10:40 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 68so195317wra for ; Wed, 22 Jun 2005 02:10:42 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=AAd7ZNMTq37s+Fefgasrllmp8sy57MuvQA7DLLAD3VluAzeTstsAQSvb4/km+fGYsKGnswNYX2UDCgSnJq67mmmKFNElEZLI+X0RPMXaKK+j7tAio8NpjVZ5NKETQTJrOk0mbvK1/qDj0FtclvvRq2JLTzqQSCUUrQnXP+pgEPk= Received: by 10.54.42.17 with SMTP id p17mr312675wrp; Wed, 22 Jun 2005 02:10:42 -0700 (PDT) Received: by 10.54.104.3 with HTTP; Wed, 22 Jun 2005 02:10:42 -0700 (PDT) Message-ID: Date: Wed, 22 Jun 2005 11:10:42 +0200 From: Kjell Tore Fossbakk Reply-To: Kjell Tore Fossbakk To: Michael Glaesemann Subject: Re: Querying 19million records very slowly Cc: Tobias Brox , pgsql-performance@postgresql.org In-Reply-To: <8B2836FF-F335-49A4-8131-8EF2D02100F9@myrealbox.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <42B8719A.6040809@refractions.net> <20050622080357.GW7839@tobias> <20050622083921.GZ7839@tobias> <8B2836FF-F335-49A4-8131-8EF2D02100F9@myrealbox.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.168 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200506/429 X-Sequence-Number: 13066 > Try to type in '2005-06-21 16:36:22+08' directly in the query, and see if= it > makes changes. Or probably '2005-06-21 10:36:22+02' in your case ;-) Which one does Pg read fastes? Does he convert datetime in the table, then my where clause and check, for each row? How does he compare a datetime with a datetime? Timestamp are easy, large number bigger than another large number.. time (datetime) > '2005-06-21 10:36:22+02' or=20 time (timestamp) > 'some timestamp pointing to yesterday' Hmm.. I cant find any doc that describes this very good. On 6/22/05, Michael Glaesemann wrote: >=20 > On Jun 22, 2005, at 5:39 PM, Tobias Brox wrote: >=20 > > (btw, does postgresql really handles timezones? '+02' is quite =20 > > different > > from 'CET', which will be obvious sometime in the late autoumn...) >=20 > Yes, it does. It doesn't (currently) record the time zone name, but =20 > rather only the offset from UTC. If a time zone name (rather than UTC =20 > offset) is given, it is converted to the UTC offset *at that =20 > timestamptz* when it is stored. For time zones that take into account =20 > DST, their UTC offset changes during the year, and PostgreSQL records =20 > the equivalent UTC offset for the appropriate timestamptz values. >=20 > There has been discussion in the past on storing the time zone name =20 > with the timestamptz as well, though no one has implemented this yet. >=20 > Michael Glaesemann > grzm myrealbox com >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? >=20 > http://archives.postgresql.org >