DBA Hub

📋Steps in this guide1/1

Update statistics of a table using analyze

-- Analyze stats for a table testanalyze(schema is public)

postgresql configurationintermediate
by PostgreSQL
11 views
1

Update statistics of a table using analyze

Code/Command (click line numbers to comment):

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
26
27
28
29
30
31
32
33
34
-- Analyze stats for a table testanalyze(schema is public)
dbaclass=#
analyze testanalyze;
ANALYZE
-- For analyzing selected columns for emptab table ( schema is dbatest)
dbaclass=#
analyze dbatest.emptab (datname,datdba);
ANALYZE
dbaclass=#
select relname,reltuples from pg_class where relname in ('testanalyze','emptab');
relname      | reltuples
-------------+-----------
testanalyze  | 4
emptab       | 4
(2 rows)
dbaclass=#
select schemaname,relname,analyze_count,last_analyze,last_autoanalyze from pg_stat_user_tables where relname in ('testanalyze','emptab');
schemaname  | relname     | analyze_count | last_analyze                     | last_autoanalyze
------------+-------------+---------------+----------------------------------+------------------
public      | testanalyze | 1             | 2020-07-21 17:00:49.687053+05:30 |
dbatest     | emptab      | 1             | 2020-07-21 17:10:01.111517+05:30 |
(2 rows)
---Analyze command with verbose command
dbaclass=#
analyze verbose dbatest.emptab (datname,datdba);
INFO:  analyzing "dbatest.emptab"
INFO:  "emptab": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows
ANALYZE
---Analyze tables in the current schema that the user has access to.
dbaclass=#
analyze ;
ANALYZE
NOTE
: ANALYZE requires only a read lock on the target table, so it can run in parallel with other activity on the table.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!