Thread: pg_total_relation_size shows 0 byt table has thousands of rows

pg_total_relation_size shows 0 byt table has thousands of rows

From

Sbob

Date:

11 April, 21:10:55

All;


I have a table that has 39,000 rows in it, but a 
pg_total_relation_size('schema.table') shows a size of 0

I did a vacuum and an analyze but no change

Thoughts?

Re: pg_total_relation_size shows 0 byt table has thousands of rows

From

"David G. Johnston"

Date:

11 April, 21:30:11

On Thu, Apr 11, 2024 at 11:11 AM Sbob <sbob@quadratum-braccas.com> wrote:

I have a table that has 39,000 rows in it, but a
pg_total_relation_size('schema.table') shows a size of 0

I did a vacuum and an analyze but no change

Thoughts?

Copy and paste the exact queries you are running and the results they are giving.

David J.

Re: pg_total_relation_size shows 0 byt table has thousands of rows

From

Sbob

Date:

11 April, 22:22:12

This is on PG version 14

sales_info=# select count(*) from sales_info.reject_errors;
count
--------
688422
(1 row)

sales_info=# select pg_total_relation_size ('sales_info.reject_errors');
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# analyze sales_info.reject_errors;
ANALYZE
sales_info=# vacuum sales_info.reject_errors;
VACUUM
sales_info=# select pg_total_relation_size ('sales_info.reject_errors');
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# select pg_total_relation_size ('sales_info.reject_errors'::regclass);
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# select oid from pg_class where relname = 'reject_errors';
oid
-------
39379
(1 row)

sales_info=# select pg_total_relation_size (39379);
pg_total_relation_size
------------------------
                      0
(1 row)

On 4/11/24 12:30, David G. Johnston wrote:

On Thu, Apr 11, 2024 at 11:11 AM Sbob <sbob@quadratum-braccas.com> wrote:

I have a table that has 39,000 rows in it, but a
pg_total_relation_size('schema.table') shows a size of 0

I did a vacuum and an analyze but no change

Thoughts?

Copy and paste the exact queries you are running and the results they are giving.

David J.

Re: pg_total_relation_size shows 0 byt table has thousands of rows

From

Javier Aragón Díaz

Date:

11 April, 22:30:26

try this

select current_database() as database,       pg_size_pretty(total_database_size) as total_database_size,       schema_name,       table_name,       pg_size_pretty(total_table_size) as total_table_size,       pg_size_pretty(table_size) as table_size,       pg_size_pretty(index_size) as index_size       from ( select table_name,                table_schema as schema_name,                pg_database_size(current_database()) as total_database_size,                pg_total_relation_size(table_name) as total_table_size,                pg_relation_size(table_name) as table_size,                pg_indexes_size(table_name) as index_size                from information_schema.tables                where table_schema=current_schema() and table_name like 'NAME_TABLE_HERE'                order by total_table_size            ) as sizes;

El jue, 11 abr 2024 a las 21:22, Sbob (<sbob@quadratum-braccas.com>) escribió:

This is on PG version 14

sales_info=# select count(*) from sales_info.reject_errors;
count
--------
688422
(1 row)

sales_info=# select pg_total_relation_size ('sales_info.reject_errors');
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# analyze sales_info.reject_errors;
ANALYZE
sales_info=# vacuum sales_info.reject_errors;
VACUUM
sales_info=# select pg_total_relation_size ('sales_info.reject_errors');
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# select pg_total_relation_size ('sales_info.reject_errors'::regclass);
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# select oid from pg_class where relname = 'reject_errors';
oid
-------
39379
(1 row)

sales_info=# select pg_total_relation_size (39379);
pg_total_relation_size
------------------------
                      0
(1 row)

On 4/11/24 12:30, David G. Johnston wrote:
On Thu, Apr 11, 2024 at 11:11 AM Sbob <sbob@quadratum-braccas.com> wrote:

I have a table that has 39,000 rows in it, but a
pg_total_relation_size('schema.table') shows a size of 0

I did a vacuum and an analyze but no change

Thoughts?

Copy and paste the exact queries you are running and the results they are giving.

David J.

Re: pg_total_relation_size shows 0 byt table has thousands of rows

From

Sbob

Date:

11 April, 22:45:02

On 4/11/24 13:30, Javier Aragón Díaz wrote:

