From aa@devshock.com Fri Oct  12 20:06:01 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1TMlUe-0001TN-Ur
	for pgsql-performance@postgresql.org; Fri, 12 Oct 2012 20:06:01 +0000
Received: from mail-gg0-f174.google.com ([209.85.161.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1TMlUa-0002GD-MH
	for pgsql-performance@postgresql.org; Fri, 12 Oct 2012 20:06:00 +0000
Received: by mail-gg0-f174.google.com with SMTP id k5so875425ggd.19
	for <pgsql-performance@postgresql.org>;
	Fri, 12 Oct 2012 13:05:55 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=from:to:subject:date:message-id:mime-version:content-type:x-mailer
	:thread-index:content-language:x-gm-message-state;
	bh=e7qr0Pq1svd508Y3PMxUI2qJhAd5UKSfmnSFZ7WFakg=;
	b=X4o6tJAfnPxH+CRc94s/GO86Ytq1SpW1wx1TzB9NwDWkKxnDfDZgJS95OCDIEsv7kb
	H8EW+2B0iPsVqblPquYtZSiJfFiC9P9O+UcaTNZSCeJdct/HaIlWNKxA7meBM9Yy3CDE
	eCHpTySrCXwCZ1lL98VYSzZB4IZRFAxWDwIEPJf40w4ZAGliA3p8gDm4iyR1zS/exsKc
	totXMRzkrHT0QNSkEw2jI4SUmhMHLQxP/t5/8ZUe0v0hqfysj2ELIm8TktObZxYwVY1p
	EpY+H8jGrSpRLlvn/AG6/9CcIIh3AsRG8OpnqucvncmXN7NdV4anNavWrp4XjRm7feSw
	htcw==
Received: by 10.236.193.71 with SMTP id j47mr5190432yhn.79.1350072355448;
	Fri, 12 Oct 2012 13:05:55 -0700 (PDT)
Received: from AnibalVAIO ([190.128.222.90])
	by mx.google.com with ESMTPS id u13sm7356286anl.5.2012.10.12.13.05.52
	(version=TLSv1/SSLv3 cipher=OTHER);
	Fri, 12 Oct 2012 13:05:54 -0700 (PDT)
From: "Anibal David Acosta" <aa@devshock.com>
To: <pgsql-performance@postgresql.org>
Subject: Do cast affects index usage?
Date: Fri, 12 Oct 2012 17:05:52 -0300
Message-ID: <004301cda8b4$fdc57bd0$f9507370$@devshock.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0044_01CDA89B.D878B900"
X-Mailer: Microsoft Outlook 14.0
Thread-Index: Ac2osay1BwMecW6/SEO8RW9fhAm0qA==
Content-Language: es-py
X-Gm-Message-State: 
 ALoCoQnf4vyg/vztFeBpSO1PYO+Mg9GumJZ+0pQI9zRUWEZu14IZIQQ2SnB7jHcacfkeGzfzL6oE
X-Pg-Spam-Score: -1.2 (-)
X-Archive-Number: 201210/163
X-Sequence-Number: 48122

This is a multipart message in MIME format.

------=_NextPart_000_0044_01CDA89B.D878B900
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

I have a table with a column of type timestamp with time zone, this column
has an index

 

If I do a select like this

 

select * from mytable where cast(my_date as timestamp without time zone) >
'2012-10-12 20:00:00'

 

this query will use the index over the my_date column?

 

Thanks

 


------=_NextPart_000_0044_01CDA89B.D878B900
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><META =
HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii"><meta name=3DGenerator content=3D"Microsoft Word 14 =
(filtered medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EstiloCorreo17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-family:"Calibri","sans-serif";}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]--></head><body lang=3DEN-US link=3Dblue =
vlink=3Dpurple><div class=3DWordSection1><p class=3DMsoNormal>I have a =
table with a column of type timestamp with time zone, this column has an =
index<o:p></o:p></p><p class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>If I do a select like this<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>select * =
from mytable where cast(my_date as timestamp without time zone) &gt; =
'2012-10-12 20:00:00'<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p class=3DMsoNormal>this query =
will use the index over the my_date column?<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p><p =
class=3DMsoNormal>Thanks<o:p></o:p></p><p =
class=3DMsoNormal><o:p>&nbsp;</o:p></p></div></body></html>
------=_NextPart_000_0044_01CDA89B.D878B900--





From scott.marlowe@gmail.com Fri Oct  12 20:37:42 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <scott.marlowe@gmail.com>) id 1TMlzJ-0004kx-Ph
	for pgsql-performance@postgresql.org; Fri, 12 Oct 2012 20:37:42 +0000
