How can I (quickly) test if a postgres partition has any rows in it?
I have a partitioned postgres table 'TABLE_A', partitioned by date-range. The name of each individual partition indicates the date-range i.e. TABLE_A_20220101 (1st Jan this year) TABLE_A_20220102 (2nd Jan 2022)
The table includes many years of data, so it includes several thousand individual partitions, each partition contains many millions of rows.
Is there a quick way of testing if a partition has any data in it? There are several solutions I've found, but they all involve count(*)
and all take ages.
Please note - I'm NOT trying to accurately determine the row-count, just determine if each partition has any rows in it.
CodePudding user response:
You can use an exists
condition:
select exists (select * from partition_name limit 1)
That will return true
if partition_name
contains at least one row