Open Bug 296668 Opened 19 years ago Updated 14 years ago

[PostgreSQL] Comparisons of times to interval math can cause a table scan (NOW should be LOCALTIMESTAMP)

Categories

(Bugzilla :: Database, defect, P2)

Product:
Bugzilla
Component:
Database
Version:
2.19.3
Type:
defect

Tracking

()

People

(Reporter: mkanat, Unassigned)

References

(Depends on 1 open bug)

Details

(Keywords: perf, Whiteboard: [blocker will fix]) 

At least in PostgreSQL 7.4, if you do something like this:

WHERE delta_ts > (NOW() - INTERVAL '2 HOURS')

You'll get a table scan on the bugs table, even though there's an index on delta_ts.

This is because delta_ts is a TIMESTAMP WITHOUT TIME ZONE (the PostgreSQL
default) and (NOW() - INTERVAL '2 HOURS') is automatically CAST'ed to TIMESTAMP
WITH TIME ZONE.
Keywords: perf
Priority: -- → P2
In Pg, at least, the problem is that NOW() is a TIMESTAMP WITH TIME ZONE.
Apparently the ANSI-standard way to solve this is to use LOCALTIMESTAMP instead
of NOW(), but that doesn't seem to exist in MySQL, at least in the MySQL 3
installation that I tested.
The actual solution to this is to use "LOCALTIMESTAMP" instead of "NOW()"
everywhere.

In actual fact, "LOCALTIMESTAMP" is ANSI-standard where NOW() is DB-specific
(even though almost every DB supports "NOW().")

MySQL 4 supports LOCALTIMESTAMP starting from 4.0.6.
Depends on: 204217
Blocks: meta-pg
I think we had also agreed to drop support for mysql3 after 2.22
ISTM 2.22 should still support mysql3/4 and contain experimental support for Pg
2.24 should require mysql4 (probably 4.0.something) and support mysql5 and Pg

(In reply to comment #3)
> I think we had also agreed to drop support for mysql3 after 2.22
> ISTM 2.22 should still support mysql3/4 and contain experimental support for Pg
> 2.24 should require mysql4 (probably 4.0.something) and support mysql5 and Pg

  No, 2.22 requires MySQL 4.
Summary: PostgreSQL: Comparisons of times to interval math can cause a table scan → [PostgreSQL] Comparisons of times to interval math can cause a table scan
Better still.  Let's just make sure it is > 4.0.6

2.21 requires MySQL >= 4.0.14, see bug 204217. MySQL docs indeed say >= 4.0.6 is needed for localtimestamp.
Lance: Does LOCALTIMESTAMP also work in Oracle 9?
(In reply to comment #7)
> Lance: Does LOCALTIMESTAMP also work in Oracle 9?

Yes, LOCALTIMESTAMP is supported natively in Oracle 10g.  (I'm not sure if it was in Oracle 9i, but Bugzilla requires Oracle 10g anyway since 9i didn't have native support for regular expressions.)  In fact, NOW() doesn't even normally exist in Oracle (it has SYSDATE instead), so I had written a private function in the Bugzilla schema which simply returned SYSDATE.  If we stop using NOW(), I can drop that private function.
Any traction on this bug?
Nope. :-)
Assignee: mkanat → database
Whiteboard: [Good Intro Bug]
Summary: [PostgreSQL] Comparisons of times to interval math can cause a table scan → [PostgreSQL] Comparisons of times to interval math can cause a table scan (NOW should be LOCALTIMESTAMP)
This can be viewed as a standardization thing, so maybe the PostgreSQL flag should be dropped.
LOCALTIMESTAMP does not exist as a function in MSSQL, so even if the other DBs support it, MSSQL will still need the ability to override it.
Max see bug 602926, essentially of a duplicate of this issue but suggests CURRENT_TIMESTAMP might be a better substitution.
It's not a duplicate. The other bug doesn't worry about doing a table scan or not. But one bug may fix the other one.
Depends on: 602926
Whiteboard: [Good Intro Bug] → [blocker will fix]
You need to log in before you can comment on or make changes to this bug.