3

I have large table with several million rows in Postgresql 9.1. One of the columns is timestamp with time zone.

Frequently used query is looking for data using where clause 'column > (now()::date - 11)' to look for last ten days.

I want to build an index that would work only for last months data, to limit the scan. Partial index.

So far I have not figured out how to use actual last month, so I started by hardcoding '2015-12-01' as a start date for index. 

create index q on test (i) where i > '2015-01-01';

this worked fine, index was created. But unfortunately, it was not used, as it treats '2015-01-01' as a ::timestamp, while query is with a ::date. So index was not used and I was back to square one.

Next I tried to modify index to compare column with date, so it would match. But here I hit the immutable wall.

As to_date or cast as date are mutable functions, they are dependent on local timezone, index creation fails.

if I have test table like this:

create table test (i timestamptz);

and then try to create index with 

create index q on test (i) where i > to_date('2015-01-01','YYYY-DD-MM');

then it fails with 

ERROR:  functions in index predicate must be marked IMMUTABLE

this is understandable. But now, when I try it with specific timezone

create index q on test (i) where i > to_date('2015-01-01','YYYY-DD-MM') 
at time zone 'UTC';

it still fails

ERROR:  functions in index predicate must be marked IMMUTABLE

this I don't understand anymore. It has timezone defined. What else is immutable?

I also tried creating immutable function myself:

CREATE FUNCTION
datacube=# create or replace function immutable_date(timestamptz) returns date as $$
select ($1::date at time zone 'UTC')::date;
$$ language sql immutable;

but using this function in index:

create index q on test (i) where i > immutable_date('2015-01-01');

fails with the same error:

ERROR:  functions in index predicate must be marked IMMUTABLE

I am at loss here. Maybe it has something to do with Locales, not only timezones? Or something else makes it mutable?

And also - maybe there are another, simpler way, to limit index to last month or two of data? Table partitioning in Postgres would require rebuilding entire database, and so far I have not found anything else.

Martins Untals
  • 2,128
  • 2
  • 18
  • 31
  • Same issue here. I need the function date() on the index as another application is creating the query. Therefore the other solution of creating a custom function for the index won't work for me. Would love to see a solution! – Vytas Bradunas Apr 03 '17 at 12:08
  • unfortunately I don't remember how did I get myself out of this hole. Probably I ditched the idea completely at the end. I remember the suffering of trying to solve it, with no end in sight. – Martins Untals Apr 03 '17 at 17:38

0 Answers0