try this
select current_database() as database,       pg_size_pretty(total_database_size) as total_database_size,       schema_name,       table_name,       pg_size_pretty(total_table_size) as total_table_size,       pg_size_pretty(table_size) as table_size,       pg_size_pretty(index_size) as index_size       from ( select table_name,                table_schema as schema_name,                pg_database_size(current_database()) as total_database_size,                pg_total_relation_size(table_name) as total_table_size,                pg_relation_size(table_name) as table_size,                pg_indexes_size(table_name) as index_size                from information_schema.tables                where table_schema=current_schema() and table_name like 'NAME_TABLE_HERE'                order by total_table_size            ) as sizes;
El jue, 11 abr 2024 a las 21:22, Sbob (<sbob@quadratum-braccas.com>) escribió:
This is on PG version 14

sales_info=# select count(*) from sales_info.reject_errors;
count
--------
688422
(1 row)

sales_info=# select pg_total_relation_size ('sales_info.reject_errors');
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# analyze sales_info.reject_errors;
ANALYZE
sales_info=# vacuum sales_info.reject_errors;
VACUUM
sales_info=# select pg_total_relation_size ('sales_info.reject_errors');
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# select pg_total_relation_size ('sales_info.reject_errors'::regclass);
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# select oid from pg_class where relname = 'reject_errors';
oid
-------
39379
(1 row)

sales_info=# select pg_total_relation_size (39379);
pg_total_relation_size
------------------------
                      0
(1 row)

On 4/11/24 12:30, David G. Johnston wrote:
On Thu, Apr 11, 2024 at 11:11 AM Sbob <sbob@quadratum-braccas.com> wrote:

I have a table that has 39,000 rows in it, but a
pg_total_relation_size('schema.table') shows a size of 0

I did a vacuum and an analyze but no change

Thoughts?

Copy and paste the exact queries you are running and the results they are giving.

David J.

Re: pg_total_relation_size shows 0 byt table has thousands of rows (SOLVED)

From

Sbob

Date:

11 April, 22:46:25

I figured it out, this is a partitioned table

On 4/11/24 13:45, Sbob wrote:

database | total_database_size | schema_name | table_name | total_table_size | table_size | index_size
-----------------------+-----------------------------+-------------------------+-------------------+----------------------+--------------+------------
sales | 28 GB | sales_info | reject_errors | 0 bytes | 0 bytes | 0 bytes
(1 row)

On 4/11/24 13:30, Javier Aragón Díaz wrote:
try this
select current_database() as database,       pg_size_pretty(total_database_size) as total_database_size,       schema_name,       table_name,       pg_size_pretty(total_table_size) as total_table_size,       pg_size_pretty(table_size) as table_size,       pg_size_pretty(index_size) as index_size       from ( select table_name,                table_schema as schema_name,                pg_database_size(current_database()) as total_database_size,                pg_total_relation_size(table_name) as total_table_size,                pg_relation_size(table_name) as table_size,                pg_indexes_size(table_name) as index_size                from information_schema.tables                where table_schema=current_schema() and table_name like 'NAME_TABLE_HERE'                order by total_table_size            ) as sizes;
El jue, 11 abr 2024 a las 21:22, Sbob (<sbob@quadratum-braccas.com>) escribió:
This is on PG version 14

sales_info=# select count(*) from sales_info.reject_errors;
count
--------
688422
(1 row)

sales_info=# select pg_total_relation_size ('sales_info.reject_errors');
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# analyze sales_info.reject_errors;
ANALYZE
sales_info=# vacuum sales_info.reject_errors;
VACUUM
sales_info=# select pg_total_relation_size ('sales_info.reject_errors');
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# select pg_total_relation_size ('sales_info.reject_errors'::regclass);
pg_total_relation_size
------------------------
                      0
(1 row)

sales_info=# select oid from pg_class where relname = 'reject_errors';
oid
-------
39379
(1 row)

sales_info=# select pg_total_relation_size (39379);
pg_total_relation_size
------------------------
                      0
(1 row)

On 4/11/24 12:30, David G. Johnston wrote:
On Thu, Apr 11, 2024 at 11:11 AM Sbob <sbob@quadratum-braccas.com> wrote:

I have a table that has 39,000 rows in it, but a
pg_total_relation_size('schema.table') shows a size of 0

I did a vacuum and an analyze but no change

Thoughts?

Copy and paste the exact queries you are running and the results they are giving.

David J.