1

First time posting here but long time reader.

I'm fairly new in my role working on optimising sql queries. Most are generated from KNEX.js leading to some peculiar artifacts that might make sense in javascript but don't play well with SQL.

I'm trying to improve the query below. The goal of the query is to produce a table listing a merchant's vending machines and the total amounts and counts taken through different gateways (eg AMEX, VISA, EBT, COUPON, NULL[Cash]) for accounting periods. I've reproduced it fully although I've altered the gateway names to ABC / XYZ for example purposes.

Clearly there is plenty of potential for improvement but where I would specifically would like to ask for help is this:

The query has two lateral joins that are essentially identical. vend_total and vend_partial. The only distinction is in the timestamp-

and "vend"."timestamp" <= cash_accounting_period_ids.closed_at AS vend_total - or between cash_accounting_period_ids.created_at and cash_accounting_period_ids.closed_at AS vend_partial

To my mind that makes vend_partial just a subset of vend_total. I would like to rewrite the query so that it does not have the obvious duplication of filters and does not fetch data twice, but I cannot wrap my head around how. It seems to me I should be able to reference vend_total as the basis of vend_partial instead of duplicating it, or instead rewriting the whole filter aggregate "totals" as a cte and then reference that, but tbh the complexity of this query is beating me.

I'd really appreciate any suggestions people might have as to how to streamline this query, and more than that I'd love some recommendations for resources or strategies for query analysis and optimisation. I'm new to this and I enjoy the challenge but I work for a small startup and at this point I'm apparently the SQL expert so will take any advice I can get!

George.

with "cash_accounting_period_ids" as (select "cash_accounting_period".*
                                      from "cash_accounting_period"
                                      where "cash_accounting_period"."merchant_id" = ?1
                                        and "cash_accounting_period"."closed_at" is not null
                                      order by closed_at DESC NULLS FIRST, cash_accounting_period.created_at DESC
                                      limit ?2)
select "cash_accounting_period_ids".*,
       (select row_to_json(vm)
        from (select vending_machine.*, ST_AsGeoJSON(location) as location) as vm) AS "vendingMachine",
       row_to_json("verified_by".*)                                                AS "verifiedBy",
       row_to_json("closed_by".*)                                                  AS "closedBy",
       row_to_json("company".*)                                                    AS "client",
       row_to_json(vend_total.*)                                                   AS "vendTotal",
       row_to_json(vend_partial.*)                                                 AS "vendPartial",
       row_to_json(route.*)                                                        AS route
