I am trying to optimize a query which has been destroying my DB.


If you have any insights into how to make this better please let me know.

We are using RDS/Postgres 11.9

explain analyze SELECT "src_rowdifference"."key",
FROM "src_rowdifference"
INNER JOIN "src_containertype" ON ("src_rowdifference"."container_type_id" = "src_containertype"."key")
WHERE ("src_rowdifference"."container_type_id" IN
         (SELECT U0."key"
          FROM "src_containertype" U0
          INNER JOIN "notification_tablenotification_container_types" U1 ON (U0."key" = U1."containertype_id")
          WHERE U1."tablenotification_id" = 'test@test.com')
       AND "src_rowdifference"."new_last_update" >= '2020-01-15T03:11:06.291947+00:00'::timestamptz
       AND "src_rowdifference"."port_id" IN
         (SELECT U0."key"
          FROM "src_port" U0
          INNER JOIN "notification_tablenotification_ports" U1 ON (U0."key" = U1."port_id")
          WHERE U1."tablenotification_id" = 'test@test.com')
       AND "src_rowdifference"."shipping_line_id" IN
         (SELECT U0."key"
          FROM "src_shippingline" U0
          INNER JOIN "notification_tablenotification_shipping_lines" U1 ON (U0."key" = U1."shippingline_id")
          WHERE U1."tablenotification_id" = 'test@test.com')
       AND "src_rowdifference"."prev_last_update" IS NOT NULL
       AND NOT ("src_rowdifference"."prev_availability_id" = 'na'
                AND "src_rowdifference"."prev_availability_id" IS NOT NULL)
       AND NOT ("src_rowdifference"."key" IN
                  (SELECT V1."rowdifference_id"
                   FROM "notification_tablenotificationtrigger_row_differences" V1
                   WHERE V1."tablenotificationtrigger_id" IN
                       (SELECT U0."id"
                        FROM "notification_tablenotificationtrigger" U0
                        WHERE U0."notification_id" = 'test@test.com'))));
  • 1
    I would start by taking a step back and asking yourself how you would tackle this query - what are the driving filters that you would start with? It looks like this is checking for where rows have been updated since a time (with that time being last year, will it always be that far back?) and then doing some lookups to see if it’s a row you care about. Do you expect those filters to drive the query? If so then those are the filters you want to concentrate on indexing. Oct 20 '21 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.