Wrong computation in Total duration time and weird AVG display according to order type

[jeanloesch]

Good morning,

My Postgres instance is running with Postgres 15.4 (under CentOS 7.9) and is a worker node of a Citus v12 cluster. In file AVG_Descending_order.JPG, the highest number of duration is displayed first so it is ok. In file AVG_Ascending_order.JPG, I expected the smallest number of duration on the first line but the highest number is displayed. Aside from that, the first line shows a wrong computation since 4 x 941ms = 941 ms.

Could you please help ?

Regards.

[pgiraud]

This indeed seems to be wrong. I'm also very surprised that total shows some many results with the exact same value.

Do you know how many pages there is? There seem to be a lot of rows in the table.

What surprises me also is the "NaN undefined".

Can you please try to filter the data for example with "102014"?

[jeanloesch]

Hi Pierre,

The Citus cluster is made of 6 nodes but only 5 are workers and stores data. The fifth is the coordinator.

There are only 2 tables in the database. bigbi=# select * from citus_tables; table_name | citus_table_type | distribution_column | colocation_id | table_size | shard_count | table_owner | access_method ------------+------------------+---------------------+---------------+------------+-------------+-------------+--------------- events | distributed | no_lt | 1 | 714 GB | 32 | bbe_adm | heap parcels | distributed | parcel_id | 1 | 187 GB | 32 | bbe_adm | heap (2 lignes)

########## Table events ########## bigbi=# select count(*) from events; count

600045605 (1 ligne)

bigbi=# SELECT pg_size_pretty(citus_total_relation_size('events')); pg_size_pretty

714 GB (1 ligne)

bigbi=# SELECT pg_size_pretty(citus_relation_size('events')); pg_size_pretty

448 GB (1 ligne)

bigbi=# SELECT citus_relation_size('events')/8 as "Nb pages"; Nb pages

60124208128 (1 ligne)

The table events is distributed over 32 shards. So each shard stores about 600045605/32=18751425 lines.

########## Table parcels ########## bigbi=# select count(*) from parcels; count

125380384

bigbi=# SELECT pg_size_pretty(citus_total_relation_size('parcels')); pg_size_pretty

187 GB (1 ligne)

bigbi=# SELECT pg_size_pretty(citus_relation_size('parcels')); pg_size_pretty

150 GB (1 ligne)

bigbi=# SELECT citus_relation_size('parcels')/8 as "Nb pages"; Nb pages

20096626688 (1 ligne)

The table parcels is distributed over 32 shards. So each shard stores about 125380384/32=3918137 lines.

What do you mean by to #filter the data for example with "102014"# ?