Objective: Perform a simple select (without any preference) and see how sorted the data is... if the default sort isnt good for you. How to fix it in postgresql?
Connection details:
pgtst_db=> \c
You are now connected to database "pgtst_db" as user "pgtst_usr".
pgtst_db=>
List of tables found in the test db:
pgtst_db=> show search_path;
search_path
-----------------
"$user", public
(1 row)
pgtst_db=> SET search_path TO pgtst_schema;
SET
pgtst_db=> show search_path;
search_path
--------------
pgtst_schema
(1 row)
pgtst_db=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------------+------------+-------+-----------+-------------+---------------+------------+-------------
pgtst_schema | PGTST_TBL1 | table | pgtst_usr | permanent | heap | 8192 bytes |
pgtst_schema | pgtst_tbl1 | table | pgtst_usr | permanent | heap | 825 MB |
(2 rows)
pgtst_db=>
pgtst_db=> select schemaname,tablename,tableowner,hasindexes from pg_tables where schemaname='pgtst_schema' order by 1;
schemaname | tablename | tableowner | hasindexes
--------------+------------+------------+------------
pgtst_schema | pgtst_tbl1 | pgtst_usr | t
pgtst_schema | PGTST_TBL1 | pgtst_usr | t
(2 rows)
pgtst_db=>
select relname table_name
,relnamespace schema_name
,reltype relation_type
,relowner owner
,relpages pages_or_blocks
,reltuples numrows
,relhasindex has_index
from pg_class
where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema') order by 2,1;
table_name | schema_name | relation_type | owner | pages_or_blocks | numrows | has_index
-----------------+-------------+---------------+-------+-----------------+--------------+-----------
PGTST_TBL1 | 24582 | 24592 | 16388 | 0 | -1 | t
PGTST_TBL1_pkey | 24582 | 0 | 16388 | 1 | 0 | f
pgtst_tbl1 | 24582 | 24585 | 16388 | 105556 | 9.999327e+06 | t <<<< 105556*8192/1024/1024 => 824.65MB
pgtst_tbl1_pkey | 24582 | 0 | 16388 | 27421 | 9.999327e+06 | f
(4 rows)
pgtst_db=>
,relnamespace schema_name
,reltype relation_type
,relowner owner
,relpages pages_or_blocks
,reltuples numrows
,relhasindex has_index
from pg_class
where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema') order by 2,1;
table_name | schema_name | relation_type | owner | pages_or_blocks | numrows | has_index
-----------------+-------------+---------------+-------+-----------------+--------------+-----------
PGTST_TBL1 | 24582 | 24592 | 16388 | 0 | -1 | t
PGTST_TBL1_pkey | 24582 | 0 | 16388 | 1 | 0 | f
pgtst_tbl1 | 24582 | 24585 | 16388 | 105556 | 9.999327e+06 | t <<<< 105556*8192/1024/1024 => 824.65MB
pgtst_tbl1_pkey | 24582 | 0 | 16388 | 27421 | 9.999327e+06 | f
(4 rows)
pgtst_db=>
Row sorted or not?
pgtst_db=> select * from pgtst_schema.pgtst_tbl1 limit 100 offset 100;
id | name
-----+-----------------------------------------------------------------------------------------------------
101 | T
102 | TS
103 | TST
104 | CTST
105 | CHTST
106 | CHKTST
107 | CHKCTST
108 | CHKCHTST
109 | CHKCHKTST
110 | CHKCHKCTST
111 | CHKCHKCHTST
112 | CHKCHKCHKTST
113 | CHKCHKCHKCTST
114 | CHKCHKCHKCHTST
115 | CHKCHKCHKCHKTST
116 | CHKCHKCHKCHKCTST
117 | CHKCHKCHKCHKCHTST
118 | CHKCHKCHKCHKCHKTST
119 | CHKCHKCHKCHKCHKCTST
120 | CHKCHKCHKCHKCHKCHTST
121 | CHKCHKCHKCHKCHKCHKTST
122 | CHKCHKCHKCHKCHKCHKCTST
123 | CHKCHKCHKCHKCHKCHKCHTST
124 | CHKCHKCHKCHKCHKCHKCHKTST
125 | CHKCHKCHKCHKCHKCHKCHKCTST
126 | CHKCHKCHKCHKCHKCHKCHKCHTST
127 | CHKCHKCHKCHKCHKCHKCHKCHKTST
128 | CHKCHKCHKCHKCHKCHKCHKCHKCTST
129 | CHKCHKCHKCHKCHKCHKCHKCHKCHTST
130 | CHKCHKCHKCHKCHKCHKCHKCHKCHKTST
Execution plan:
pgtst_db-> select * from pgtst_schema.pgtst_tbl1 limit 100 offset 100;
QUERY PLAN
----------------------------------------------------------------------------
Limit (cost=2.06..4.11 rows=100 width=54)
-> Seq Scan on pgtst_tbl1 (cost=0.00..205549.27 rows=9999327 width=54)
(2 rows)
pgtst_db=>
>>>>> looks like the rows are sorted. I have used limit and offset togather to examine the row sorting nature after 100 rows. The way we loaded the data (using generate_series) ensured the records in sorted form.
But before we move to the next step to assess this row sorting, let us actually examine the rowaddress etc..
In oracle terms a scattered read or FTS is the equivalent form of a "Seq Scan" on postgresql db.
Now let us use a mehtod to collapse the row sort...
CREATE TABLE pgtst_tbl1_random AS SELECT *
FROM pgtst_tbl1
ORDER BY random();
Actual:
pgtst_db=> CREATE TABLE pgtst_tbl1_random AS SELECT *
pgtst_db-> FROM pgtst_tbl1
pgtst_db-> ORDER BY random();
SELECT 10000000
pgtst_db=> \d pgtst_tbl1_random
Table "pgtst_schema.pgtst_tbl1_random"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
pgtst_db=>
pgtst_db=>
We lost the index, let us create a primary key index on id column.
alter table pgtst_tbl1_random add primary key (id);
pgtst_db=> alter table pgtst_tbl1_random add primary key (id);
ALTER TABLE
pgtst_db=>
pgtst_db=> \d pgtst_tbl1_random
Table "pgtst_schema.pgtst_tbl1_random"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text | | |
Indexes:
"pgtst_tbl1_random_pkey" PRIMARY KEY, btree (id)
pgtst_db=>
pgtst_db-> where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema') order by 2,1;
table_name | schema_name | relation_type | owner | pages_or_blocks | numrows | has_index
------------------------+-------------+---------------+-------+-----------------+--------------+-----------
PGTST_TBL1 | 24582 | 24592 | 16388 | 0 | -1 | t
PGTST_TBL1_pkey | 24582 | 0 | 16388 | 1 | 0 | f
pgtst_tbl1 | 24582 | 24585 | 16388 | 105556 | 9.999327e+06 | t
pgtst_tbl1_pkey | 24582 | 0 | 16388 | 27421 | 9.999327e+06 | f
pgtst_tbl1_random | 24582 | 24599 | 16388 | 105786 | 1e+07 | t
pgtst_tbl1_random_pkey | 24582 | 0 | 16388 | 27422 | 1e+07 | f
(6 rows)
pgtst_db=>
If you see the first data load is autovaccumed by the postgresql process.
Anyway we analyzed it one more time.
pgtst_db=> analyze pgtst_tbl1_random;
ANALYZE
pgtst_db=>
Let us query the table using limit and see how sorted is the data.
select * from pgtst_tbl1_random limit 100 rows;
pgtst_db=> select * from pgtst_tbl1_random limit 100;
id | name
---------+-----------------------------------------------------------------------------------------------------
6288820 | CHKCHKCHKCHKCHKCHTST
6979227 | CHKCHKCHKCHKCHKCHKCHKCHKTST
1395170 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
2664160 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
1690706 | CHKTST
6525700 |
4397957 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
1699603 | TST
4810266 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
7223631 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
1136186 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
3626291 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
1698698 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
7635 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
4468453 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
1401870 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
1091503 | TST
6794916 | CHKCHKCHKCHKCTST
9804103 | TST
9133509 | CHKCHKTST
6616994 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
8108546 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
3642179 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
2511005 | CHTST
8485883 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
7165948 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
1888675 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
8336776 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
1526729 | CHKCHKCHKCHKCHKCHKCHKCHKCHTST
252438 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
>>>> the data is a mess.
pgtst_db=> explain select * from pgtst_tbl1_random limit 100;
QUERY PLAN
-----------------------------------------------------------------------------------
Limit (cost=0.00..2.06 rows=100 width=54)
-> Seq Scan on pgtst_tbl1_random (cost=0.00..205785.22 rows=9999922 width=54)
(2 rows)
pgtst_db=>
Let us try to use index to see if the data is arriving as sorted....
explain select * from pgtst_tbl1_random where id < 100;
pgtst_db=> explain select * from pgtst_tbl1_random where id < 100;
QUERY PLAN
--------------------------------------------------------------------------------------
Bitmap Heap Scan on pgtst_tbl1_random (cost=5.17..377.82 rows=95 width=54)
Recheck Cond: (id < 100)
-> Bitmap Index Scan on pgtst_tbl1_random_pkey (cost=0.00..5.15 rows=95 width=0)
Index Cond: (id < 100)
(4 rows)
pgtst_db=>
We seem to use the index, which obviously will be in sorted order. Let us see the data...
pgtst_db=> select * from pgtst_tbl1_random where id < 100;
id | name
----+-----------------------------------------------------------------------------------------------------
92 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
73 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
70 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
47 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
21 | CHKCHKCHKCHKCHKCHKTST
51 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
11 | CHKCHKCHTST
94 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
50 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
4 | CTST
86 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
15 | CHKCHKCHKCHKTST
75 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
22 | CHKCHKCHKCHKCHKCHKCTST
74 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
59 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
64 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
48 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
28 | CHKCHKCHKCHKCHKCHKCHKCHKCTST
7 | CHKCTST
66 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
83 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
97 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
40 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
69 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
31 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCTST
39 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
1 | T
80 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHTST
60 | CHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKCHKTST
pgtst_db=>
So the data isnt sorted, since we didnt really sort the output using order by clause. The query engine printed the records as when it was available.
Now the objective is to get this output sorted.....
cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey;
Fired the above command around 1:22pm IST or as per the pg_stat_activity output it shows "query_start | 2022-10-24 13:27:57.759631+05:30".
The time now - 01:50PM IST 24Oct2022. Looking at the pg_stat_activity, I see we have the command is still inpogress. Not sure how much is done and is left.
Fired another query in the meantime selecting the table pgtst_tbl1_random, the select query is stuck and it says is lock wait.
postgres=# \x on
Expanded display is on.
postgres=# select * from pg_stat_activity;
...
-[ RECORD 3 ]----+----------------------------------------------------------------
datid | 16390
datname | pgtst_db
pid | 1776
leader_pid |
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2022-10-24 13:44:37.650432+05:30
xact_start | 2022-10-24 13:48:53.235688+05:30
query_start | 2022-10-24 13:48:53.235688+05:30
state_change | 2022-10-24 13:48:53.239728+05:30
wait_event_type | Lock
wait_event | relation
state | active
backend_xid |
backend_xmin | 747
query_id |
query | select * from pgtst_schema.pgtst_tbl1_random limit 10;
backend_type | client backend
-[ RECORD 4 ]----+----------------------------------------------------------------
datid | 16390
datname | pgtst_db
pid | 1356
leader_pid |
usesysid | 16388
usename | pgtst_usr
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2022-10-24 12:51:48.161825+05:30
xact_start | 2022-10-24 13:27:57.759631+05:30
query_start | 2022-10-24 13:27:57.759631+05:30
state_change | 2022-10-24 13:27:57.759714+05:30
wait_event_type | IO
wait_event | DataFileRead
state | active
backend_xid | 747
backend_xmin | 747
query_id |
query | cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey;
backend_type | client backend
-[ RECORD 5 ]----+----------------------------------------------------------------
datid | 5
datname | postgres
pid | 1850
leader_pid |
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2022-10-24 13:49:52.123753+05:30
xact_start | 2022-10-24 13:51:22.655539+05:30
query_start | 2022-10-24 13:51:22.655539+05:30
state_change | 2022-10-24 13:51:22.656856+05:30
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin | 747
query_id |
query | select * from pg_stat_activity;
backend_type | client backend
.. [trimmed to show only necessary records]
postgres=#
pgtst_db=> cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey;
INFO: clustering "pgtst_schema.pgtst_tbl1_random" using index scan on "pgtst_tbl1_random_pkey"
INFO: "pgtst_schema.pgtst_tbl1_random": found 0 removable, 10000000 nonremovable row versions in 105786 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 181.25 s, system: 882.18 s, elapsed: 2869.87 s.
CLUSTER
pgtst_db=>
Nearly 1hr it took to transform 862MB of data. The query we ran in other session fetched the records in sorted order. Like we mentioned it held an exclusive lock and didnt allow even select statements on the table.
The poor part is there is no inbuild tool with postgresql to track the progress of the cluster operation. I have checked statio, stat, class tables, none of them showed any values changing.
pgtst_db=# select * from pgtst_schema.pgtst_tbl1_random limit 10;
id | name
----+------------
1 | T
2 | TS
3 | TST
4 | CTST
5 | CHTST
6 | CHKTST
7 | CHKCTST
8 | CHKCHTST
9 | CHKCHKTST
10 | CHKCHKCTST
(10 rows)
pgtst_db=#
Now the pg_stat_activity shows...
postgres=# select * from pg_stat_activity where pid in (1356,1850);
-[ RECORD 1 ]----+----------------------------------------------------------------
datid | 16390
datname | pgtst_db
pid | 1356
leader_pid |
usesysid | 16388
usename | pgtst_usr
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2022-10-24 12:51:48.161825+05:30
xact_start |
query_start | 2022-10-24 13:27:57.759631+05:30
state_change | 2022-10-24 14:16:08.812693+05:30 <<<<<< not when active the timestamp matches the query start time
wait_event_type | Client
wait_event | ClientRead
state | idle
backend_xid |
backend_xmin |
query_id |
query | cluster verbose pgtst_tbl1_random using pgtst_tbl1_random_pkey;
backend_type | client backend
-[ RECORD 2 ]----+----------------------------------------------------------------
datid | 5
datname | postgres
pid | 1850
leader_pid |
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2022-10-24 13:49:52.123753+05:30
xact_start | 2022-10-24 14:19:27.687243+05:30
query_start | 2022-10-24 14:19:27.687243+05:30
state_change | 2022-10-24 14:19:27.688594+05:30
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin | 748
query_id |
query | select * from pg_stat_activity where pid in (1356,1850);
backend_type | client backend
postgres=#
Let us analyze the table and check the explain output:
pgtst_db=> analyze verbose pgtst_tbl1_random;
INFO: analyzing "pgtst_schema.pgtst_tbl1_random"
INFO: "pgtst_tbl1_random": scanned 30000 of 105883 pages, containing 2833232 live rows and 0 dead rows; 30000 rows in sample, 9999703 estimated total rows
ANALYZE
pgtst_db=>
pgtst_db=# explain select * from pgtst_schema.pgtst_tbl1_random limit 10;
QUERY PLAN
-----------------------------------------------------------------------------------
Limit (cost=0.00..0.21 rows=10 width=54)
-> Seq Scan on pgtst_tbl1_random (cost=0.00..205880.03 rows=9999703 width=54)
(2 rows)
pgtst_db=#
pgtst_db=> select relname table_name
pgtst_db-> ,relnamespace schema_name
pgtst_db-> ,reltype relation_type
pgtst_db-> ,relowner owner
pgtst_db-> ,relpages pages_or_blocks
pgtst_db-> ,reltuples numrows
pgtst_db-> ,relhasindex has_index
pgtst_db-> from pg_class
pgtst_db-> where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema') order by 2,1;
table_name | schema_name | relation_type | owner | pages_or_blocks | numrows | has_index
------------------------+-------------+---------------+-------+-----------------+--------------+-----------
PGTST_TBL1 | 24582 | 24592 | 16388 | 0 | -1 | t
PGTST_TBL1_pkey | 24582 | 0 | 16388 | 1 | 0 | f
pgtst_tbl1 | 24582 | 24585 | 16388 | 105556 | 9.999327e+06 | t
pgtst_tbl1_pkey | 24582 | 0 | 16388 | 27421 | 9.999327e+06 | f
pgtst_tbl1_random | 24582 | 24599 | 16388 | 105883 | 9.999703e+06 | t
pgtst_tbl1_random_pkey | 24582 | 0 | 16388 | 27422 | 9.999703e+06 | f
(6 rows)
pgtst_db=>
<<<< notice not much change in statistics. The number of blocks increased a bit (100+)
As such as cluster operation wasnt trackable, but after the operation is finished the necessary tables were updated.
pgtst_db=# select * from pg_stat_user_tables where schemaname='pgtst_schema';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n
_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum | last_vacuum | last
_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_cou
nt | analyze_count | autoanalyze_count
-------+--------------+-------------------+----------+--------------+----------+---------------+-----------+-----------+--
---------+---------------+------------+------------+---------------------+--------------------+-------------+-------------
---------------------+----------------------------------+----------------------------------+--------------+---------------
---+---------------+-------------------
24597 | pgtst_schema | pgtst_tbl1_random | 8 | 30000120 | 4 | 10000099 | 10000000 | 0 |
0 | 0 | 9999703 | 0 | 0 | 0 | | 2022-10-24 1
3:17:50.696014+05:30 | 2022-10-24 14:26:57.748114+05:30 | 2022-10-24 13:17:54.87431+05:30 | 0 |
1 | 2 | 1
24590 | pgtst_schema | PGTST_TBL1 | 1 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | |
| | | 0 |
0 | 0 | 0
24583 | pgtst_schema | pgtst_tbl1 | 7 | 10000413 | 0 | 0 | 10000000 | 0 |
0 | 0 | 9999327 | 0 | 0 | 0 | | 2022-10-23 2
1:48:25.73182+05:30 | 2022-10-23 23:09:11.140771+05:30 | 2022-10-23 21:48:29.010948+05:30 | 0 |
1 | 2 | 1
(3 rows)
pgtst_db=#
pgtst_db=> \d+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------------+-------------------+-------+-----------+-------------+---------------+------------+-------------
pgtst_schema | PGTST_TBL1 | table | pgtst_usr | permanent | heap | 8192 bytes |
pgtst_schema | pgtst_tbl1 | table | pgtst_usr | permanent | heap | 825 MB |
pgtst_schema | pgtst_tbl1_random | table | pgtst_usr | permanent | heap | 827 MB |
(3 rows)
pgtst_db=>
This closes this blog.
Thank you!
No comments:
Post a Comment