Efficiency of querying on views in Postgres 13

Question

Can anyone shed some light on when/how Postgres handles searches against views? I've got a prejudice about querying on views instead of tables, which is definitely wrong. I'm not sure if my prejudice is totally or only partially unjustified.

Lurking in the back of my mind is the idea that the views, in some situations, lacking filtering conditions from a query, generate a large product that is then selected against afterwards by the query conditions. (I think I ran into a case like this in MySQL 5.6 years ago where MySQL generated a CROSS JOIN to execute some outer joins. Even with two records predictably in the result, it took minutes to run. MySQL is no Postgres.)

I've got a view that combines results from two tables using an INNER JOIN. Right now, the total product is around 152M rows. Count(*) takes some time to run, as you might imagine.

I've created two stored function to compare performance. They both use rcl_combined as a RETURN format.

1. rcl_combined_query performs the same select as the rcl_combined view, but includes a where condition against an indexed field.

2. rcl_combined_query_on_view searches against the view directly.

I've listed the code below. When I tried this out, the functions perform the same way, and generate the same simple plans for a "function_scan". Millisecond results, either way.

Is my prejudice completely unfounded? Or are there cases (or edge cases) where it's safer to query directly against a table rather than to query against a view in Postgres 13+? Hopefully, that's not an impossible question. We're using FROM, (INNER) JOIN, LEFT JOIN, and sometimes RIGHT JOIN pretty exclusively on tiny tables, small tables, and somewhat larger tables, still in the under 200M row range.

I appreciate that this is likely an "it depends" sort of a situation, and am hoping to at least clear up some of my mis-assumptions here. I'm not expecting an exact formula, etc. from anyone.

-----------------------------------------------------------------
-- The view used as a result format by both of the functions, and
-- as the search target for the second query.
-----------------------------------------------------------------
CREATE OR REPLACE VIEW api.rcl_combined AS

SELECT
    record_changes_log.id,
    record_changes_log.record_id,
    record_changes_log.table_number,
    record_changes_log.updated_dts,
    record_changes_log.updated_by,
    record_changes_log.app_type,
    record_changes_log.machine_name,
    record_changes_log_detail.id AS details_id,
    record_changes_log_detail.field_name,
    record_changes_log_detail.old_value,
    record_changes_log_detail.new_value

FROM (record_changes_log
JOIN record_changes_log_detail ON ((record_changes_log.id = record_changes_log_detail.record_changes_log_id)));

-----------------------------------------------------------------
-- A function that returns the same data as the pet_pretty view
-- using the rcl_combined view format/type.
-----------------------------------------------------------------
CREATE OR REPLACE FUNCTION rcl_combined_query (record_id_in uuid)
     RETURNS  rcl_combined -- Used for the format definition and dependency tracking.

LANGUAGE sql STABLE

AS
$BODY$

-- The select, from and join recapitulate what's in the view.
SELECT
    record_changes_log.id,
    record_changes_log.record_id,
    record_changes_log.table_number,
    record_changes_log.updated_dts,
    record_changes_log.updated_by,
    record_changes_log.app_type,
    record_changes_log.machine_name,
    record_changes_log_detail.id AS details_id,
    record_changes_log_detail.field_name,
    record_changes_log_detail.old_value,
    record_changes_log_detail.new_value

FROM record_changes_log
JOIN record_changes_log_detail
  ON record_changes_log.id = record_changes_log_detail.record_changes_log_id

WHERE record_changes_log.record_id = record_id_in;

$BODY$;

-----------------------------------------------------------------
-- A function that returns the same data as the pet_pretty view
-- by searching on it.
-----------------------------------------------------------------
-- Just being thorough here, I'm very unlikely to write a function to query a view.

CREATE OR REPLACE FUNCTION rcl_combined_query_on_view (record_id_in uuid)
     RETURNS  rcl_combined -- Used for the format definition and dependency tracking.

LANGUAGE sql STABLE

AS
$BODY$

SELECT * from rcl_combined where record_id = record_id_in;

$BODY$;

Answer 1

A view in PostgreSQL is basically like a macro in a programming language. Your query is rewritten when it references a view to effectively expand the view into its definition.

So, any problems the planner has pushing conditions into your view it would also have with an identical query written out "in full".

A "materialized view" is a different thing altogether though. Don't mix them up. It stores the results of a view's query in an underlying table and later queries will be against that. You might typically use that for a slowly changing summary, e.g. daily sales totals by region.