Thread: Column missing from pg_statistics
Hello,
I'm DBA for several quite large postgres databases on debian servers. Current version:
PostgreSQL 9.2.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
but the problem I'm about to describe was discovered before upgrade, while running v9.2.4.
I have set up a cron-job that sends me daily reports on bloat amount in tables and indices, which I calculate using pg_stats, not pgstattuple, for performance and I/O reasons. If the bloat amount or percentage are big enough, I use pg_repack to get rid of it. At some point I noticed, that some tables keep showing up in the reports with the same amount of bloat, which pg_repack was seemingly unable to remove. Investigation showed that pgstattuple gave very different results than my bloat-finding query. Reason - for some tables there are some columns that never show up in pg_statistics.
I'm going to give an example of a table that has this issue. I have vacuumed it, analyzed it, used "alter column set statistic" specifically on the missing column - no results. I hope someone has encountered this issue before or at least could give me some new ideas, why this is or how to fix it.
Here's the example:
# \d+ d3s_log
Table "public.d3s_log"
Column | Type | Modifiers | Storage | Stats target | Description
---------------+-----------------------------+------------------------------------------------------+----------+--------------+-------------
id | bigint | not null default nextval('d3s_log_id_seq'::regclass) | plain | |
users_id | integer | not null | plain | |
bet_id | integer | not null | plain | |
bet_state | integer | | plain | |
d3s_trans_id | character varying(255) | not null | extended | |
operation | d3s_operation_enum | not null | plain | |
created | timestamp without time zone | not null default now() | plain | |
success | boolean | not null | plain | |
response_time | timestamp without time zone | | plain | |
path | character varying(255) | default NULL::character varying | extended | |
params | text | | extended | |
request | text | | extended | 1000 |
response | text | | extended | |
Indexes:
"d3s_log_pkey" PRIMARY KEY, btree (id)
"d3s_log_bet_id_operation_success" btree (bet_id, operation, success)
"d3s_log_bets_id_index" btree (bet_id)
"d3s_log_created" btree (created)
"d3s_log_users_id_index" btree (users_id)
Has OIDs: no
# SELECT s.staattnum, a.attname FROM pg_statistic s INNER JOIN pg_attribute a ON (a.attrelid,a.attnum)=(s.starelid,s.staattnum) WHERE starelid = 'd3s_log'::regclass;
staattnum | attname
-----------+---------------
1 | id
2 | users_id
3 | bet_id
4 | bet_state
5 | d3s_trans_id
6 | operation
7 | created
8 | success
9 | response_time
10 | path
11 | params
13 | response
(12 rows)
## As can be seen, column 12, "request", is missing.
# select tablename, attname, null_frac, avg_width from pg_stats where tablename = 'd3s_log';
tablename | attname | null_frac | avg_width
-----------+---------------+------------+-----------
d3s_log | id | 0 | 8
d3s_log | users_id | 0 | 4
d3s_log | bet_id | 0 | 4
d3s_log | bet_state | 1 | 4
d3s_log | d3s_trans_id | 0 | 23
d3s_log | operation | 0 | 4
d3s_log | created | 0 | 8
d3s_log | success | 0 | 1
d3s_log | response_time | 0 | 8
d3s_log | path | 0.00303333 | 48
d3s_log | params | 0 | 180
d3s_log | response | 0.00123333 | 456
(12 rows)
## Again, "request" is missing, but there were small changes in avg_width for other columns after analyze, so some statistics calculation seems to be working.
The difference between pgstattuple and calculated bloat is 6.5 GB out of 15 GB total table size, which is approximately the amount of data stored in the extended "request" column.
I also did pg_dump of this table and restored it on another server, running v9.2.4 - no errors, analyze calculates statistics, but "request" is still missing there, too.
I have also checked other tables with the same problem - the common thing seems to be that all the missing columns are extended, type text or varchar, but I'm not 100% sure, haven't checked them all yet. The size of the missing column is not a common thing - on some tables the missing column is almost empty.
Looking forward to your answers and ideas,
Katie
I'm DBA for several quite large postgres databases on debian servers. Current version:
PostgreSQL 9.2.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
but the problem I'm about to describe was discovered before upgrade, while running v9.2.4.
I have set up a cron-job that sends me daily reports on bloat amount in tables and indices, which I calculate using pg_stats, not pgstattuple, for performance and I/O reasons. If the bloat amount or percentage are big enough, I use pg_repack to get rid of it. At some point I noticed, that some tables keep showing up in the reports with the same amount of bloat, which pg_repack was seemingly unable to remove. Investigation showed that pgstattuple gave very different results than my bloat-finding query. Reason - for some tables there are some columns that never show up in pg_statistics.
I'm going to give an example of a table that has this issue. I have vacuumed it, analyzed it, used "alter column set statistic" specifically on the missing column - no results. I hope someone has encountered this issue before or at least could give me some new ideas, why this is or how to fix it.
Here's the example:
# \d+ d3s_log
Table "public.d3s_log"
Column | Type | Modifiers | Storage | Stats target | Description
---------------+-----------------------------+------------------------------------------------------+----------+--------------+-------------
id | bigint | not null default nextval('d3s_log_id_seq'::regclass) | plain | |
users_id | integer | not null | plain | |
bet_id | integer | not null | plain | |
bet_state | integer | | plain | |
d3s_trans_id | character varying(255) | not null | extended | |
operation | d3s_operation_enum | not null | plain | |
created | timestamp without time zone | not null default now() | plain | |
success | boolean | not null | plain | |
response_time | timestamp without time zone | | plain | |
path | character varying(255) | default NULL::character varying | extended | |
params | text | | extended | |
request | text | | extended | 1000 |
response | text | | extended | |
Indexes:
"d3s_log_pkey" PRIMARY KEY, btree (id)
"d3s_log_bet_id_operation_success" btree (bet_id, operation, success)
"d3s_log_bets_id_index" btree (bet_id)
"d3s_log_created" btree (created)
"d3s_log_users_id_index" btree (users_id)
Has OIDs: no
# SELECT s.staattnum, a.attname FROM pg_statistic s INNER JOIN pg_attribute a ON (a.attrelid,a.attnum)=(s.starelid,s.staattnum) WHERE starelid = 'd3s_log'::regclass;
staattnum | attname
-----------+---------------
1 | id
2 | users_id
3 | bet_id
4 | bet_state
5 | d3s_trans_id
6 | operation
7 | created
8 | success
9 | response_time
10 | path
11 | params
13 | response
(12 rows)
## As can be seen, column 12, "request", is missing.
# select tablename, attname, null_frac, avg_width from pg_stats where tablename = 'd3s_log';
tablename | attname | null_frac | avg_width
-----------+---------------+------------+-----------
d3s_log | id | 0 | 8
d3s_log | users_id | 0 | 4
d3s_log | bet_id | 0 | 4
d3s_log | bet_state | 1 | 4
d3s_log | d3s_trans_id | 0 | 23
d3s_log | operation | 0 | 4
d3s_log | created | 0 | 8
d3s_log | success | 0 | 1
d3s_log | response_time | 0 | 8
d3s_log | path | 0.00303333 | 48
d3s_log | params | 0 | 180
d3s_log | response | 0.00123333 | 456
(12 rows)
## Again, "request" is missing, but there were small changes in avg_width for other columns after analyze, so some statistics calculation seems to be working.
The difference between pgstattuple and calculated bloat is 6.5 GB out of 15 GB total table size, which is approximately the amount of data stored in the extended "request" column.
I also did pg_dump of this table and restored it on another server, running v9.2.4 - no errors, analyze calculates statistics, but "request" is still missing there, too.
I have also checked other tables with the same problem - the common thing seems to be that all the missing columns are extended, type text or varchar, but I'm not 100% sure, haven't checked them all yet. The size of the missing column is not a common thing - on some tables the missing column is almost empty.
Looking forward to your answers and ideas,
Katie
Kadri Raudsepp <kadri.raudsepp@nordicgaming.com> writes: > I have set up a cron-job that sends me daily reports on bloat amount in > tables and indices, which I calculate using pg_stats, not pgstattuple, for > performance and I/O reasons. If the bloat amount or percentage are big > enough, I use pg_repack to get rid of it. At some point I noticed, that > some tables keep showing up in the reports with the same amount of bloat, > which pg_repack was seemingly unable to remove. Investigation showed that > pgstattuple gave very different results than my bloat-finding query. > Reason - for some tables there are some columns that never show up in > pg_statistics. Hmm. Eyeballing the ANALYZE code, I note that it will decide that it hasn't got any valid statistics for a column if (1) it finds no NULL values and (2) every single sampled value in the column is too wide (more than WIDTH_THRESHOLD = 1024 bytes wide). Does this describe your problematic column? It seems like the code is being too conservative here --- it could at least generate valid values for stanullfrac and stawidth. I'm inclined to think maybe it should also set stadistinct = -1 ("unique") in this case, since the basic assumption that validates ignoring very wide values is that they aren't duplicates. regards, tom lane