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 по дате отправления: