Zenn
🐘

PostgreSQLの関数INDEX

2024/03/31に公開
PostgreSQL
tech

PostgreSQLでは、テーブルの列だけではなく、列を利用した式でINDEXを作成することができます。

ここでは、式の中でも、関数を使ったINDEXについて書いていきます。

関数INDEX

下記のような関数(lower)を使った検索の場合、該当カラム(col1)にINDEXが設定されていたとしても、INDEXを使った検索にはなりません。

SELECT * FROM test1 WHERE lower(col1) = 'value';

下記のように関数を使ったINDEXを作ることで、上記検索でもINDEXが使えるようになります。

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

また、関数をかけたうえでの一意制約を用意したいような場合(今回の例だと、大文字小文字同一視で一意にしたい)には、UNIQUE INDEXとすることで一意制約を加えることができます。

CREATE UNIQUE INDEX test1_lower_col1_unique_idx ON test1 (lower(col1));

関数INDEXとして利用できる関数

関数INDEXとして利用できる関数は、同じ引数での呼び出しで常に同じ結果を返す必要があります。常に同じ結果を返さないと、INDEX作成時と利用時で同じ結果にならない可能性があるので当然ですね。

関数のVolatilityがimmutableとなっているものが、常に同じ結果を返す関数になります。

先ほど使ったlower関数はimmutableです。

postgres-# \df+ lower
                                                                                       List of functions
   Schema   | Name  | Result data type | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |   Source code    |        Description
------------+-------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+------------------+---------------------------
 pg_catalog | lower | anyelement       | anymultirange       | func | immutable  | safe     | postgres | invoker  |                   | internal | multirange_lower | lower bound of multirange
 pg_catalog | lower | anyelement       | anyrange            | func | immutable  | safe     | postgres | invoker  |                   | internal | range_lower      | lower bound of range
 pg_catalog | lower | text             | text                | func | immutable  | safe     | postgres | invoker  |                   | internal | lower            | lowercase
(3 rows)

date関数は、timestamp with time zoneを引数で受け取る方は、stableとなっています。

postgres-# \df+ date
                                                                                                  List of functions
   Schema   | Name | Result data type |     Argument data types     | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |   Source code    |               Description
------------+------+------------------+-----------------------------+------+------------+----------+----------+----------+-------------------+----------+------------------+------------------------------------------
 pg_catalog | date | date             | timestamp without time zone | func | immutable  | safe     | postgres | invoker  |                   | internal | timestamp_date   | convert timestamp to date
 pg_catalog | date | date             | timestamp with time zone    | func | stable     | safe     | postgres | invoker  |                   | internal | timestamptz_date | convert timestamp with time zone to date
(2 rows)

stableとなるのは、セッションのタイムゾーンによって返す値が変わるためです。

postgres=# SET timezone TO 'Etc/UTC';
SET
postgres=# SELECT
  '2024-01-01T00:00:00+9'::timestamp with time zone,
  date('2024-01-01T00:00:00+9'::timestamp with time zone)
;
      timestamptz       |    date
------------------------+------------
 2023-12-31 15:00:00+00 | 2023-12-31
(1 row)

postgres=# SET timezone TO 'Asia/Tokyo';
SET
postgres=# SELECT
  '2024-01-01T00:00:00+9'::timestamp with time zone,
  date('2024-01-01T00:00:00+9'::timestamp with time zone)
;
      timestamptz       |    date
------------------------+------------
 2024-01-01 00:00:00+09 | 2024-01-01
(1 row)

たとえば下記のような timestamp without time zone と timestamp with time zone のカラムを持つテーブルがあった場合

CREATE TABLE test1 (
  id serial PRIMARY KEY,
  datetime1 timestamp without time zone,
  datetime2 timestamp with time zone
);

date関数でdate型のINDEXを作成しようとすると、timestamp without time zone のカラムに対しては作成できますが、timestamp with time zone だと作成できません。

postgres=# CREATE INDEX test1_datetime1_idx ON test1 (date(datetime1));
CREATE INDEX
postgres=# CREATE INDEX test1_datetime2_idx ON test1 (date(datetime2));
ERROR:  functions in index expression must be marked IMMUTABLE

タイムゾーンが決まっているならば、そのタイムゾーンでのtimestamp without time zoneに変えてあげることで回避できます。

postgres=# CREATE INDEX test1_datetime2_idx ON test1 (date(datetime2 AT TIME ZONE 'Asia/Tokyo'));
CREATE INDEX

Discussion