From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@postgreSQL.org
Cc: dsuchka@gmail.com
Subject: Function transform optimizations versus reality
Date: Wed, 18 Jan 2017 12:10:39 -0500
Message-ID: <18771.1484759439@sss.pgh.pa.us> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
I looked into bug #14504,
https://www.postgresql.org/message-id/20170118144828.1432.52823@wrigleys.postgresql.org
The problem is that timestamp_zone_transform() has the cute idea that
it can simplify timezone('UTC', timestamptzvalue) into a RelabelType
that claims the timestamptzvalue is just type timestamp. Well, that's
just too cute, because it confuses the index machinery completely.
What _bt_first() sees is an equality comparison between an index
column that it knows is timestamptz, and a RHS constant that it knows
is timestamp, so it selects timestamptz_cmp_timestamp() as the
comparison function to use. And that function will apply a timezone
rotation, resulting in the wrong answers.
You could blame this on the fact that the planner will ignore the
RelabelType when trying to match the qual to indexes. But that hack has
fifteen years' seniority on this one, and removing it would break (at
least) use of indexes on varchar columns, so I'm not planning on fixing
this that way. I think the only realistic fix is to disable this
optimization in timestamp_zone_transform; certainly that's the only way
I'd be comfortable with back-patching.
(Another reason for being unhappy about this optimization is that EXPLAIN
will print the resulting expression tree as timestamptzvalue::timestamp,
which is a completely misleading description; an actual cast like that
would not behave this way.)
More generally, this is the second serious bug we've found in the last
month in the "transform" optimizations (see also bug #14479 and commit
f0774abde). I'm starting to get the feeling that that idea was an
attractive nuisance --- at the very least, it would behoove us to go
through all the transform functions with a gimlet eye.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, dsuchka@gmail.com
Subject: Re: Function transform optimizations versus reality
In-Reply-To: <18771.1484759439@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox