expr_index.sql

application/sql

Download

\set ECHO queries
\set total_rows 100000

DROP TABLE IF EXISTS foo;

CREATE UNLOGGED TABLE foo (id bigint, ackid int, crit text);

ALTER TABLE foo ALTER COLUMN crit SET statistics 400;

INSERT INTO foo SELECT i, NULL, CASE WHEN i%100=1 THEN 'WARNING' ELSE 'INFO' END FROM generate_series(1, :total_rows) AS T(i);

UPDATE foo SET ackid = random()*10000 WHERE id%100=1 AND id > 500 ;

CREATE INDEX foo_expr_idx ON foo ((ackid IS NULL AND crit = 'WARNING'));
ANALYZE foo ;

EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE ackid IS NULL AND crit = 'WARNING';

CREATE OR REPLACE
FUNCTION f(crit text, ackid int)
RETURNS bool
LANGUAGE plpgsql
IMMUTABLE AS $$
  BEGIN RETURN crit = 'WARNING' AND ackid IS NULL; END
$$;

CREATE INDEX foo_f_idx ON foo (f(crit, ackid));
ANALYZE foo ;

EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE f(crit, ackid);

SELECT tablename, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename like 'foo_%';

SELECT most_common_freqs[2] AS freq FROM pg_stats WHERE tablename = 'foo_f_idx' \gset

SELECT :total_rows * :freq AS the_row_estimate_that_the_planner_should_use;