pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_total_relation_size shows 0 byt table has thousands of rows
6+ messages / 3 participants
[nested] [flat]
* pg_total_relation_size shows 0 byt table has thousands of rows
@ 2024-04-11 18:10 Sbob <sbob@quadratum-braccas.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sbob @ 2024-04-11 18:10 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_total_relation_size shows 0 byt table has thousands of rows
@ 2024-04-11 18:30 David G. Johnston <david.g.johnston@gmail.com>
parent: Sbob <sbob@quadratum-braccas.com>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2024-04-11 18:30 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_total_relation_size shows 0 byt table has thousands of rows
@ 2024-04-11 19:22 Sbob <sbob@quadratum-braccas.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sbob @ 2024-04-11 19:22 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_total_relation_size shows 0 byt table has thousands of rows
@ 2024-04-11 19:30 Javier Aragón Díaz <javier.aragon.diaz@gmail.com>
parent: Sbob <sbob@quadratum-braccas.com>
0 siblings, 1 reply; 6+ messages in thread
From: Javier Aragón Díaz @ 2024-04-11 19:30 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; pgsql-admin@lists.postgresql.org
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.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_total_relation_size shows 0 byt table has thousands of rows
@ 2024-04-11 19:45 Sbob <sbob@quadratum-braccas.com>
parent: Javier Aragón Díaz <javier.aragon.diaz@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sbob @ 2024-04-11 19:45 UTC (permalink / raw)
To: Javier Aragón Díaz <javier.aragon.diaz@gmail.com>; pgsql-admin@lists.postgresql.org
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.
>>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_total_relation_size shows 0 byt table has thousands of rows (SOLVED)
@ 2024-04-11 19:46 Sbob <sbob@quadratum-braccas.com>
parent: Sbob <sbob@quadratum-braccas.com>
0 siblings, 0 replies; 6+ messages in thread
From: Sbob @ 2024-04-11 19:46 UTC (permalink / raw)
To: Javier Aragón Díaz <javier.aragon.diaz@gmail.com>; pgsql-admin@lists.postgresql.org
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.
>>>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-04-11 19:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-11 18:10 pg_total_relation_size shows 0 byt table has thousands of rows Sbob <sbob@quadratum-braccas.com>
2024-04-11 18:30 ` David G. Johnston <david.g.johnston@gmail.com>
2024-04-11 19:22 ` Sbob <sbob@quadratum-braccas.com>
2024-04-11 19:30 ` Javier Aragón Díaz <javier.aragon.diaz@gmail.com>
2024-04-11 19:45 ` Sbob <sbob@quadratum-braccas.com>
2024-04-11 19:46 ` Re: pg_total_relation_size shows 0 byt table has thousands of rows (SOLVED) Sbob <sbob@quadratum-braccas.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox