Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tbl(id int, title text);

INSERT INTO tbl (id, title)
SELECT g, 'foobar'::text || g
FROM   generate_series(1,100000) g;

CREATE INDEX tbl_id ON tbl(id);

CREATE TABLE

INSERT 0 100000

CREATE INDEX

VACUUM ANALYZE tbl;

VACUUM

CREATE OR REPLACE FUNCTION col_exists(_tbl regclass, _col text)
  RETURNS bool
  LANGUAGE sql STABLE STRICT PARALLEL SAFE AS
$func$
SELECT TRUE
FROM   pg_catalog.pg_attribute
WHERE  attrelid = $1
AND    attname  = $2
AND    NOT attisdropped
AND    attnum   > 0
$func$;

CREATE OR REPLACE FUNCTION col_exists2(_tbl regclass, _col text)
  RETURNS bool
  LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE AS
$func$
BEGIN
RETURN (
   SELECT TRUE
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = $1
   AND    attname  = $2
   AND    NOT attisdropped
   AND    attnum   > 0
   );
END
$func$;

CREATE FUNCTION

CREATE FUNCTION

-- Q0 Rowans Query
EXPLAIN ANALYZE
SELECT id, title
     , CASE WHEN extra_exists THEN extra::text ELSE 'default' END AS extra
FROM   tbl
CROSS  JOIN (
   SELECT EXISTS (
      SELECT FROM information_schema.columns 
      WHERE  table_name  = 'tbl'
      AND    column_name = 'extra'
      )
   ) extra(extra_exists);

QUERY PLAN

