cobusc · July 26, 2024 09:16 · Nov 30, 2023 · Nov 19, 2013 · Jun 27, 2013 · Jun 27, 2013
diff --git a/postgresql_date_function_index_howto.md b/postgresql_date_function_index_howto.md
@@ -1,3 +1,5 @@
+> Update: 2023/11/30 Bruce Momjian's take on things: https://momjian.us/main/blogs/pgblog/2023.html#November_22_2023
+
 Given a table...
 
 ```sql

diff --git a/postgresql_date_function_index_howto.md b/postgresql_date_function_index_howto.md
@@ -32,7 +32,7 @@ postgres=> CREATE INDEX ON test (date(created_at));
 CREATE INDEX
 ```
 
-So how do we handle the case where we have a time zone? Well, since the `DATE()` funtion may give different results based on the time zone, we have to decide which time zone is applicable. In this example we use UTC:
+So how do we handle the case where we have a time zone? Well, since the `DATE()` function may give different results based on the time zone, we have to decide which time zone is applicable. In this example we use UTC:
 
 ```sql
 postgres=> CREATE INDEX ON foo (DATE(created_at AT TIME ZONE 'UTC'));

diff --git a/postgresql_date_function_index_howto.md b/postgresql_date_function_index_howto.md
@@ -78,6 +78,8 @@ postgres=> EXPLAIN SELECT * FROM foo WHERE DATE(TIMEZONE('UTC'::text, created_at
 
 This seems to have done the trick. It is strange though that PostgreSQL rewrites the function used to create the index to a canonical form, but does not seem to do the same when the function is used in the `WHERE` clause (in order to match the index function).
 
+Footnote: PostgreSQL 9.1.9 was used.
+
 
 
 

diff --git a/postgresql_date_function_index_howto.md b/postgresql_date_function_index_howto.md
@@ -60,7 +60,11 @@ postgres=> \d foo_date_idx
 btree, for table "public.foo"
 ```
 
-Let's try to use the definition as stored by the database. Note that this is (and should be) functionally equivalent to `DATE(created_at AT TIME ZONE 'UTC')`:
+Let's try to use the definition as stored by the database. Note that this is (and should be) functionally equivalent to `DATE(created_at AT TIME ZONE 'UTC')`, since the [documentation](http://www.postgresql.org/docs/9.1/static/functions-datetime.html) says:
+
+```
+The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone.
+```
 
 ```
 postgres=> EXPLAIN SELECT * FROM foo WHERE DATE(TIMEZONE('UTC'::text, created_at)) = DATE('2013-01-01');

diff --git a/postgresql_date_function_index_howto.md b/postgresql_date_function_index_howto.md
@@ -12,7 +12,7 @@ CREATE TABLE foo (
 
 ```sql
 WHERE ...
-  AND date(created_at) = date('2013-01-01') ...
+  AND DATE(created_at) = DATE('2013-01-01') ...
 ```
 
 The table is big and other indexes are not helping, so you decide to create an index on `DATE(created_at)`:

diff --git a/postgresql_date_function_index_howto.md b/postgresql_date_function_index_howto.md
@@ -72,7 +72,7 @@ postgres=> EXPLAIN SELECT * FROM foo WHERE DATE(TIMEZONE('UTC'::text, created_at
          Index Cond: (date(timezone('UTC'::text, created_at)) = '2013-01-01'::date)
 ```
 
-This seems to have done the trick. It is strange though that PostgreSQL rewrites the function used to create the index to a canonical form, but does not seem to do the same when the function is used in the `WHERE` clause.
+This seems to have done the trick. It is strange though that PostgreSQL rewrites the function used to create the index to a canonical form, but does not seem to do the same when the function is used in the `WHERE` clause (in order to match the index function).
 
 
 

diff --git a/postgresql_date_function_index_howto.md b/postgresql_date_function_index_howto.md
@@ -0,0 +1,82 @@
+Given a table...
+
+```sql
+CREATE TABLE foo (
+  id SERIAL PRIMARY KEY,
+  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
+  ...
+);
+```
+
+...you realize that you have to perform complex queries with the following type of condition:
+
+```sql
+WHERE ...
+  AND date(created_at) = date('2013-01-01') ...
+```
+
+The table is big and other indexes are not helping, so you decide to create an index on `DATE(created_at)`:
+
+```sql
+postgres=> CREATE INDEX ON foo (DATE(created_at));
+ERROR:  functions in index expression must be marked IMMUTABLE
+```
+
+It turns out that the function `DATE(TIMESTAMP WITH TIME ZONE)` is _mutable_, due to the time zone.
+On the other hand `DATE(TIMESTAMP WITHOUT TIME ZONE)` is _immutable_, as shown in the following example:
+
+```sql
+postgres=> CREATE TABLE test (created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW());
+CREATE TABLE
+postgres=> CREATE INDEX ON test (date(created_at));
+CREATE INDEX
+```
+
+So how do we handle the case where we have a time zone? Well, since the `DATE()` funtion may give different results based on the time zone, we have to decide which time zone is applicable. In this example we use UTC:
+
+```sql
+postgres=> CREATE INDEX ON foo (DATE(created_at AT TIME ZONE 'UTC'));
+CREATE INDEX
+```
+
+That worked. Now how do get the query planner to use it? I was under the impression that I simply had to use the same function I used to create the index in the `WHERE` condition. Let's see what the query planner says:
+
+```sql
+postgres=> EXPLAIN SELECT * FROM foo WHERE DATE(created_at AT TIME ZONE 'UTC') = DATE('2013-01-01');
+                                    QUERY PLAN                                    
+----------------------------------------------------------------------------------
+ Seq Scan on foo  (cost...)
+   Filter: (date(timezone('UTC'::text, created_at)) = '2013-01-01'::date)
+```
+
+The index is not used. Let's go look at the index definition:
+
+```sql
+postgres=> \d foo_date_idx 
+       Index "public.foo_date_idx"
+ Column | Type |               Definition                
+--------+------+-----------------------------------------
+ date   | date | date(timezone('UTC'::text, created_at))
+btree, for table "public.foo"
+```
+
+Let's try to use the definition as stored by the database. Note that this is (and should be) functionally equivalent to `DATE(created_at AT TIME ZONE 'UTC')`:
+
+```
+postgres=> EXPLAIN SELECT * FROM foo WHERE DATE(TIMEZONE('UTC'::text, created_at)) = DATE('2013-01-01');
+                                         QUERY PLAN                                         
+--------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on foo (cost...)
+   Recheck Cond: (date(timezone('UTC'::text, created_at)) = '2013-01-01'::date)
+   ->  Bitmap Index Scan on foo_date_idx  (cost...)
+         Index Cond: (date(timezone('UTC'::text, created_at)) = '2013-01-01'::date)
+```
+
+This seems to have done the trick. It is strange though that PostgreSQL rewrites the function used to create the index to a canonical form, but does not seem to do the same when the function is used in the `WHERE` clause.
+
+
+
+
+
+
+
Original file line number	Diff line number	Diff line change
		@@ -78,6 +78,8 @@ postgres=> EXPLAIN SELECT * FROM foo WHERE DATE(TIMEZONE('UTC'::text, created_at

		This seems to have done the trick. It is strange though that PostgreSQL rewrites the function used to create the index to a canonical form, but does not seem to do the same when the function is used in the `WHERE` clause (in order to match the index function).

		Footnote: PostgreSQL 9.1.9 was used.