vyegorov, Maxim Boguk,Alexius,
спасибо!
Начну пользоваться советами с конца:
min - работает
max - не дождался.
Табличка InsertOnly - но не давно несколько COPY упало.
Очень вероятно, что autovacuum до нее еще не добрался.
Буду делать vacuum verbose analyze metasearch.reports_tripadvisor_auction_overview_daily2
после set enable_mergejoin = off;
дождаться плана не смог.
1.
2.
3.
4.
5.
6.
7.
8.
9.
explain (analyze, costs, buffers, timing)
select * from metasearch.reports_tripadvisor_auction_overview_daily2 WHERE id > 85637180 ORDER BY id ASC LIMIT 100
Limit (cost=0.00..99.28 rows=100 width=306) (actual time=100.700..122.985 rows=100 loops=1)
Buffers: shared read=10
-> Index Scan using pk_reports_tripadvisor_auction_overview_daily2_id on reports_tripadvisor_auction_overview_daily2 (cost=0.00..576544686.80 rows=580727990 width=306) (actual time=100.696..122.965 rows=100 loops=1)
Index Cond: (id > 85637180)
Buffers: shared read=10
Total runtime: 123.062 ms
select * from pg_stats where tablename='reports_tripadvisor_auction_overview_daily2' and attname='id';
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
schemaname | metasearch
tablename | reports_tripadvisor_auction_overview_daily2
attname | id
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {60014,7738879,15812113,23296015,31093423,40250929,4801
6461,55183598,62148706,103202520,110916130,117848757,124909647,131369750,1388142
34,146287519,154403211,162344426,169955559,210970236,219107062,227231304,2350901
51,247785897,255576036,262854790,269623142,296435933,302767868,311469210,3192921
28,326847719,334652257,341893349,348744849,355552439,362106057,375381723,3828630
23,390949870,398377787,406215160,414187978,421368361,429489136,436080415,4438561
50,451222612,458817065,465923570,473533959,481415635,489401193,497460888,5045770
08,512072063,520170110,527313240,535334652,542189058,550520738,558079456,5646871
41,572198949,579295570,587036416,595615839,603628429,611249395,619651058,6270286
91,635202540,642819427,651012509,658561701,667081294,674855691,682759757,6908874
29,699083265,706522809,714163058,721675347,729752510,737260897,745022418,7523820
89,759235219,766871114,773685967,780757213,787627285,795861741,803570705,8113019
94,818969769,826719431,834110109,841225180,848400884,856312235}
correlation | 0.76847
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
select * from pg_stat_user_tables where relname= 'reports_tripadvisor_auction_overview_daily2'
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
relid | 2306506341
schemaname | metasearch
relname | reports_tripadvisor_auction_overview_daily2
seq_scan | 49
seq_tup_read | 5282917
idx_scan | 43
idx_tup_fetch | 34144284
n_tup_ins | 890625235
n_tup_upd | 0
n_tup_del | 34144160
n_tup_hot_upd | 0
n_live_tup | 614957141
n_dead_tup | 35069569
last_vacuum |
last_autovacuum | 2016-08-23 05:15:24.906623+00
last_analyze | 2016-08-31 14:30:10.234893+00
last_autoanalyze | 2016-08-31 05:57:05.357382+00
vacuum_count | 0
autovacuum_count | 1
analyze_count | 3
autoanalyze_count | 2
|
