Geek asks and answers

Add datetime restriction for partial PostgreSQL index with multiple columns

I have a PostgreSQL table with a name queries_querythat has many columns.

Two of these columns createdand user_sidare often used together in SQL queries my application to determine how many queries posed by the last 30 days. It is very, very rare that I request these statistics anytime older than the last 30 days.

Here is my question:

I have currently created my multi-column index in these two columns by doing:

CREATE INDEX CONCURRENTLY some_index_name ON queries_query (user_sid, created)

But I would like to further limit the index to only taking care of those queries that create a date in the last 30 days. I tried to do the following:

CREATE INDEX CONCURRENTLY some_index_name ON queries_query (user_sid, created)
WHERE created >= NOW() - '30 days'::INTERVAL`

But this throws an exception indicating that my function should be immutable.

, , , Postgres .

+5
1

now(), IMMUTABLE (), :

, , ""...

( ) :

1. , :

CREATE INDEX queries_recent_idx ON queries_query (user_sid, created)
WHERE created > '2013-01-07 00:00'::timestamp;

, created timestamp. timestamp timestamptz (timestamp with time zone). timestamp timestamptz ( ) . . / :

, , cron ( - ). , , . .

, :

CREATE OR REPLACE FUNCTION f_index_recreate()
  RETURNS void AS
$func$
BEGIN
   DROP INDEX IF EXISTS queries_recent_idx;
   EXECUTE format('
      CREATE INDEX queries_recent_idx
      ON queries_query (user_sid, created)
      WHERE created > %L::timestamp'
    , LOCALTIMESTAMP - interval '30 days');  -- timestamp constant
--  , now() - interval '30 days');           -- alternative for timestamptz
END
$func$  LANGUAGE plpgsql;

:

SELECT f_index_recreate();

now() ( ) CURRENT_TIMESTAMP timestamptz. timestamp now()::timestamp LOCALTIMESTAMP.

Postgres 9.2 - 9.4.
SQL Fiddle.

, CREATE INDEX CONCURRENTLY. , :

... CREATE INDEX, CREATE INDEX CONCURRENTLY .

, :

CREATE INDEX CONCURRENTLY queries_recent_idx2 ON queries_query (user_sid, created)
WHERE  created > '2013-01-07 00:00'::timestamp;  -- your new condition

:

DROP INDEX CONCURRENTLY IF EXISTS queries_recent_idx;

:

ALTER INDEX queries_recent_idx2 RENAME TO queries_recent_idx;

2. ""

archived :

ALTER queries_query ADD COLUMN archived boolean NOT NULL DEFAULT FALSE;

UPDATE , " " , :

CREATE INDEX some_index_name ON queries_query (user_sid, created)
WHERE NOT archived;

( ), . EXPLAIN ANALYZE, - . , .

, UPDATE , , .

first ( ). , . .

, , .

+7

More articles:


All Articles