pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Sbob <sbob@quadratum-braccas.com>
To: Javier Aragón Díaz <javier.aragon.diaz@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: pg_total_relation_size shows 0 byt table has thousands of rows (SOLVED)
Date: Thu, 11 Apr 2024 13:46:25 -0600
Message-ID: <581aea8f-6530-466b-bda3-3e48637a40d4@quadratum-braccas.com> (raw)
In-Reply-To: <983af359-f58d-46cb-b296-59b75e5db75e@quadratum-braccas.com>
References: <a13530b7-2bf6-4eb4-8cb5-395ce485b006@quadratum-braccas.com>
	<CAKFQuwZ47g5Dg79kB9fF-Z6P-0R5kOtf81u_GiKD7wtVRX6GfA@mail.gmail.com>
	<f6900fcb-9b9c-4d35-8dee-3bccb5d5e64d@quadratum-braccas.com>
	<CALpUZwXTXFp8YknpRBBf1K1dy3vzSNKRi5P74CYwx_HYNZvSvA@mail.gmail.com>
	<983af359-f58d-46cb-b296-59b75e5db75e@quadratum-braccas.com>

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.
>>>

view thread (6+ messages)

Message-ID: <581aea8f-6530-466b-bda3-3e48637a40d4@quadratum-braccas.com>
Permalink:  ../581aea8f-6530-466b-bda3-3e48637a40d4@quadratum-braccas.com/
Also on:    postgresql.org/message-id/581aea8f-6530-466b-bda3-3e48637a40d4@quadratum-braccas.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: sbob@quadratum-braccas.com, javier.aragon.diaz@gmail.com, pgsql-admin@lists.postgresql.org
  Subject: Re: pg_total_relation_size shows 0 byt table has thousands of rows (SOLVED)
  In-Reply-To: <581aea8f-6530-466b-bda3-3e48637a40d4@quadratum-braccas.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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