tell me about my optimization ideas
first use the partition table according to the period of time (of course, according to your business needs, which can save you time querying the subtable Filter). For example, the following example is the monthly partition table

CREATE SCHEMA IF NOT EXISTS log;

DROP TABLE IF EXISTS log.t_log CASCADE;
CREATE TABLE log.t_log (
    name character varying(30),
    time TIMESTAMP WITHOUT TIME ZONE,
    note TEXT
);


CREATE OR REPLACE FUNCTION log.partition_trigger() RETURNS TRIGGER AS $$
DECLARE
    data_text TEXT;
    insert_statement TEXT;
BEGIN
    SELECT TO_CHAR(NEW.time, 'YYYYMM') INTO data_text;
    insert_statement := 'INSERT INTO log.t_log_'||data_text||' VALUES($1.*)';
    EXECUTE insert_statement USING NEW;
    RETURN NULL;
    EXCEPTION
    WHEN UNDEFINED_TABLE
    THEN
        EXECUTE 'CREATE TABLE IF NOT EXISTS log.t_log_'||data_text
        ||'(CHECK (TO_CHAR(time,''YYYYMM'')='''||data_text||''')) INHERITS (log.t_log)';
        RAISE NOTICE 'CREATE NON-EXISTANT TABLE log.t_log_%', data_text;
        EXECUTE 'CREATE INDEX t_log_index_'||data_text||' ON log.t_log_'||data_text||'(time)';
        EXECUTE insert_statement USING NEW;
    RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;


CREATE TRIGGER insert_log_partition_trigger BEFORE INSERT ON log.t_log
FOR EACH ROW EXECUTE PROCEDURE log.partition_trigger();

then through the join query between child tables, if you cross multiple child tables, you can directly query the main table and conditional Filter

= actual business =

when you see your actual business, the optimization idea is changed to generate 30-60-day, 60-90-day materialized views at 0: 00 a day (assuming you don't add the past data, which is equivalent to checking two tables in advance and saving query time)