Блог

У меня есть подзапрос, который используется в нескольких условиях where в моем основном запросе. Из-за этого подзапрос выполняется несколько раз, чтобы получить один и тот же результат. есть ли способ сохранить и использовать результат подзапроса, чтобы он выполнялся только один раз.

Пример кода:

 from sqlalchemy.sql.schema import ForeignKey
from sqlalchemy import Column, Integer, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import select, union

Base = declarative_base()
class Table1(Base):
    __tablename__ = 'table1'

    id = Column(Integer, primary_key=True)
    uuid = Column(Text, unique=True, nullable=False)
    
class Table2(Base):
    __tablename__ = 'table2'

    id = Column(Integer, primary_key=True)
    uuid = Column(Text, unique=True, nullable=False)
    
class Table3(Base):
    __tablename__ = 'table3'

    id = Column(Integer, primary_key=True)
    uuid = Column(Text, unique=True, nullable=False)
    

class Table4(Base):
    __tablename__ = 'table4'

    id = Column(Integer, primary_key=True)
    type = Column(Text, nullable=False)


class Table5(Base):
    __tablename__ = 'table5'

    id = Column(Integer, primary_key=True)
    res_id = Column(Integer, ForeignKey('table4.id'), nullable=False)
    value = Column(Text, nullable=False)

class Table1Map(Base):
    __tablename__ = 'table1_map'

    id = Column(Integer, ForeignKey('table4.id'), primary_key=True, nullable=False)
    map_id = Column(Integer, ForeignKey('table1.id'), primary_key=True, unique=True, nullable=False)

class Table2Map(Base):
    __tablename__ = 'table2_map'

    id = Column(Integer, ForeignKey('table4.id'), primary_key=True, nullable=False)
    map_id = Column(Integer, ForeignKey('table2.id'), primary_key=True, unique=True, nullable=False)
    
class Table3Map(Base):
    __tablename__ = 'table3_map'

    id = Column(Integer, ForeignKey('table4.id'), primary_key=True, nullable=False)
    map_id = Column(Integer, ForeignKey('table3.id'), primary_key=True, unique=True, nullable=False)


sub_query = select([Table5.__table__.c.id]).where(Table5.__table__.c.value=='somevalue')
subquery_1 = select([Table1.__table__.c.uuid.label("map_id"), Table1Map.__table__.c.id.label("id")]).select_from(Table1.__table__.join(Table1Map.__table__, Table1Map.__table__.c.map_id==Table1.__table__.c.id)).where(Table1Map.__table__.c.id.in_(sub_query))

subquery_2 = select([Table2.__table__.c.uuid.label("map_id"), Table2Map.__table__.c.id.label("id")]).select_from(Table2.__table__.join(Table2Map.__table__, Table2Map.__table__.c.map_id==Table2.__table__.c.id)).where(Table2Map.__table__.c.id.in_(sub_query))

subquery_3 = select([Table3.__table__.c.uuid.label("map_id"), Table3Map.__table__.c.id.label("id")]).select_from(Table3.__table__.join(Table3Map.__table__, Table3Map.__table__.c.map_id==Table3.__table__.c.id)).where(Table3Map.__table__.c.id.in_(sub_query))

main_query = union(subquery_1, subquery_2, subquery_3)

print(main_query)
 

Это приведет к следующему запросу. Мне нужно избежать многократного выполнения этого подзапроса несколько раз.

 SELECT TABLE1.UUID AS MAP_ID,
    TABLE1_MAP.ID AS ID
FROM TABLE1
JOIN TABLE1_MAP ON TABLE1_MAP.MAP_ID = TABLE1.ID
WHERE TABLE1_MAP.ID IN
        (SELECT TABLE5.ID
            FROM TABLE5
            WHERE TABLE5.VALUE = 'some_value')
UNION
SELECT TABLE2.UUID AS MAP_ID,
    TABLE2_MAP.ID AS ID
FROM TABLE2
JOIN TABLE2_MAP ON TABLE2_MAP.MAP_ID = TABLE2.ID
WHERE TABLE2_MAP.ID IN
        (SELECT TABLE5.ID
            FROM TABLE5
            WHERE TABLE5.VALUE = 'some_value')
UNION
SELECT TABLE3.UUID AS MAP_ID,
    TABLE3_MAP.ID AS ID
FROM TABLE3
JOIN TABLE3_MAP ON TABLE3_MAP.MAP_ID = TABLE3.ID
WHERE TABLE3_MAP.ID IN
        (SELECT TABLE5.ID
            FROM TABLE5
            WHERE TABLE5.VALUE = 'some_value')
 

Почему? Вы запустили explain (analyze, buffers) достаточно, чтобы показать, что это действительно вызывает проблемы с производительностью. Вполне возможно, что повторные выполнения находят необходимое значение уже в памяти, поэтому не требуют дополнительного ввода-вывода. Однако для достижения этой цели в Postgres можно было бы выбрать значение из таблицы 5 в CTE: (извините, я не знаю вашего менеджера запутывания, Python-Sqlalchemy).

 with cte (id) as 
     (select id 
        from table5 t5
       where t5.value = 'some_value'
     )          
select t1.uuid as map_id 
       t1m.id as id
  from table1 t1
  join table1_map on t1m.id = t1.id
  where t1m.id in
        (select  id
            from cte
        )
select t2.uuid as map_id 
       t2m.id as id
  from table2 t2
  join table2_map on t2m.id = t2.id
  where t2m.id in
        (select  id
            from cte
        )  
select t3.uuid as map_id 
       t3m.id as id
  from table3 t3
  join table3_map on t3m.id = t3.id
  where t3m.id in
        (select  id
            from cte
        );  
 

Обратите внимание, что у вас все еще есть возможность повторить дополнительный выбор (просто ссылаясь на CTE). Если вы настаиваете на удалении любого повторения, вы, конечно, можете выполнить объединение в подзапросе, а затем отфильтровать идентификаторы.

 select uuid, id
  from (select t1.uuid 
              , t1.id    
           from table1 t1 
        union 
        select t2.uuid 
             , t2.id    
          from table2 t2
        union
        select t3.uuid 
             , t3.id    
          from table3 t3 
       )  tall 
where tall.id in 
      (select t5.id
         from table5 t5
        where t5.value = 'some_value'
      );
 

В любом случае запустите объяснение, чтобы увидеть, что на самом деле работает лучше всего в вашей среде. (Убедитесь, что у него есть объем производства. Т. Е. не выполняйте тесты для таблиц со 100 строками, если в вашем производстве 100 тыс. строк).

НЕ ПРОВЕРЕНО.

ПРИМЕЧАНИЕ: Иметь имя столбца-плохая идея uuid . Postgres поддерживает собственный тип данных uuid . Неправильный выбор имен приводит к путанице (разработчики не Postgres), а путаница приводит к ошибкам. Часто остается незамеченным до тех пор, пока это не станет критической производственной проблемой.