How/When do views generate columns-CodePudding

Consider the following setup:

CREATE TABLE person (
  first_name varchar,
  last_name varchar,
  age INT
 );
 
INSERT INTO person (first_name, last_name, age)
VALUES ('pete', 'peterson', 16),
        ('john', 'johnson', 20),
        ('dick', 'dickson', 42),
        ('rob', 'robson', 30);
 
Create OR REPLACE VIEW adult_view AS
SELECT 
  first_name || ' ' || last_name as full_name, 
  age
FROM person;

If I run:

SELECT * 
FROM adult_view
WHERE age > 18;

Will the view generate the full_name column for pete even though he gets filtered out?

Similarly, if I run:

SELECT age
FROM adult_view;

Will the view generate any full_name columns at all?

CodePudding user response：

Concerning your first query:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT full_name
FROM adult_view
WHERE age > 18;
                                    QUERY PLAN                                    
══════════════════════════════════════════════════════════════════════════════════
 Seq Scan on laurenz.person
   Output: (((person.first_name)::text || ' '::text) || (person.last_name)::text)
   Filter: (person.age > 18)
 Query Identifier: 5675263059379476127

So the table is scanned, people under 19 are filtered out, and then the result is calculated. So full_name won't be computed for Pete.

Concerning your second query:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT age       
FROM adult_view
WHERE age > 18;
               QUERY PLAN               
════════════════════════════════════════
 Seq Scan on laurenz.person
   Output: person.age
   Filter: (person.age > 18)
 Query Identifier: -8981994317495194105
(4 rows)

full_name is not calculated at all.