Postgresql the same table to find the intersection of different conditions?

the database is postgresql. Table table shows the conditional data of several intervals of A _ Magi B ~ C ~ D,
1, finding the intersection of A _ mai B?
2. Find the intersection of Amurc, which is not in the B interval.
3. Find the intersection of Amurd and not in Bmurc?

currently using query statement: select a.name from (select distinct name from table where A) as a join (select distinct name from table where B) as b on a.name = b.name

the amount of data is about 6000W, which is currently queried by subquery and then Join. But the query time is long, is there any other better solution?

= actual business =
an order table that queries users who have orders 30 or 60 days before a certain date (a cycle).
demand 1, with orders in 30-60 days (previous cycle)
demand 2, no orders in 30-60 days (previous cycle), orders in 60-90 (first 2 cycles)

Postgresql sql-statement php

Aug.05,2021

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)

Previous: Have done the advance of js audio compression: can javascript compress the recorded binary audio? At present, it is hoped that it will be in AAC format and transferred to the background to reduce traffic consumption.

Next: Ajax request ashx data is empty