3

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)
2
  • What happens if you add LATERAL JOIN (VALUES(UPPER(foo))) AS v(foo) ON TRUE and then select v.foo, instead of selecting UPPER(foo) directly in the SELECT Nov 30 '21 at 9:39
  • @Charlieface same... EXPLAIN (ANALYZE, VERBOSE, BUFFERS, TIMING OFF) SELECT generate_series(1, 1000000), upper(v.foo) FROM t LEFT JOIN LATERAL (VALUES(UPPER(foo))) AS v(foo) ON TRUE; Nov 30 '21 at 21:11
3

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.