sql - Postgres - 查询需要很长时间

Question

我有一个查询需要很长时间。反正有没有更好和优化的方式写它：

select 1, my_text from (
    select distinct a.my_text||'_'||b.my_text my_text from (
        select r_id, my_text 
        from tmp_v 
        where r_id in (
            select o_id 
            from tmp_recid
        ) and v_id in (
            select v_id 
            from o_v 
            where v_id in (
                select o_id from tmp_record_vaid 
                union 
                select o_id from tmp_vue_vaid
            ) and va_nm = 'My V'
        )
    ) a,
    (
        select r_id, my_text 
        from tmp_v 
        where r_id in (
            select o_id 
            from tmp_recid
        ) and v_id in (
            select v_id 
            from o_v 
            where v_id in (
                select o_id from tmp_record_vaid 
                union 
                select o_id from tmp_vue_vaid
            ) and va_nm = 'My V 2'
        )
    ) b
where a.r_id = b.r_id
except 
    select e_nm 
    from myp_ent_id 
    where p_m_id = 1 and entity_id in (
        select entity_id 
        from o_e_t 
        where p_m_id = 1 and tag = 'Ample' and tag_category = 'My Type'
    )
) a;

标签： sqlpostgresql

Answer 1

请尝试以下查询，但“查询除外”除外：

select 1, distinct(CONCAT(TV1.my_text, '_'))
from tmp_v TV1
left outer join tmp_recid TR1 on TR1.o_id = TV1.r_id
left outer join o_v OV1 on OV1.v_id = TV1.v_id and OV1.va_nm in ('My V', 'My V 2')

left outer join tmp_record_vaid TRV1 on OV1.v_id = TRV1.o_id
left outer join tmp_vue_vaid TVV1 on OV1.v_id = TVV1.o_id

where TR1.o_id is not null and (TRV1.o_id is not null OR TVV1.o_id is not null)
group by TV1.r_id