Postgres 9.6

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 TEMP TABLE dir_process (
  id bigint
, volume_id bigint
, parent_id bigint
);

INSERT INTO dir_process VALUES (1,1,1);
ANALYZE;
ALTER TABLE dir_process ALTER COLUMN parent_id SET (n_distinct=1000);
ALTER TABLE

-- n_distinct not set, yet! it was only stored in pg_attribute
SELECT *
FROM   pg_stats
WHERE  tablename = 'dir_process'
AND    attname = 'parent_id'
ORDER  BY attname;


schemaname
tablename
attname
inherited
null_frac
avg_width
n_distinct
most_common_vals
most_common_freqs
histogram_bounds
correlation
most_common_elems
most_common_elem_freqs
elem_count_histogram


pg_temp_1
dir_process
parent_id
f
0
8
-1
null
null
null
null
null
null
null


SELECT 1

ANALYZE dir_process;  -- after fixing n_distinct!
-- now n_distinct has been copied to column statistics!
SELECT *
FROM   pg_stats
WHERE  tablename = 'dir_process'
AND    attname = 'parent_id'
ORDER  BY attname;


schemaname
tablename
attname
inherited
null_frac
avg_width
n_distinct
most_common_vals
most_common_freqs
histogram_bounds
correlation
most_common_elems
most_common_elem_freqs
elem_count_histogram


pg_temp_1
dir_process
parent_id
f
0
8
1000
null
null
null
null
null
null
null


SELECT 1