Received: from mail-qc0-f174.google.com ([209.85.216.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <scott.marlowe@gmail.com>) id 1TMlzG-0002i8-4R
	for pgsql-performance@postgresql.org; Fri, 12 Oct 2012 20:37:40 +0000
Received: by mail-qc0-f174.google.com with SMTP id d3so2559731qch.19
	for <pgsql-performance@postgresql.org>;
	Fri, 12 Oct 2012 13:37:37 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=yYYz3XyRjWp+XOxlO2fAnKPT79JiMAzhg7eA5dbP1Cw=;
	b=WGB9r69qVKjVD2nBkEFriaRPS7KfP86EAH+YUYM1SgVA3V+SNCc1cJ6Szk7jc+tEH7
	mhU4MhUOzf/RagpsUDZHW6nxoQ8Sw2gS4j/oSWw1y8wHn1B1A4aGdeYxeO3L9EfmQUre
	S77Y1mygjnvcJfYOrUdSXKqcQyv9LFW12Spxsx1YBuRQLgycCy1SIdOZhWXaao8HCaOv
	hdsi98Ln63+CerX7zPfD3zB49oGtyvCwzWSNpbyOym0IdLuiUmX2vwHsm2veFvTRaknn
	+VrN2aU329FVq/gjVxMUl8JOvCH3CA97KA2J2xRVwiwriKJSbDQ28/ppkaprQhA0aNWG
	FlAw==
MIME-Version: 1.0
Received: by 10.224.187.198 with SMTP id cx6mr1297248qab.56.1350074257152;
	Fri, 12 Oct 2012 13:37:37 -0700 (PDT)
Received: by 10.49.64.98 with HTTP; Fri, 12 Oct 2012 13:37:37 -0700 (PDT)
In-Reply-To: <004301cda8b4$fdc57bd0$f9507370$@devshock.com>
References: <004301cda8b4$fdc57bd0$f9507370$@devshock.com>
Date: Fri, 12 Oct 2012 14:37:37 -0600
Message-ID: 
 <CAOR=d=3iBsgKQ_U59Y7Wkcq+7OGgmXXHc76-_HmXkgRxVtdaWQ@mail.gmail.com>
Subject: Re: Do cast affects index usage?
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Anibal David Acosta <aa@devshock.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201210/164
X-Sequence-Number: 48123

On Fri, Oct 12, 2012 at 2:05 PM, Anibal David Acosta <aa@devshock.com> wrote:
> I have a table with a column of type timestamp with time zone, this column
> has an index
>
> If I do a select like this
>
> select * from mytable where cast(my_date as timestamp without time zone) >
> '2012-10-12 20:00:00'
>
> this query will use the index over the my_date column?

No.  but it will use a functional index:

create index yada on blah (cast(my_date as timestaqmp without time zone));




From tgl@sss.pgh.pa.us Fri Oct  12 20:38:36 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TMm0C-0005Sq-ER
	for pgsql-performance@postgresql.org; Fri, 12 Oct 2012 20:38:36 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TMm09-0002iH-Qg
	for pgsql-performance@postgresql.org; Fri, 12 Oct 2012 20:38:35 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q9CKcW1O021824;
	Fri, 12 Oct 2012 16:38:32 -0400 (EDT)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Anibal David Acosta" <aa@devshock.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Do cast affects index usage?
In-reply-to: <004301cda8b4$fdc57bd0$f9507370$@devshock.com>
References: <004301cda8b4$fdc57bd0$f9507370$@devshock.com>
Comments: In-reply-to "Anibal David Acosta" <aa@devshock.com>
	message dated "Fri, 12 Oct 2012 17:05:52 -0300"
Date: Fri, 12 Oct 2012 16:38:31 -0400
Message-ID: <21823.1350074311@sss.pgh.pa.us>
X-Pg-Spam-Score: -4.0 (----)
X-Archive-Number: 201210/165
X-Sequence-Number: 48124

"Anibal David Acosta" <aa@devshock.com> writes:
> I have a table with a column of type timestamp with time zone, this column
> has an index

> If I do a select like this
> select * from mytable where cast(my_date as timestamp without time zone) >
> '2012-10-12 20:00:00'
> this query will use the index over the my_date column?

No.  The cast seems rather pointless though ...

			regards, tom lane




From aa@devshock.com Sat Oct  13 00:27:50 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1TMpa2-0005ON-AT
	for pgsql-performance@postgresql.org; Sat, 13 Oct 2012 00:27:50 +0000
Received: from mail-gh0-f174.google.com ([209.85.160.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <aa@devshock.com>) id 1TMpZx-0005uk-2C
	for pgsql-performance@postgresql.org; Sat, 13 Oct 2012 00:27:48 +0000
Received: by mail-gh0-f174.google.com with SMTP id g15so942442ghb.19
	for <pgsql-performance@postgresql.org>;
	Fri, 12 Oct 2012 17:27:44 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=from:to:cc:references:in-reply-to:subject:date:message-id
	:mime-version:content-type:content-transfer-encoding:x-mailer
	:thread-index:content-language:x-gm-message-state;
	bh=Z63chmj+20P0UVSzBJ6xz5UtylixJxDudtiX2ilQYiE=;
	b=SwSRD5azST/K070LJu14IeAvMC+HpltFxouQi7WgybtObx8pKQGJ8m2Rf3vs9N/j9G
	UPZlOxCX8kiR8x4XYgPnLOIyd3i3pu5v41x9qOTYBpuz0K6MTeOzA0y0HOXp2Fuw4t87
	jHUkQq+dE5+DSg8dVE9R9j1BRjJDuCxVPHraMojwsBhP9Xeie+7g26JUoSY+5y5+z+Aj
	FUfELbEgpV/gxxn2oQoy3u9PpHHnr1/6gkU0ZdqS7ShVPHGExMnZK4lJnSnKQ3Ih4gOd
	nex0yZzq0MtYgC7gmm5bRRD+bZIc2fdQf7sMZEZeXQq6iq2PDUqPk7lqsyzfVLtE1HCN
	a4RA==
Received: by 10.101.175.34 with SMTP id c34mr1816866anp.0.1350088064000;
	Fri, 12 Oct 2012 17:27:44 -0700 (PDT)
Received: from AnibalVAIO (host-1-86.b20.cvc.com.py. [186.2.211.86])
	by mx.google.com with ESMTPS id e65sm6651586yhc.18.2012.10.12.17.27.42
	(version=TLSv1/SSLv3 cipher=OTHER);
	Fri, 12 Oct 2012 17:27:43 -0700 (PDT)
From: "Anibal David Acosta" <aa@devshock.com>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-performance@postgresql.org>
References: <004301cda8b4$fdc57bd0$f9507370$@devshock.com>
	<21823.1350074311@sss.pgh.pa.us>
In-Reply-To: <21823.1350074311@sss.pgh.pa.us>
Subject: Re: Do cast affects index usage?
Date: Fri, 12 Oct 2012 21:27:42 -0300
Message-ID: <000001cda8d9$913a7770$b3af6650$@devshock.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Outlook 14.0
Thread-Index: AQGT57XVKrcJB9MlNPDc4DRfzfgK1QD/vCnUmCHi5LA=
Content-Language: es-py
X-Gm-Message-State: 
 ALoCoQlgHExKh5fw2MWpMvBFbSAMR2IJZMz3IhUX0SapvWnGbu6q064kIh07TW0Qjt0xOQ747yyu
X-Pg-Spam-Score: -0.9 (/)
X-Archive-Number: 201210/170
X-Sequence-Number: 48129

Because I need to get all rows where datetime is greater than (for example)
'2012-10-10 00:00:00' but ignoring timezone, so basically I need to truncate
timezone
This can be done by converting to timestamp without timezone.



-----Mensaje original-----
De: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Enviado el: viernes, 12 de octubre de 2012 05:39 p.m.
Para: Anibal David Acosta
CC: pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] Do cast affects index usage?

"Anibal David Acosta" <aa@devshock.com> writes:
> I have a table with a column of type timestamp with time zone, this 
> column has an index

> If I do a select like this
> select * from mytable where cast(my_date as timestamp without time 
> zone) >
> '2012-10-12 20:00:00'
> this query will use the index over the my_date column?

No.  The cast seems rather pointless though ...

			regards, tom lane





From tgl@sss.pgh.pa.us Sat Oct  13 01:34:15 2012
Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TMqcJ-0002Do-6C
	for pgsql-performance@postgresql.org; Sat, 13 Oct 2012 01:34:15 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1TMqcE-0006oD-9C
	for pgsql-performance@postgresql.org; Sat, 13 Oct 2012 01:34:14 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q9D1Y9Um027830;
	Fri, 12 Oct 2012 21:34:09 -0400 (EDT)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Anibal David Acosta" <aa@devshock.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Do cast affects index usage?
In-reply-to: <000001cda8d9$913a7770$b3af6650$@devshock.com>
References: <004301cda8b4$fdc57bd0$f9507370$@devshock.com>
	<21823.1350074311@sss.pgh.pa.us>
	<000001cda8d9$913a7770$b3af6650$@devshock.com>
Comments: In-reply-to "Anibal David Acosta" <aa@devshock.com>
	message dated "Fri, 12 Oct 2012 21:27:42 -0300"
Date: Fri, 12 Oct 2012 21:34:08 -0400
Message-ID: <27829.1350092048@sss.pgh.pa.us>
X-Pg-Spam-Score: -2.3 (--)
X-Archive-Number: 201210/172
X-Sequence-Number: 48131

"Anibal David Acosta" <aa@devshock.com> writes:
> Because I need to get all rows where datetime is greater than (for example)
> '2012-10-10 00:00:00' but ignoring timezone, so basically I need to truncate
> timezone
> This can be done by converting to timestamp without timezone.

[ shrug... ]  It can also be done without that.  Whatever your cutoff
time is can be expressed as a timestamp *with* tz.

			regards, tom lane