[SOLVED] How to analyze query with parameters in Postgres

How to analyze query with parameters in Postgres

I wanted to analyze and compare following queries:

SELECT from mytable WHERE mytable.TimeStamp < NOW() - MAKE_INTERVAL(DAYS => 1);

and same query, but replace Now() with a variable.

DO $$
DECLARE retentionTimestamp TIMESTAMP  := NOW() - MAKE_INTERVAL(DAYS => 1);
BEGIN
   SELECT from mytable WHERE mytable.TimeStamp < retentionTimestamp;
END $$;

I can explain the first statement easily, how about the other?

* I'm using PgAdmin

Solution

Internally, PL/pgSQL variables are parameters. So you can use a prepared statement:

PREPARE stmt(timestamp) AS SELECT from mytable WHERE mytable.timestamp < $1;
EXPLAIN (ANALYZE) EXECUTE stmt(NOW() - INTERVAL '1' DAYS);

Two notes:

Your first statement has a timestamp with time zone on the right hand side, while the statement in the DO statement uses a timestamp. That may make a difference.
You should EXECUTE the prepared statement at least six times to see a generic plan, or SET plan_cache_mode = force_generic_plan;