Здравствуйте! У меня проблема со скоростью выдачи запросов на сайте.
Можете проверить по этой ссылке:
САЙТ
Очень долго идёт запрос из за многочисленных обращении через LEFT JOIN в таблицу связей.
База сделана криво и переделать не удастся. Там ещё и парсер работает.
Загрузка ну очень долго идёт особенно если применять фильтрации.
SELECT DISTINCT ON (lots.id) lots.id AS lot_id,
lots.cadastreid AS lot_cadastreid,
lots.description AS lot_description,
lots.startprice AS lot_startprice,
lots.stepprice AS lot_stepprice,
lots.advance AS lot_advance,
lots.auctionstepunit AS lot_auctionstepunit,
lots.advancestepunit AS lot_advancestepunit,
lots.pricereduction AS lot_pricereduction,
torgy.id AS torgy_id,
dctgetvalue(torgy.state) AS torgy_status,
torgy.timepublication AS lot_timepublication,
torgy.description AS torgy_description,
torgy.tradetype AS torgy_tradetype,
torgy.pricetype AS torgy_pricetype,
torgy.timebegin AS lot_timebegin,
torgy.timeend AS lot_timeend,
torgy.rules AS torgy_rules,
trade.tradename AS lot_tradename,
trade.tradesite AS lot_tradesite,
trade.idtradeplace AS lot_idtradeplace,
CASE
WHEN (person.id IS NOT NULL) THEN ((((person.lname || ' '::text) || person.fname) || ' '::text) || person.mname)
ELSE company.shortname
END AS bnkr__name,
CASE
WHEN (person.id IS NOT NULL) THEN person.inn
ELSE company.inn
END AS bnkr__inn,
CASE
WHEN (person.id IS NOT NULL) THEN person.address
ELSE company.legaladdress
END AS bnkr__address,
link_case.objid AS case_id,
cadastre.regionid AS lot_regionid,
cadastre.areaid AS lot_areaid,
(EXISTS ( SELECT "obj$images".id
FROM "obj$images"
WHERE ("obj$images".objid = lots.id))) AS lot_image,
lots.lotid AS lot_lotid,
link_bnkr.lnkobjid AS bnkr__id,
( SELECT count(wish.id) AS count
FROM site."wishList" wish
WHERE ((wish.type = 'bankrupt'::text) AND (wish."lotId" = lots.id))) AS wish_count
FROM ((((((((("obj$lots" lots
LEFT JOIN "obj$cadastre" cadastre ON ((lots.cadastreid = cadastre.id)))
LEFT JOIN "obj$auctions" torgy ON ((lots.auctionid = torgy.id)))
LEFT JOIN tradeplace trade ON ((torgy.idtradeplace = trade.idtradeplace)))
LEFT JOIN "obj$links" link_case ON (((link_case.objtype = 1044) AND (link_case.lnkobjtype = 1048) AND (link_case.lnkobjid = torgy.id))))
LEFT JOIN "obj$links" link_bnkr ON (((link_bnkr.objtype = 1044) AND (link_bnkr.lnkobjtype = 1049) AND (link_bnkr.objid = link_case.objid))))
LEFT JOIN "obj$links" link_bnkr_person ON (((link_bnkr_person.objtype = 1049) AND (link_bnkr_person.lnkobjtype = 1042) AND (link_bnkr_person.objid = link_bnkr.lnkobjid))))
LEFT JOIN "obj$persons" person ON ((person.id = link_bnkr_person.lnkobjid)))
LEFT JOIN "obj$links" link_bnkr_company ON (((link_bnkr_company.objtype = 1049) AND (link_bnkr_company.lnkobjtype = 1043) AND (link_bnkr_company.objid = link_bnkr.id))))
LEFT JOIN "obj$company" company ON ((company.id = link_bnkr_company.lnkobjid)))
WHERE ((torgy.timeend >= now()) AND (torgy.timepublication IS NOT NULL) AND (torgy.timebegin IS NOT NULL) AND (torgy.timeend IS NOT NULL) AND (torgy.timepublication <> ALL (ARRAY['0001-01-01 00:00:00'::timestamp without time zone, '0001-01-01 00:00:00 BC'::timestamp without time zone])) AND (torgy.timebegin <> ALL (ARRAY['0001-01-01 00:00:00'::timestamp without time zone, '0001-01-01 00:00:00 BC'::timestamp without time zone])) AND (torgy.timeend <> ALL (ARRAY['0001-01-01 00:00:00'::timestamp without time zone, '0001-01-01 00:00:00 BC'::timestamp without time zone])));
Вот такой код для запроса. Можете помочь как нибудь это ускорить?
Простой
Средний