Seq Scan on tbl  (cost=81.54..2122.54 rows=100000 width=47) (actual time=0.347..11.594 rows=100000 loops=1)

  InitPlan 1 (returns $8)

    ->  Nested Loop Left Join  (cost=27.54..40.77 rows=1 width=0) (actual time=0.216..0.222 rows=0 loops=1)

          Join Filter: (dep.refobjsubid = a.attnum)

          ->  Merge Right Join  (cost=27.10..27.27 rows=1 width=6) (actual time=0.215..0.221 rows=0 loops=1)

                Merge Cond: (co.oid = a.attcollation)

                ->  Nested Loop  (cost=0.28..114.16 rows=781 width=4) (actual time=0.188..0.189 rows=1 loops=1)

                      Join Filter: ((co.collnamespace = nco.oid) AND ((nco.nspname <> 'pg_catalog'::name) OR (co.collname <> 'default'::name)))

                      Rows Removed by Join Filter: 16

                      ->  Index Scan using pg_collation_oid_index on pg_collation co  (cost=0.28..59.99 rows=781 width=72) (actual time=0.162..0.164 rows=2 loops=1)

                      ->  Materialize  (cost=0.00..1.06 rows=4 width=68) (actual time=0.006..0.009 rows=8 loops=2)

                            ->  Seq Scan on pg_namespace nco  (cost=0.00..1.04 rows=4 width=68) (actual time=0.008..0.010 rows=15 loops=1)

                ->  Sort  (cost=26.83..26.83 rows=1 width=10) (actual time=0.026..0.029 rows=0 loops=1)

                      Sort Key: a.attcollation

                      Sort Method: quicksort  Memory: 25kB

                      ->  Nested Loop  (cost=1.36..26.82 rows=1 width=10) (actual time=0.021..0.024 rows=0 loops=1)

                            ->  Nested Loop  (cost=1.23..26.64 rows=1 width=14) (actual time=0.021..0.023 rows=0 loops=1)

                                  Join Filter: (c.relnamespace = nc.oid)

                                  ->  Nested Loop Left Join  (cost=1.23..25.55 rows=1 width=18) (actual time=0.020..0.023 rows=0 loops=1)

                                        Join Filter: (t.typtype = 'd'::"char")

                                        ->  Nested Loop  (cost=0.82..24.94 rows=1 width=23) (actual time=0.020..0.022 rows=0 loops=1)

                                              ->  Nested Loop  (cost=0.55..16.61 rows=1 width=18) (actual time=0.020..0.021 rows=0 loops=1)

                                                    ->  Index Scan using pg_class_relname_nsp_index on pg_class c  (cost=0.27..8.30 rows=1 width=12) (actual time=0.013..0.014 rows=1 loops=1)

                                                          Index Cond: (relname = 'tbl'::name)

                                                          Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))

                                                    ->  Index Scan using pg_attribute_relid_attnam_index on pg_attribute a  (cost=0.28..8.31 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=1)

                                                          Index Cond: ((attrelid = c.oid) AND (attname = 'extra'::name))

                                                          Filter: ((NOT attisdropped) AND (attnum > 0) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))

                                              ->  Index Scan using pg_type_oid_index on pg_type t  (cost=0.27..8.29 rows=1 width=13) (never executed)

                                                    Index Cond: (oid = a.atttypid)

                                        ->  Nested Loop  (cost=0.40..0.60 rows=1 width=4) (never executed)

                                              ->  Index Scan using pg_type_oid_index on pg_type bt  (cost=0.27..0.42 rows=1 width=8) (never executed)

                                                    Index Cond: (oid = t.typbasetype)

                                              ->  Index Only Scan using pg_namespace_oid_index on pg_namespace nbt  (cost=0.13..0.17 rows=1 width=4) (never executed)

                                                    Index Cond: (oid = bt.typnamespace)

                                                    Heap Fetches: 0

                                  ->  Seq Scan on pg_namespace nc  (cost=0.00..1.05 rows=3 width=4) (never executed)

                                        Filter: (NOT pg_is_other_temp_schema(oid))

                            ->  Index Only Scan using pg_namespace_oid_index on pg_namespace nt  (cost=0.13..0.17 rows=1 width=4) (never executed)

                                  Index Cond: (oid = t.typnamespace)

                                  Heap Fetches: 0

          ->  Nested Loop  (cost=0.44..13.49 rows=1 width=8) (never executed)

                ->  Index Scan using pg_depend_reference_index on pg_depend dep  (cost=0.29..8.31 rows=1 width=12) (never executed)

                      Index Cond: ((refclassid = '1259'::oid) AND (refobjid = c.oid))

                      Filter: ((classid = '1259'::oid) AND (deptype = 'i'::"char"))

                ->  Index Only Scan using pg_sequence_seqrelid_index on pg_sequence seq  (cost=0.15..5.17 rows=1 width=4) (never executed)

                      Index Cond: (seqrelid = dep.objid)

                      Heap Fetches: 0

  InitPlan 2 (returns $17)

    ->  Nested Loop Left Join  (cost=27.54..40.77 rows=1 width=0) (never executed)

          Join Filter: (dep_1.refobjsubid = a_1.attnum)

          ->  Merge Right Join  (cost=27.10..27.27 rows=1 width=6) (never executed)

                Merge Cond: (co_1.oid = a_1.attcollation)

                ->  Nested Loop  (cost=0.28..114.16 rows=781 width=4) (never executed)

                      Join Filter: ((co_1.collnamespace = nco_1.oid) AND ((nco_1.nspname <> 'pg_catalog'::name) OR (co_1.collname <> 'default'::name)))

                      ->  Index Scan using pg_collation_oid_index on pg_collation co_1  (cost=0.28..59.99 rows=781 width=72) (never executed)

                      ->  Materialize  (cost=0.00..1.06 rows=4 width=68) (never executed)

                            ->  Seq Scan on pg_namespace nco_1  (cost=0.00..1.04 rows=4 width=68) (never executed)

                ->  Sort  (cost=26.83..26.83 rows=1 width=10) (never executed)

                      Sort Key: a_1.attcollation

                      ->  Nested Loop  (cost=1.36..26.82 rows=1 width=10) (never executed)

                            ->  Nested Loop  (cost=1.23..26.64 rows=1 width=14) (never executed)

                                  Join Filter: (c_1.relnamespace = nc_1.oid)

                                  ->  Nested Loop Left Join  (cost=1.23..25.55 rows=1 width=18) (never executed)

                                        Join Filter: (t_1.typtype = 'd'::"char")

                                        ->  Nested Loop  (cost=0.82..24.94 rows=1 width=23) (never executed)

                                              ->  Nested Loop  (cost=0.55..16.61 rows=1 width=18) (never executed)

                                                    ->  Index Scan using pg_class_relname_nsp_index on pg_class c_1  (cost=0.27..8.30 rows=1 width=12) (never executed)

                                                          Index Cond: (relname = 'tbl'::name)

                                                          Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))

                                                    ->  Index Scan using pg_attribute_relid_attnam_index on pg_attribute a_1  (cost=0.28..8.31 rows=1 width=14) (never executed)

                                                          Index Cond: ((attrelid = c_1.oid) AND (attname = 'extra'::name))

                                                          Filter: ((NOT attisdropped) AND (attnum > 0) AND (pg_has_role(c_1.relowner, 'USAGE'::text) OR has_column_privilege(c_1.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))

                                              ->  Index Scan using pg_type_oid_index on pg_type t_1  (cost=0.27..8.29 rows=1 width=13) (never executed)

                                                    Index Cond: (oid = a_1.atttypid)

                                        ->  Nested Loop  (cost=0.40..0.60 rows=1 width=4) (never executed)

                                              ->  Index Scan using pg_type_oid_index on pg_type bt_1  (cost=0.27..0.42 rows=1 width=8) (never executed)

                                                    Index Cond: (oid = t_1.typbasetype)

                                              ->  Index Only Scan using pg_namespace_oid_index on pg_namespace nbt_1  (cost=0.13..0.17 rows=1 width=4) (never executed)

                                                    Index Cond: (oid = bt_1.typnamespace)

                                                    Heap Fetches: 0

                                  ->  Seq Scan on pg_namespace nc_1  (cost=0.00..1.05 rows=3 width=4) (never executed)

                                        Filter: (NOT pg_is_other_temp_schema(oid))

                            ->  Index Only Scan using pg_namespace_oid_index on pg_namespace nt_1  (cost=0.13..0.17 rows=1 width=4) (never executed)

                                  Index Cond: (oid = t_1.typnamespace)

                                  Heap Fetches: 0

          ->  Nested Loop  (cost=0.44..13.49 rows=1 width=8) (never executed)

                ->  Index Scan using pg_depend_reference_index on pg_depend dep_1  (cost=0.29..8.31 rows=1 width=12) (never executed)

                      Index Cond: ((refclassid = '1259'::oid) AND (refobjid = c_1.oid))

                      Filter: ((classid = '1259'::oid) AND (deptype = 'i'::"char"))

                ->  Index Only Scan using pg_sequence_seqrelid_index on pg_sequence seq_1  (cost=0.15..5.17 rows=1 width=4) (never executed)

                      Index Cond: (seqrelid = dep_1.objid)

                      Heap Fetches: 0

Planning Time: 14.826 ms

Execution Time: 16.937 ms

EXPLAIN

-- Q1 Basic query with pg_catalog
EXPLAIN ANALYZE
SELECT id, title
     , CASE WHEN col_exists
            THEN extra::text
            ELSE 'default'::text END AS extra
FROM   tbl
CROSS  JOIN (
   SELECT EXISTS (
      SELECT FROM pg_catalog.pg_attribute
      WHERE  attrelid = 'tbl'::regclass
      AND    attname  = 'extra'
      AND    NOT attisdropped
      AND    attnum   > 0)
   ) extra(col_exists);

QUERY PLAN

Seq Scan on tbl  (cost=16.60..2057.61 rows=100000 width=47) (actual time=0.023..11.208 rows=100000 loops=1)

  InitPlan 1 (returns $0)

    ->  Index Scan using pg_attribute_relid_attnam_index on pg_attribute  (cost=0.28..8.30 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)

          Index Cond: ((attrelid = '1782450'::oid) AND (attname = 'extra'::name))

          Filter: ((NOT attisdropped) AND (attnum > 0))

  InitPlan 2 (returns $1)

    ->  Index Scan using pg_attribute_relid_attnam_index on pg_attribute pg_attribute_1  (cost=0.28..8.30 rows=1 width=0) (never executed)

          Index Cond: ((attrelid = '1782450'::oid) AND (attname = 'extra'::name))

          Filter: ((NOT attisdropped) AND (attnum > 0))

Planning Time: 0.269 ms

Execution Time: 14.678 ms

EXPLAIN

-- Q2 Using SQL function
EXPLAIN ANALYZE
SELECT id, title
     , CASE WHEN col_exists('tbl', 'extra') THEN extra::text ELSE 'default' END AS extra
FROM   tbl AS extra;

QUERY PLAN

Seq Scan on tbl extra  (cost=0.00..27041.00 rows=100000 width=47) (actual time=0.198..806.068 rows=100000 loops=1)

Planning Time: 0.090 ms

Execution Time: 814.156 ms

EXPLAIN

-- Q3 Using PL/pgSQL function
SELECT 'Q3' AS query;
EXPLAIN ANALYZE
SELECT id, title
     , CASE WHEN col_exists2('tbl', 'extra') THEN extra::text ELSE 'default' END AS extra
FROM   tbl AS extra;

query

Q3

SELECT 1

QUERY PLAN

Seq Scan on tbl extra  (cost=0.00..27041.00 rows=100000 width=47) (actual time=0.863..1286.095 rows=100000 loops=1)

Planning Time: 0.048 ms

Execution Time: 1295.096 ms

EXPLAIN

-- Q4  SQL function in separate table
EXPLAIN ANALYZE
SELECT id, title, CASE WHEN col_exists THEN extra::text ELSE 'default' END AS extra
FROM   tbl
CROSS  JOIN col_exists('tbl', 'extra') AS extra(col_exists);

QUERY PLAN

Nested Loop  (cost=0.25..2791.26 rows=100000 width=47) (actual time=0.158..21.486 rows=100000 loops=1)

  ->  Function Scan on col_exists extra  (cost=0.25..0.26 rows=1 width=1) (actual time=0.147..0.148 rows=1 loops=1)

  ->  Seq Scan on tbl  (cost=0.00..1541.00 rows=100000 width=15) (actual time=0.008..7.685 rows=100000 loops=1)

Planning Time: 0.126 ms

Execution Time: 25.762 ms

EXPLAIN

-- Q5  PL/pgSQL function in separate table
EXPLAIN ANALYZE
SELECT id, title, CASE WHEN col_exists THEN extra::text ELSE 'default' END AS extra
FROM   tbl
CROSS  JOIN col_exists2('tbl', 'extra') AS extra(col_exists);

QUERY PLAN

Nested Loop  (cost=0.25..2791.26 rows=100000 width=47) (actual time=0.060..20.583 rows=100000 loops=1)

  ->  Function Scan on col_exists2 extra  (cost=0.25..0.26 rows=1 width=1) (actual time=0.050..0.051 rows=1 loops=1)

  ->  Seq Scan on tbl  (cost=0.00..1541.00 rows=100000 width=15) (actual time=0.008..7.604 rows=100000 loops=1)

Planning Time: 0.067 ms

Execution Time: 24.850 ms

EXPLAIN

-- Q6 plain expression using pg_catalog, no additional table -- fastest
EXPLAIN ANALYZE
SELECT id, title
     , CASE WHEN EXISTS (SELECT FROM pg_catalog.pg_attribute
                         WHERE  attrelid = 'tbl'::regclass
                         AND    attname  = 'extra'
                         AND    NOT attisdropped
                         AND    attnum   > 0)
            THEN extra::text
            ELSE 'default' END AS extra
FROM   tbl AS extra;

QUERY PLAN

Seq Scan on tbl extra  (cost=8.30..2049.30 rows=100000 width=47) (actual time=0.019..10.918 rows=100000 loops=1)

  InitPlan 1 (returns $0)

    ->  Index Scan using pg_attribute_relid_attnam_index on pg_attribute  (cost=0.28..8.30 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=1)

          Index Cond: ((attrelid = '1782450'::oid) AND (attname = 'extra'::name))

          Filter: ((NOT attisdropped) AND (attnum > 0))

Planning Time: 0.140 ms

Execution Time: 14.423 ms

EXPLAIN