postgresql - Optimise With Query in PostgreSQL

Question

I have a working PostgreSQL query, but it is taking a considerable amount of time to execute. I need help optimising it.

I have:

• Removed inner queries as much as possible.
• Removed the unnecessary data from the query.
• Created a with query which gets the required data from the beginning

I need help to optimise this query

    with data as (
      select
      e.id,
      e.name,
      t.barcode,
      tt.variant,
      t.cost_cents::decimal / 100 as ticket_cost,
      t.fee_cents::decimal / 100 as booking_fee
    from
      tickets t
      inner join events e on t.event_id = e.id
      inner join ticket_types tt on t.ticket_type_id = tt.id
    where
      t.status = 2
      and e.source in ('source1', 'source2')
    )
    select
      d.name,
      count(distinct d.barcode) as issued,
      (select count(distinct d2.barcode) from data d2 where d2.id = d.id and d2.variant is null) as sold,
      sum(d.ticket_cost) as ticket_revenue,
      sum(d.booking_fee) as booking_fees
    from
      data d
    group by
      id,
      name

标签： postgresqloptimization

Answer 1

您可以通过创建适当的索引来加速连接。

另外，删除子查询

(select count(distinct d2.barcode) from data d2 where d2.id = d.id and d2.variant is null)

从 SELECT 子句中添加一个连接到 d2 表，如下所示：

select
  d.name,
  count(distinct d.barcode) as issued,
  count(distinct d2.barcode) as sold,
  sum(d.ticket_cost) as ticket_revenue,
  sum(d.booking_fee) as booking_fees
from
  data d
  left join data d2 on (d2.id = d.id and d2.variant is null)
group by
  d.id,
  d.name