Speed ​​up SELECT query with LEFT JOIN on PostgreSQL?

Question

PostgreSQL

Hello! I have a problem with the speed of issuing requests on the site.
You can check this link: WEBSITE
The request takes a long time due to numerous calls via LEFT JOIN to the link table.
The base is crooked and cannot be remade. There is also a parser working.
Loading well, it takes a very long time, especially if you apply filtering.

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])));

Here is the code for the request. Can you help speed it up somehow?

Answer 1

I can help with advice: Run it with explain analyze, analyze the execution plan and proceed from the results of the analysis in further actions.