Postgres 12

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 t (
  name varchar(100),
  x int[],
  y int
)
insert into t
values
  ('AA', $${1,2,3}$$, 1),
  ('BB', $${1,2,3,4}$$, 2),
  ('CC', $${1,2,3}$$, 3),
  ('DD', $${1,2,3, 4}$$, 4),
  ('EE', $${1,2}$$, 6),
  ('FF', $${1,2}$$, 9)
6 rows affected

insert into t(name, y)
select t::text, t
from generate_series(1, 1000) as t
1000 rows affected

create index ix_t_bool
on t((array_length(x, 1) = y))
explain analyze
select *
from t
where (array_length(x, 1) = y) = true


QUERY PLAN


Seq Scan on t  (cost=0.00..20.09 rows=5 width=254) (actual time=0.014..0.092 rows=2 loops=1)

  Filter: (array_length(x, 1) = y)

  Rows Removed by Filter: 1004

Planning Time: 0.393 ms

Execution Time: 0.105 ms


create index ix_t_int
on t(( (array_length(x, 1) = y)::char(1) ))
explain analyze
select *
from t
where (array_length(x, 1) = y)::char(1) = 'Y'


QUERY PLAN


Bitmap Heap Scan on t  (cost=4.31..9.69 rows=5 width=254) (actual time=0.045..0.045 rows=0 loops=1)

  Recheck Cond: (((array_length(x, 1) = y))::character(1) = 'Y'::bpchar)

  ->  Bitmap Index Scan on ix_t_int  (cost=0.00..4.31 rows=5 width=0) (actual time=0.043..0.043 rows=0 loops=1)

        Index Cond: (((array_length(x, 1) = y))::character(1) = 'Y'::bpchar)

Planning Time: 0.250 ms

Execution Time: 0.067 ms


alter table t
add column
  state text generated always as (
    case array_length(x, 1)
      when y
      then 'SOLVED'
      else 'NOT_SOLVED'
    end
  ) stored
create index ix_t_calc
on t(state)
explain analyze
select *
from t
where state = 'SOLVED'


QUERY PLAN


Bitmap Heap Scan on t  (cost=4.31..11.31 rows=5 width=286) (actual time=0.025..0.026 rows=2 loops=1)

  Recheck Cond: (state = 'SOLVED'::text)

  Heap Blocks: exact=1

  ->  Bitmap Index Scan on ix_t_calc  (cost=0.00..4.31 rows=5 width=0) (actual time=0.020..0.020 rows=2 loops=1)

        Index Cond: (state = 'SOLVED'::text)

Planning Time: 0.307 ms

Execution Time: 0.047 ms