from "cash_accounting_period_ids"
         left join "vending_machine" on "vending_machine"."id" = "cash_accounting_period_ids"."vending_machine_id"
         left join "user" AS "verified_by" on "verified_by"."id" = "cash_accounting_period_ids"."verified_by"
         left join "user" AS "closed_by" on "closed_by"."id" = "cash_accounting_period_ids"."closed_by"
         left join "company" on "company"."id" = "cash_accounting_period_ids"."client_id"
         left join lateral (select "totals".*
                            from (select "vend"."vending_machine_id",
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'ABC')           AS abc_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'ABC'),
                                                  0)                                                   AS abc_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'XYZ')           AS xyz_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'XYZ'),
                                                  0)                                                   AS xyz_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name IS NULL)             AS money_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name IS NULL),
                                                  0)                                                   AS money_amount,
                                         COUNT(*)                                                      AS total_count,
                                         COALESCE(SUM(vend.amount), 0)                                 AS total_amount
                                  from "vend"
                                           left join lateral (select coalesce(array_agg(route_vending_machine.route_id), '{}') as id
                                                              from "route_vending_machine"
                                                              where route_vending_machine.vending_machine_id = vend.vending_machine_id) subquery
                                                     ON TRUE
                                  where "vend"."merchant_id" = ?1
                                    and "vend"."display" = true
                                    and "vend"."currency_id" = 'EUR'
                                    and ("succeeded" = true or "succeeded" is null)
                                    and cash_accounting_period_ids.vending_machine_id = vend.vending_machine_id
                                    and (((TRUE or TRUE or TRUE)) and not (("subquery"."id" && '{}' or 1 = 0 or 1 = 0)))
                                    and "vend"."timestamp" <= cash_accounting_period_ids.closed_at
                                  group by "vend"."vending_machine_id", "vending_machine"."name") as "totals"
                            limit 1) vend_total ON TRUE
         left join lateral (select "totals".*
                            from (select "vend"."vending_machine_id",
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'ABC')           AS abc_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'ABC'),
                                                  0)                                                   AS abc_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'XYZ')           AS xyz_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'XYZ'),
                                                  0)                                                   AS xyz_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name IS NULL)             AS money_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name IS NULL),
                                                  0)                                                   AS money_amount,
                                         COUNT(*)                                                      AS total_count,
                                         COALESCE(SUM(vend.amount), 0)                                 AS total_amount
                                  from "vend"
                                           left join lateral (select coalesce(array_agg(route_vending_machine.route_id), '{}') as id
                                                              from "route_vending_machine"
                                                              where route_vending_machine.vending_machine_id = vend.vending_machine_id) subquery
                                                     ON TRUE
                                  where "vend"."merchant_id" = ?1
                                    and "vend"."display" = true
                                    and "vend"."currency_id" = 'EUR'
                                    and ("succeeded" = true or "succeeded" is null)
                                    and cash_accounting_period_ids.vending_machine_id = vend.vending_machine_id
                                    and (((TRUE or TRUE or TRUE)) and not (("subquery"."id" && '{}' or 1 = 0 or 1 = 0)))
                                    and "vend"."timestamp" between cash_accounting_period_ids.created_at and cash_accounting_period_ids.closed_at
                                  group by "vend"."vending_machine_id", "vending_machine"."name") as "totals"
                            limit 1) vend_partial ON TRUE
         left join "route_vending_machine"
                   on "route_vending_machine"."vending_machine_id" = "cash_accounting_period_ids"."vending_machine_id"
         left join "route" on "route"."id" = "route_vending_machine"."route_id";

EXPLAIN (ANALYSE, BUFFERS) is too long to share here in post or comment so I dumped it here: https://justpaste.it/bolah

3
  • As a long time reader, you should know that EXPLAIN (ANALYZE, BUFFERS) is pretty much mandatory for performance questions. If the duplicate subquery is not the performance bottleneck, fixing it won't matter.
    – jjanes
    Commented Mar 10, 2023 at 15:03
  • EXPLAIN ANALYSE: justpaste.it/bolah
    – George B
    Commented Mar 10, 2023 at 16:18
  • Don't the two lateral join result in a cross product that returns way too many rows than you'd need?
    – bobflux
    Commented Mar 11, 2023 at 11:16

1 Answer 1

0

If they were both slow, it could be that combining them reduce the time. But one of the lateral joins is essentially free, and it is hard to see how combining them would make the slow one any faster as it would still need to do its own slow work, plus more.

It looks to me like the problem is just that you are processing a lot of rows (counting them up with different filters) and that this just takes a bit of time. That time is spread out through many nodes on that branch of the plan tree, so there is probably not much you can do except without restructuring the query fundamentally.

Maybe you could make materialized views (or something) which aggregates up the old partitions so that you can then query those aggregates rather than needing to re-count each one each time. Alas, you would then need to make your already gross query even grosser, because PostgreSQL won't automatically rewrite queries to pull in preaggregated MVs when they would apply.

2
  • Thank you for your perspective. This query really is gross, eh! The CTE at the start of the query is just a projection / filters, so I rewrote the query to include it in the select and it reduced the cost from 700 to 500.
    – George B
    Commented Mar 14, 2023 at 13:41
  • I agree that a lot of it is down to the sheer number of rows. I'll play around with an MV solution. I was fixed on the sheer length of the query and the repetition, but that's just the js dev in me. Thank you for your help!
    – George B
    Commented Mar 14, 2023 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.