sql - 具有大量表连接的查询的 SQL 性能调优

Question

我有一些 SQL，我一直在努力让它尽可能快。但是，因为我们有大量数据的表，SQL 需要很长时间才能运行。

更多信息：

x86_64-redhat-linux-gnu 上的 PostgreSQL 9.2.24，由 gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28) 编译，64 位

问题

有什么指针可以用来加快速度吗？

SELECT DISTINCT
    trip.travel_order_num as order_num,
    min(contact.fname || ' ' || contact.sname) as traveller,
    min(agentcontact.fname || ' ' || agentcontact.sname) as agent,
    min(employee.staff_code) as staff_code,
    trip.projectnumber,
    min(lob.name) as cost_centre,

    trip.start_date,
    trip.end_date,
    DATE(trip.end_date) - DATE(trip.start_date) + 1 as number_of_days,
    DATE(trip.start_date) - DATE(reqcreationdate) as days_requested_in_advance,
    trip.created as tripcreationdate,
    trip.recoverablecost,
    tripstatus.name AS status,
    trip.company_id,
    trip.eventtypes,
    trip.trip_reason,
    min(to_char(timetofirstbooking.earliestbookingdate - trip.reqcreationdate, 'FMDD HH24:MI:SS')) as dates_taken_to_book,
    min(COALESCE(paidaccommodation.paid_accomtot, 0.00)) AS paid_accomtot,
    min(COALESCE(booked_accommodation_total, 0.00)) as booked_accommodation_total,
    min(COALESCE(null, 0.00)) AS paid_cartot,
    min(COALESCE(bookedcarhire.booked_car_hire_total, 0.00)) as booked_car_hire_total,
    min(COALESCE(paidflightfare.allstatuses_flighttot, 0.00)) AS allstatuses_flighttot,
    min(COALESCE(paidflightfare.savings, 0.00)) AS flightsavings_tot,
    min(COALESCE(paidflightfare.publishedfare, 0.00)) AS flightpublishedfare_tot,
    min(COALESCE(tripservicefeepaid.feeamount, 0.00)) as servicefee,
    min(COALESCE(tripchangeservicefeepaid.feeamount, 0.00)) as changeservicefee,
    min(COALESCE(otheritemspaid.amount, 0.00)) as otherfee,
    CASE
        WHEN trip.requester_personid IS NOT NULL THEN min(requestercontact.fname || ' ' || requestercontact.sname)
        WHEN trip.requester_personid IS NULL THEN min(agentcontact.fname || ' ' || agentcontact.sname)
    END as requester,
    CASE
        WHEN min(tas.lastname) IS NOT NULL AND min(tas.staffcode) IS NOT NULL THEN string_agg(DISTINCT(CONCAT(tas.firstname,' ',tas.lastname, ' (',tas.staffcode,')')),', ')
        WHEN min(tas.lastname) IS NOT NULL THEN string_agg(DISTINCT(CONCAT(tas.firstname,' ',tas.lastname)),', ')
        ELSE ''
    END as approver,
    min(bob.approvalRequestedDate) as approvalrequesteddate,
    max(bob.apprConcDate) as approvalconclusiondate,
    extract(EPOCH from min(timetoapprove))/3600 as timetoapprove


