slow sql query for big items

Hello,

I have a common table for telemetry data. the stru is:

Column | Type | Collation | Nullable |
Default

------------------------+--------------------------+-----------+----------+---------

record_time | timestamp with time zone | | not null |

station_name | text | | |

feeder_gis_id | text | | |

switch_name | text | | |

switch_oid | text | | not null |

switch_gis_id | text | | |

switch_status | integer | | |

switch_status_quality | integer | | |

active_power | numeric(18,6) | | |

active_power_quality | integer | | |

reactive_power | numeric(18,6) | | |

reactive_power_quality | integer | | |

current_a | numeric(18,6) | | |

current_a_quality | integer | | |

current_b | numeric(18,6) | | |

current_b_quality | integer | | |

current_c | numeric(18,6) | | |

current_c_quality | integer | | |

voltage_uab | numeric(18,6) | | |

voltage_uab_quality | integer | | |

voltage_ubc | numeric(18,6) | | |

voltage_ubc_quality | integer | | |

voltage_uca | numeric(18,6) | | |

voltage_uca_quality | integer | | |

created_at | timestamp with time zone | | |
now()

Indexes:

"dms_data_gzdy_pkey" PRIMARY KEY, btree (record_time, switch_oid)

"dms_data_gzdy_record_time_idx" btree (record_time DESC)

"idx_dms_feeder_gis_id" btree (feeder_gis_id, record_time)

"idx_dms_station_name" btree (station_name, record_time)

"idx_dms_switch_oid" btree (switch_oid, record_time)

Data records are growing by about *10 million* every day, reaching *300
million* per month. In this case, even a simple COUNT(*) query becomes
extremely slow, taking about 7-8 minutes to finish.

I am running PostgreSQL 14 on Ubuntu 22.04 with a 24GB shared buffer.

And, though in our test env we have timescaledb enabled:

Triggers:

ts_insert_blocker BEFORE INSERT ON dms_data_gzdy FOR EACH ROW EXECUTE
FUNCTION _timescaledb_functions.insert_blocker()

Number of child tables: 9 (Use \d+ to list them.)

But in production env there is no timescaledb which can't be installed as
well.

Can you help me?

Thanks.