Why is FROM VALUES massively faster than visiting a table once?

Given this setup,
CREATE TABLE t(foo)
AS SELECT 'foobar'::text;


Why is this query,
EXPLAIN (ANALYZE, TIMING OFF)
SELECT
generate_series(1, 1000000),
upper(foo)
FROM ( VALUES ('foobar') ) AS t(foo);


Massively faster than this
EXPLAIN (ANALYZE, TIMING OFF)
SELECT
generate_series(1, 1000000),
upper(foo)
FROM t;


Even though these two queries are roughly the same speed,
EXPLAIN (ANALYZE, TIMING OFF)
SELECT generate_series(1, 1000000), foo FROM t;

EXPLAIN (ANALYZE, TIMING OFF)
SELECT generate_series(1, 1000000), foo FROM ( VALUES ('foobar') ) AS t(foo);

Query Plans

=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS, TIMING OFF)
SELECT
  generate_series(1, 1000000),
  upper(foo)
FROM t;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 ProjectSet  (cost=0.00..6800037.20 rows=1360000000 width=36) (actual rows=1000000 loops=1)
   Output: generate_series(1, 1000000), upper(foo)
   Buffers: shared hit=1
   ->  Seq Scan on public.t  (cost=0.00..23.60 rows=1360 width=32) (actual rows=1 loops=1)
         Output: foo
         Buffers: shared hit=1
 Planning Time: 0.087 ms
 JIT:
   Functions: 4
   Options: Inlining true, Optimization true, Expressions true, Deforming true
 Execution Time: 249.514 ms
(11 rows)

=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS, TIMING OFF)
SELECT
  generate_series(1, 1000000),
  upper(foo)
FROM ( VALUES ('foobar') ) AS t(foo);
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 ProjectSet  (cost=0.00..5000.02 rows=1000000 width=36) (actual rows=1000000 loops=1)
   Output: generate_series(1, 1000000), 'FOOBAR'::text
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1)
 Planning Time: 0.116 ms
 Execution Time: 55.310 ms
(5 rows)

The answer is actually here,

Output: generate_series(1, 1000000), 'FOOBAR'::text

vs,

Output: generate_series(1, 1000000), upper(foo)

The one in the top gets inlined as a constant, and the one at the bottom does not.

Thanks to RhodiumToad on irc.libera.chat for the pointer.

StackExchange Database Administrators Q#303274, answer score: 3