Re: slow sql query for big items - Mailing list pgsql-general

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.