Postgres - Are views backed by prepared statements?

Question

When creating a view, does PostgreSQL also create a prepared statement for the corresponding query? Or is the query execution plan regenerated each time the view is accessed?

Answer 1

No, a query plan is constructed every time the view is used. Even when the view is joined with other tables or views (you could see views as a kind of macros) . Just look at the resulting query plans:

CREATE table one
        ( one_id INTEGER NOT NULL PRIMARY KEY
        , one_text text
        );
INSERT INTO one (one_id,one_text)
SELECT gs, 'one_' || gs::integer
FROM generate_series (0,10,2) gs;

CREATE table two
        ( two_id INTEGER NOT NULL PRIMARY KEY
        , two_text text
        );
INSERT INTO two (two_id,two_text)
SELECT gs, 'two_' || gs::integer
FROM generate_series (0,10,3) gs;

VACUUM ANALYZE one;
VACUUM ANALYZE two;

CREATE VIEW silly_carth AS
SELECT o.one_id, o.one_text
        , t.two_id, t.two_text
FROM one o
CROSS JOIN two t
        ;

EXPLAIN
SELECT * FROM silly_carth;
EXPLAIN
SELECT * FROM silly_carth WHERE one_id = two_id;

---

Resulting plans:

                            QUERY PLAN                            
------------------------------------------------------------------
 Nested Loop  (cost=0.00..2.41 rows=24 width=20)
   ->  Seq Scan on one o  (cost=0.00..1.06 rows=6 width=10)
   ->  Materialize  (cost=0.00..1.06 rows=4 width=10)
         ->  Seq Scan on two t  (cost=0.00..1.04 rows=4 width=10)
(4 rows)

                            QUERY PLAN                            
------------------------------------------------------------------
 Hash Join  (cost=1.09..2.21 rows=4 width=20)
   Hash Cond: (o.one_id = t.two_id)
   ->  Seq Scan on one o  (cost=0.00..1.06 rows=6 width=10)
   ->  Hash  (cost=1.04..1.04 rows=4 width=10)
         ->  Seq Scan on two t  (cost=0.00..1.04 rows=4 width=10)
(5 rows)