FROM TRIP
    LEFT JOIN TRIPTRAVELLERMAP ON triptravellermap.tripid = trip.trip_id
    LEFT JOIN FLIGHTFARE ON flightfare.triptravid = triptravellermap.triptravid
    LEFT JOIN TRAVELAGENT ON travelagent.agentid = trip.agent_id
    LEFT JOIN CONTACT as agentcontact ON agentcontact.member_id = travelagent.memberid
    LEFT JOIN ITEM ON item.itemid = trip.itemid
    LEFT JOIN LOB ON lob.lobid = item.lobid
    LEFT JOIN TRIPSTATUS ON tripstatus.statusid = trip.status
    LEFT JOIN PERSON_CONTACT ON person_contact.person_personid = triptravellermap.personid
    LEFT JOIN CONTACT ON contact.contact_id = person_contact.personaldetails_contact_id
    LEFT JOIN PERSON_EMPLOYEE ON person_employee.person_personid = triptravellermap.personid
    LEFT JOIN EMPLOYEE ON employee.employee_id = person_employee.employmentdetails_employee_id
    LEFT JOIN TRIPREQUIREMENTSMAPPING ON triprequirementsmapping.tripid = trip.trip_id
    LEFT JOIN TRIPREQUIREMENTS ON triprequirements.uri = triprequirementsmapping.corporatetravelrequesturi
    LEFT JOIN PERSON_CONTACT AS requesterperson ON requesterperson.person_personid = trip.requester_personid
    LEFT JOIN CONTACT AS requestercontact ON requestercontact.contact_id = requesterperson.personaldetails_contact_id
    LEFT JOIN trip_approver_snapshot tas ON trip.trip_id=tas.trip_id
    /* PAID ACCOMMODATION */
    LEFT JOIN (SELECT trip.trip_id, SUM(lineitems.amount) as paid_accomtot
        FROM TRIP
            INNER JOIN RESERVATION ON reservation.trip_id = trip.trip_id
            INNER JOIN ACCOMMODATION ON accommodation.reservationid = reservation.reservationid
            INNER JOIN LINEITEMS ON lineitems.invoiceid = accommodation.invoiceid
        WHERE accommodation.created >= '2021-05-01' and accommodation.created <= ('2021-05-23')
            AND accommodation.resstatus <> 2
        GROUP BY trip.trip_id) AS paidaccommodation ON paidaccommodation.trip_id = trip.trip_id

    /* BOOKED ACCOMMODATION */
    LEFT JOIN (SELECT trip.trip_id, SUM(accommodation.totalaftertax) as booked_accommodation_total
        FROM TRIP
            INNER JOIN RESERVATION ON reservation.trip_id = trip.trip_id
            INNER JOIN ACCOMMODATION ON accommodation.reservationid = reservation.reservationid
        WHERE accommodation.created >= '2021-05-01' AND accommodation.created <= '2021-05-23'
            AND accommodation.invoiceid IS NULL AND accommodation.resstatus <> 2
        GROUP BY trip.trip_id) AS bookedaccommodation ON bookedaccommodation.trip_id = trip.trip_id

    /* PAID CARRENTAL */
    LEFT JOIN (SELECT trip.trip_id, SUM(carrental.rental_price) as booked_car_hire_total
        FROM TRIP
            INNER JOIN RESERVATION ON reservation.trip_id = trip.trip_id
            INNER JOIN CARRENTAL ON carrental.reservationid = reservation.reservationid
        WHERE carrental.created >= '2021-05-01' AND carrental.created <= '2021-05-23' AND carrental.status <> 2
        GROUP BY trip.trip_id) AS bookedcarhire ON bookedcarhire.trip_id = trip.trip_id

    /* PAID FLIGHT DETAILS */
    LEFT JOIN ( SELECT trip.trip_id, SUM(flightfare.totalfare) as allstatuses_flighttot, SUM(flightfare.savings) as savings, SUM(flightfare.publishedfare) as publishedfare, MIN(flightfare.created) as earliestbooking
        FROM TRIP
            INNER JOIN TRIPTRAVELLERMAP ON triptravellermap.tripid = trip.trip_id
            INNER JOIN FLIGHTFARE ON flightfare.triptravid = triptravellermap.triptravid
        WHERE flightfare.created >= '2021-05-01' AND flightfare.created <= '2021-05-23'
            AND (flightfare.status <> 2 OR (flightfare.status = 2 AND flightfare.ticketed = 1))
            AND NOT(flightfare.ticketed = 0 AND flightfare.tickettimelimit < CURRENT_TIMESTAMP)
        GROUP BY trip.trip_id ) AS paidflightfare ON paidflightfare.trip_id = trip.trip_id

    /* TIME TO FIRST BOOKING */
    LEFT JOIN (SELECT cc.trip_id, MIN(earliest_booked_date) as earliestbookingdate
            FROM(
                (SELECT trip.trip_id, MIN(flightfare.created) as earliest_booked_date
                    FROM TRIP
                        INNER JOIN TRIPTRAVELLERMAP ON triptravellermap.tripid = trip.trip_id
                        INNER JOIN FLIGHTFARE ON flightfare.triptravid = triptravellermap.triptravid
                    GROUP BY trip.trip_id )

                UNION

                (SELECT trip.trip_id, MIN(carrental.created) as earliest_booked_date
                    FROM TRIP
                        INNER JOIN RESERVATION ON reservation.trip_id = trip.trip_id
                        INNER JOIN CARRENTAL ON carrental.reservationid = reservation.reservationid
                    GROUP BY trip.trip_id)

                UNION

                (SELECT trip.trip_id, MIN(accommodation.created) as earliest_booked_date
                    FROM TRIP
                        INNER JOIN RESERVATION ON reservation.trip_id = trip.trip_id
                        INNER JOIN ACCOMMODATION ON accommodation.reservationid = reservation.reservationid
                    GROUP BY trip.trip_id)
            ) as cc GROUP BY cc.trip_id) AS timetofirstbooking ON timetofirstbooking.trip_id = trip.trip_id

    /* TRIP FEES */
    LEFT JOIN (SELECT trip.trip_id, SUM(tripfee.feeamount + tripfee.feeamountvat) as feeamount
        FROM TRIP
            INNER JOIN TRIPFEE ON tripfee.tripid = trip.trip_id
        GROUP BY trip.trip_id) AS tripservicefeepaid ON tripservicefeepaid.trip_id = trip.trip_id

    /* TRIP CHANGE FEES */
    LEFT JOIN (SELECT trip.trip_id, SUM(tripfee.feeamount + tripfee.feeamountvat) as feeamount
        FROM TRIP
            INNER JOIN TRIPFEE ON tripfee.tripid = trip.trip_id
        WHERE tripfee.tripfeetype = 9
        GROUP BY trip.trip_id) AS tripchangeservicefeepaid ON tripchangeservicefeepaid.trip_id = trip.trip_id

    /* OTHER ITEMS PAID */
    LEFT JOIN (SELECT trip.trip_id, SUM(otheritems.amount + otheritems.vat) as amount
        FROM TRIP
            INNER JOIN OTHERITEMS ON otheritems.tripid = trip.trip_id
        GROUP BY trip.trip_id) AS otheritemspaid ON otheritemspaid.trip_id = trip.trip_id

    LEFT JOIN (
        SELECT requested,max(conclusiondate) as apprConcDate , max(requestedDate) as approvalRequestedDate, max(conclusiondate)- max(requestedDate) as timetoapprove
        FROM approvalsubmission
        left join triprequirements on approvalsubmission.requested=triprequirements.uri
        where approvalsubmission.conclusion='APPROVED'
        group by requested) as bob on bob.requested = triprequirements.uri

WHERE
    trip.trip_id IN (SELECT DISTINCT(trip_id) FROM reservation WHERE reservation.created >= ('2021-05-01') AND reservation.created <= ('2021-05-23'))
    --AND LOWER(employee.staff_code) LIKE LOWER('$P!{staffCode')

    AND trip.status <> 2
    AND trip.company_id = 6632
    --AND $P!{costCentreCompanySwitcher}

group by trip.trip_id, tripstatus.name
ORDER BY min(lob.name)

标签： sqlpostgresqlquery-optimizationpostgresql-performance

Answer 1

固定的：

status从 group by 子句中删除：

max(tripstatus.name) AS status,
 ...
group by trip.trip_id