Intenté implementar la indexación de expresiones en columnas virtuales como sugiere Bruce Momjian en el blog.
Funciona como se esperaba (pero requiere datos adicionales en la tabla para que el índice sea útil). ¡Entonces intenté usarlo a la vista y lo rompí!
Paso a paso:
CREATE TABLE customer (id SERIAL, firstname TEXT, lastname TEXT); -- create virtual column CREATE FUNCTION fullname(customer) RETURNS text AS $$ SELECT $1.firstname || ' ' || $1.lastname $$ LANGUAGE SQL; INSERT INTO customer VALUES (DEFAULT, 'Mark', 'Pennypincher'); -- Insert some data for index became useful INSERT INTO customer(firstname, lastname) SELECT 'first_' || n, 'last_' || n FROM generate_series(1, 1000) as n; CREATE INDEX i_customer_firstname ON customer (firstname); CREATE INDEX i_customer_bothnames ON customer ((firstname || ' ' || lastname)); CREATE INDEX i_customer_fullname ON customer (fullname(customer)); ANALYZE customer; EXPLAIN SELECT * FROM customer WHERE customer.fullname = 'Mark Pennypincher';Por ahora funciona como se esperaba, consulte el índice de uso:
Index Scan using i_customer_bothnames on customer (cost=0.28..8.29 rows=1 width=21) Index Cond: (((firstname || ' '::text) || lastname) = 'Mark Pennypincher'::text)Ahora crea la vista:
CREATE OR REPLACE VIEW v_customer AS SELECT c.* ,c.fullname FROM customer c;No tenemos agregaciones ni límites, por lo que,de acuerdo con la documentación, gracias al mecanismo de reescritura y al optimizador, debe aparecer en la consulta. ¡Pero no lo es!
Mirar:
EXPLAIN SELECT * FROM v_customer WHERE v_customer.fullname = 'Mark Pennypincher';lleva inmediatamente al siguiente plan de consulta:
Subquery Scan on v_customer (cost=0.00..34.53 rows=5 width=53) Filter: (v_customer.fullname = 'Mark Pennypincher'::text) -> Seq Scan on customer c (cost=0.00..22.01 rows=1001 width=53) ¿Por qué Subquery Scan ?
Lo que también son interesantes las siguientes dos declaraciones:
EXPLAIN SELECT * FROM v_customer WHERE v_customer.firstname = 'Mark'; EXPLAIN SELECT * FROM v_customer WHERE v_customer.firstname || ' ' || v_customer.lastname = 'Mark Pennypincher';ambos usan sus índices también.
Por supuesto, tengo una vista más complicada en la realidad con varias uniones. Hay solo un ejemplo de problema. Y es la razón por la que quiero reemplazarlo por vista por simplicidad .
Debe hacer que la función sea inmutable; de lo contrario, el planificador no la usará:
CREATE FUNCTION fullname(customer) RETURNS text AS $$ SELECT $1.firstname || ' ' || $1.lastname $$ LANGUAGE SQL immutable; --<< hereEsto luego genera el siguiente plan con sus datos de muestra:
Index Scan using i_customer_fullname on stuff.customer (cost=0.28..8.29 rows=1 width=21) (actual time=0.054..0.054 rows=1 loops=1) Output: id, firstname, lastname Index Cond: (((customer.firstname || ' '::text) || customer.lastname) = 'Mark Pennypincher'::text) Buffers: shared hit=1 read=2 I/O Timings: read=0.035 Planning time: 0.571 ms Execution time: 0.079 ms Si la función no es inmutable, Postgres no puede confiar en que los valores almacenados en el índice sean siempre los mismos. stable habría logrado el mismo objetivo. Pero como la función solo se basa en los valores que se le pasan, immutable es la opción correcta.