Indexes on expressions with multiple columns and operators
От | Frédéric Yhuel |
---|---|
Тема | Indexes on expressions with multiple columns and operators |
Дата | |
Msg-id | a8b99dce-1bfb-4d97-af73-54a32b85c916@dalibo.com обсуждение исходный текст |
Ответы |
Re: Indexes on expressions with multiple columns and operators
Re: Indexes on expressions with multiple columns and operators |
Список | pgsql-performance |
Hello, in the following, I don't understand why: 1) the expression index isn't used in the first EXPLAIN 2) the number of estimated rows is completely off in the second EXPLAIN, whereas the planner could easily use the statistics of foo_f_idx. (SQL script attached, tested with master and v17) 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,100000) 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'; QUERY PLAN ------------------------------------------------------------------------------------------------------- Seq Scan on foo (cost=0.00..1797.00 rows=990 width=17) (actual time=0.012..23.932 rows=5.00 loops=1) Filter: ((ackid IS NULL) AND (crit = 'WARNING'::text)) Rows Removed by Filter: 99995 (3 rows) 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); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Index Scan using foo_f_idx on foo (cost=0.29..8.39 rows=33333 width=17) (actual time=0.021..0.028 rows=5.00 loops=1) Index Cond: (f(crit, ackid) = true) Index Searches: 1 (3 rows) SELECT tablename, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename like 'foo_%'; tablename | most_common_vals | most_common_freqs --------------+------------------+------------------- foo_expr_idx | {f,t} | {0.99995,5e-05} foo_f_idx | {f,t} | {0.99995,5e-05} SELECT 100000 * 5e-05 AS the_row_estimate_that_the_planner_should_use; the_row_estimate_that_the_planner_should_use --------------------------------------------- 5.00000 Best regards, Frédéric
Вложения
В списке pgsql-performance по дате отправления: