What's it all about, Alfie?

Postgres is an amazing database. With 9.4's JSONB datatype, and a little clojure code, I can save data structures to and from Postgres, and have them query-able by any JDBC client. It's just amazing. But recently we had a problem at The Shop where we needed to index a field in a JSONB element, but it was a date, and so we ran into the problem of Immutable Functions.

Simply trying to create an index on the field:

  CREATE INDEX idx_d ON my_table ((fields->>'DateStarted'))::DATE;

didn't work with Postgres saying you can't have a mutable function in the creation of the index. So it was time to dig out the browser and look up what's going on. Sure enough, this was something well-known, and all involving the conversion of dates by locale, and why that was not allowed for an index.

Makes sense, but I still needed an index because the queries using this field in the WHERE clause were taking 6+ min, and we needed to really bring them down - like way down. Then I found that you could make functions that were tagged as IMMUTABLE and so made these:

  CREATE OR REPLACE FUNCTION mk_date(src VARCHAR)
  RETURNS DATE
  immutable AS $body$
    SELECT src::DATE;
  $body$ LANGUAGE SQL;
 
  CREATE OR REPLACE FUNCTION mk_datetime(src VARCHAR)
  RETURNS TIMESTAMP
  immutable AS $body$
    SELECT src::TIMESTAMP;
  $body$ LANGUAGE SQL;

the reason I chose to use ::date and ::timestamp is that they handle NULL values very well, and the corresponding functions don't do that so well.

With these functions, I was then able to create the index:

  CREATE INDEX idx_d ON my_table (mk_date((fields->>'DateStarted')));

and then as long as I used the same functions in my WHERE clause, everything was great.

The result was a 1000x increase in speed - 319,000 msec to 317 msec. Amazing.

I do love Postgres.