Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > 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); > > <pre> > ✓ > 1 rows affected > > ✓ > > ✓ > </pre> <!-- --> > -- 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; > > <pre> > 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_2 | dir_process | parent_id | f | 0 | 8 | -1 | <em>null</em> | <em>null</em> | <em>null</em> | <em>null</em> | <em>null</em> | <em>null</em> | <em>null</em> > </pre> <!-- --> > 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; > > <pre> > ✓ > > 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_2 | dir_process | parent_id | f | 0 | 8 | 1000 | <em>null</em> | <em>null</em> | <em>null</em> | <em>null</em> | <em>null</em> | <em>null</em> | <em>null</em> > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=66974db4564e1f456f0f5ab35ae71c99)*
back to fiddle