Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE source_table (id int);
CREATE MATERIALIZED VIEW some_view AS SELECT * FROM source_table;

CREATE OR REPLACE FUNCTION refresh_some_view()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    REFRESH MATERIALIZED VIEW some_view;
    RETURN NEW;
END;
$$;

CREATE TABLE

SELECT 0

CREATE FUNCTION

CREATE TRIGGER on_source_table_update AFTER UPDATE OR INSERT OR DELETE ON source_table 
FOR EACH STATEMENT EXECUTE PROCEDURE refresh_some_view();

EXPLAIN (ANALYSE, COSTS OFF)
INSERT INTO source_table VALUES (generate_series(1,1000)); 

CREATE TRIGGER

QUERY PLAN

Insert on source_table (actual time=1.131..1.131 rows=0 loops=1)

  ->  ProjectSet (actual time=0.004..0.124 rows=1000 loops=1)

        ->  Result (actual time=0.002..0.002 rows=1 loops=1)

Planning Time: 0.043 ms

Trigger on_source_table_update: time=2.546 calls=1

Execution Time: 3.715 ms

EXPLAIN

DROP TRIGGER on_source_table_update ON source_table;

DROP TRIGGER

CREATE TRIGGER on_source_table_update AFTER UPDATE OR INSERT OR DELETE ON source_table 
FOR EACH ROW EXECUTE PROCEDURE refresh_some_view();

EXPLAIN (ANALYSE, COSTS OFF)
INSERT INTO source_table VALUES (generate_series(1,1000)); 

CREATE TRIGGER

QUERY PLAN

Insert on source_table (actual time=1.690..1.690 rows=0 loops=1)

  ->  ProjectSet (actual time=0.002..0.201 rows=1000 loops=1)

        ->  Result (actual time=0.001..0.001 rows=1 loops=1)

Planning Time: 0.050 ms

Trigger on_source_table_update: time=6921.839 calls=1000

Execution Time: 6923.947 ms

EXPLAIN