On Fri, Nov 17, 2017 at 01:27:49PM -0300, Alvaro Herrera wrote:
> Justin Pryzby wrote:
> > After adding extended/MV stats to a few of our tables a few days ago, it
> > looks
> > like I wasn't been paying attention and this first crashed 2 nights ago.
> > Why
> > at 1am? not sure. I have an "reindex" job which runs at 1am, and an
> > vacuum/analyze job which runs at 2am, but I don't use cron to change
> > autovac/analyze thresholds..
>
> Can you please show the definition of the table and of the extended
> stats?
gtt=# SELECT stxrelid::regclass, stxname, stxkind FROM pg_statistic_ext ORDER
BY 1;
stxrelid |
stxname | stxkind
-------------------------------------------------+-----------------------------------------------------------+---------
daily_umts_eric_cell_traffic_hs_view_201603 |
daily_umts_eric_cell_traffic_hs_view_201603_key_stats | {d,f}
daily_umts_eric_cell_traffic_hs_eul_view_201603 |
daily_umts_eric_cell_traffic_hs_eul_view_201603_key_stats | {d,f}
daily_eric_umts_rnc_utrancell_view_201603 |
daily_eric_umts_rnc_utrancell_view_201603_key_stats | {d,f}
daily_umts_eric_cell_traffic_hs_view_201504 |
daily_umts_eric_cell_traffic_hs_view_201504_key_stats | {d,f}
daily_umts_eric_cell_traffic_hs_eul_view_201504 |
daily_umts_eric_cell_traffic_hs_eul_view_201504_key_stats | {d,f}
daily_eric_umts_rnc_utrancell_view_201504 |
daily_eric_umts_rnc_utrancell_view_201504_key_stats | {d,f}
daily_enodeb_ncell_view_201603 |
daily_enodeb_ncell_view_201603_key_stats | {d,f}
daily_enodeb_ncell_view_201503 |
daily_enodeb_ncell_view_201503_key_stats | {d,f}
daily_enodeb_ncell_view_201502 |
daily_enodeb_ncell_view_201502_key_stats | {d,f}
daily_enodeb_ncell_view_201501 |
daily_enodeb_ncell_view_201501_key_stats | {d,f}
daily_enodeb_baseband_view_201603 |
daily_enodeb_baseband_view_201603_key_stats | {d,f}
daily_enodeb_baseband_view_201503 |
daily_enodeb_baseband_view_201503_key_stats | {d,f}
daily_enodeb_baseband_view_201502 |
daily_enodeb_baseband_view_201502_key_stats | {d,f}
daily_enodeb_baseband_view_201501 |
daily_enodeb_baseband_view_201501_key_stats | {d,f}
daily_enodeb_cell_view_201603 |
daily_enodeb_cell_view_201603_key_stats | {d,f}
daily_enodeb_cell_view_201502 |
daily_enodeb_cell_view_201502_key_stats | {d,f}
daily_enodeb_201603 |
daily_enodeb_201603_key_stats | {d,f}
daily_enodeb_201503 |
daily_enodeb_201503_key_stats | {d,f}
daily_enodeb_201502 |
daily_enodeb_201502_key_stats | {d,f}
daily_enodeb_201501 |
daily_enodeb_201501_key_stats | {d,f}
daily_enodeb_cell_view_201710 | x
| {d,f}
daily_cdr_pstn_user_201711 |
daily_cdr_pstn_user_201711_key_stats | {d,f}
daily_umts_eric_cell_traffic_hs_eul_view_201711 |
daily_umts_eric_cell_traffic_hs_eul_view_201711_key_stats | {d,f}
daily_umts_eric_cell_traffic_hs_view_201711 |
daily_umts_eric_cell_traffic_hs_view_201711_key_stats | {d,f}
daily_eric_umts_rnc_utrancell_view_201711 |
daily_eric_umts_rnc_utrancell_view_201711_key_stats | {d,f}
daily_enodeb_baseband_view_201711 |
daily_enodeb_baseband_view_201711_key_stats | {d,f}
daily_enodeb_cell_view_201711 |
daily_enodeb_cell_view_201711_key_stats | {d,f}
daily_enodeb_ncell_view_201711 |
daily_enodeb_ncell_view_201711_key_stats | {d,f}
daily_enodeb_201711 |
daily_enodeb_201711_key_stats | {d,f}
(29 rows)
Here's the table which was 1) reindexed (including its toast) and 2)
autovacuumed(crashed):
gtt=# \d+ daily_enodeb_baseband_view_201711
Table
"public.daily_enodeb_baseband_view_201711"
Column | Type | Collation
| Nullable | Default | Storage | Stats target | Description
--------------------------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
device_id | smallint |
| not null | | plain | 400 |
site_id | smallint |
| not null | | plain | 400 |
start_time | timestamp without time zone |
| not null | | plain | 0 |
bbprocessingresource | text |
| not null | | extended | 400 |
interval_seconds | bigint |
| | | plain | |
interval_seconds_min | smallint |
| | | plain | |
interval_seconds_max | smallint |
| | | plain | |
nbevt | bigint |
| | | plain | |
nbevt_min | integer |
| | | plain | |
nbevt_max | integer |
| | | plain | |
[...]
Indexes:
"daily_enodeb_baseband_view_201711_unique_idx" UNIQUE, btree (start_time,
site_id, device_id, bbprocessingresource)
Check constraints:
"daily_enodeb_baseband_view_201711_start_time_check" CHECK (start_time >=
'2017-11-01 00:00:00'::timestamp without time zone AND start_time < '2017-12-01
00:00:00'::timestamp without time zone)
Statistics objects:
"public"."daily_enodeb_baseband_view_201711_key_stats" (ndistinct,
dependencies) ON device_id, site_id, start_time, bbprocessingresource FROM
daily_enodeb_baseband_view_201711
Inherits: daily_enodeb_baseband_view
Options: autovacuum_analyze_scale_factor=0.005, autovacuum_analyze_threshold=2
gtt=# SELECT * FROM pg_statistic_ext WHERE
stxname='daily_enodeb_baseband_view_201711_key_stats';
stxrelid | 691157026
stxname | daily_enodeb_baseband_view_201711_key_stats
stxnamespace | 2200
stxowner | 18819
stxkeys | 1 2 3 4
stxkind | {d,f}
stxndistinct |
stxdependencies |
Justin
