PgHero

Queries

Total Time Average Time Calls
53,679 min 32% 34 ms 94,570,929 postgres 
-- runner get next pending job
      SELECT id
      FROM jobs
      WHERE (command like $1)
      AND (
        (status = $3 AND attempts < $2 AND now() >= next_run)
        OR (status = $4 AND (now() >= next_run OR next_run IS NULL))
      )
      ORDER BY next_run ASC NULLS FIRST, updated_at ASC
      LIMIT $5
      FOR UPDATE SKIP LOCKED
18,877 min 11% 1,020 ms 1,110,300 postgres 
SELECT
      (data->$2->$3)::numeric AS slot_id,
      jsonb_build_object(
        $4, count($5) filter (where state = $6),
        $7, coalesce(sum((data->$8)::numeric) filter (where state = $9 and (data->$10->$11)::numeric > extract($12 from now())), $13),
        $14, coalesce(sum((data->$15)::numeric) filter (where state = $16 and (data->$17->$18)::numeric <= extract($19 from now())), $20)
      ) as stats
    FROM tickets
    WHERE space_id = $21
      AND branch_id = $22
      AND service_id = $23
      AND data ? $24
    and state in ($25, $26)
    and (data->$27->$29)::numeric between $1 and extract($31 from now()) + $32*$33*$34
    group by 1
16,908 min 10% 3,114 ms 325,823 postgres 
SELECT $2 FROM services WHERE id = $1 FOR UPDATE
10,644 min 6% 145 ms 4,416,977 postgres 
-- slots days
      SELECT    date_trunc($3, to_timestamp(start_time_ts))::date AS day
      FROM      slot
      WHERE     space_id = $1
      AND       branch_id = $2
      AND       date_trunc($4, to_timestamp(start_time_ts)) >= date_trunc($5, now())
      GROUP BY  1
      HAVING    sum(coalesce(manual->$6, external->$7, internal->$8, $9::jsonb)::numeric) > $10
9,248 min 5% 40 ms 13,944,282 postgres 
-- slots days
      SELECT    date_trunc($3, to_timestamp(start_time_ts))::date AS day
      FROM      slot
      WHERE     space_id = $1
      AND       branch_id = $2
      AND       date_trunc($4, to_timestamp(start_time_ts)) >= date_trunc($5, now())
      GROUP BY  1
      HAVING    sum((internal->$6)::numeric) > $7
6,873 min 4% 1,266 ms 325,819 postgres 
select max("serial") from "ticket_history" where "space_id" = $1 and "branch_session_id" = $2 and "service_id" = $3
Covered by index on (branch_session_id, service_id)
Rows: 1269427
Row progression: 1269427, 28209, 9403

Row estimates
- branch_session_id (=): 28209
- service_id (=): 423142
- space_id (=): 634714

Existing indexes
- id PRIMARY
- branch_session_id, counter_id
- branch_session_id, service_id
- branch_session_id, state
- branch_session_id, user_id
- session_id, branch_session_id
- space_id
- space_id, branch_session_id, service_id, serial DESC
- state
- ticket_id
- tstzrange(created_at, next_created_at, '[]'::text) GIST
6,531 min 4% 822 ms 476,568 postgres 
-- lookup ticket
      SELECT *
      FROM tickets
      WHERE space_id = $1
      AND (id::text = $2::text
          OR lower(code) = lower($3::text)
          OR lower(label) = lower($4::text)
      )
      LIMIT $5
4,489 min 3% 3,065 ms 87,878 postgres 
SELECT
      (data->$5->$6)::numeric AS slot_id,
      jsonb_build_object(
        $7, count($8) filter (where state = $9),
        $10, coalesce(sum((data->$11)::numeric) filter (where state = $12 and (data->$13->$14)::numeric > extract($15 from now())), $16),
        $17, coalesce(sum((data->$18)::numeric) filter (where state = $19 and (data->$20->$21)::numeric <= extract($22 from now())), $23)
      ) as stats
    FROM tickets
    WHERE space_id = $1
      AND branch_id = $2
      AND service_id = $3
      AND state IN ($24, $25)
      AND data ? $26
      AND slot_id IN (
        SELECT id
        FROM slot
        WHERE start_time_ts BETWEEN $4 AND extract($27 from now()) + $28*$29*$30
      )
    GROUP BY 1
4,139 min 2% 1,659 ms 149,717 postgres 
--tickets-stats-total
    WITH data1 AS (
      SELECT  COALESCE(sum((values->$4)::numeric), $5)                                                  AS total_count,
              round((sum((values->$6)::numeric) / sum((values->$7)::numeric)))     AS waiting_average,
              round(sum((values->$8)::numeric) / sum((values->$9)::numeric))   AS attending_average
      FROM    ticket_stats
      WHERE   space_id = $1
      AND     (keys->>$10)::integer in ($11)
      AND     tr <@ numrange($12, $13)
    ), 
    data2 AS (
      SELECT  COALESCE(COUNT(tickets.id) FILTER (WHERE state = $14), $15)                               AS waiting_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $16), $17)                             AS attending_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $18), $19)                                AS paused_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $20), $21)                            AS terminated_count
      FROM    tickets
      WHERE   space_id = $2
      AND     branch_id in ($22)
      AND     EXTRACT($23 FROM created_at)::numeric <@ 
              numrange($24, $25)
    ),
    data3 AS (
      SELECT  coalesce(sum((stats->>$26)::integer), $27) as slot_noshow_count,
              coalesce(sum((stats->>$28)::integer), $29) as slot_booked_count,
              coalesce(sum((stats->>$30)::integer), $31) as slot_checked_count,
              round((
                coalesce(sum((stats->>$32)::integer), $33)::numeric /
                greatest(
                  coalesce(sum((stats->>$34)::integer), $35) +
                  coalesce(sum((stats->>$36)::integer), $37) +
                  coalesce(sum((stats->>$38)::integer), $39),
                  $40
                )::numeric
              ) * $41) || $42 as slot_noshow_rate
      from    slot
      where   space_id = $3
      and     numrange(start_time_ts, end_time_ts) <@ numrange($43, $44)
    )
    select json_agg(data4)::jsonb as aggdata
      from (
        SELECT 
          data1.total_count,
          data1.waiting_average,
          data1.attending_average,
          data2.waiting_count,
          data2.attending_count,
          data2.paused_count,
          data2.terminated_count,
          data3.slot_noshow_count,
          data3.slot_booked_count,
          data3.slot_checked_count,
          data3.slot_noshow_rate
        FROM data1, data2, data3
      ) data4
4,089 min 2% 1,888 ms 129,939 postgres 
-- count-tickets-by-service-profile.js
    with data as (
        SELECT    (service->>$2)::integer as service_profile_id, count(id) as cnt
        FROM      ticket_archive
        WHERE     space_id = $1
        AND       branch_id in ($3)
        AND       extract($4 from created_at)::numeric <@ numrange($5, $6)
        GROUP BY  1
        ORDER BY  2 desc
        LIMIT     $7
    ),
    data2 as (
        SELECT      data.cnt, 
                    service_profiles.id,
                    service_profiles.name, 
                    service_profiles.code, 
                    service_profiles.color
        FROM        data
        INNER JOIN  service_profiles
        ON          data.service_profile_id = service_profiles.id
        ORDER BY    data.cnt DESC
    )
    SELECT json_agg(data2)::jsonb as aggdata FROM data2
3,845 min 2% 708 ms 325,811 postgres 
select  count($4) as cnt
    from    tickets
    where   space_id = $1
    and     service_id = $2
    and     state = $5
    and     id != $3
Details
CREATE INDEX CONCURRENTLY ON tickets (state)
 
Rows: 334112
Row progression: 334112, 83528, 41764

Row estimates
- state (=): 83528
- service_id (=): 167056
- space_id (=): 167056
- id (<>): 334111

Existing indexes
- id PRIMARY
- (((metadata -> 'boarding_pass'::text) ->> 'encoded'::text)) WHERE metadata ? 'boarding_pass'::text UNIQUE
- counter_id
- serial, branch_session_id, service_id, space_id UNIQUE
- service_id WHERE state = 'waiting'::text
- space_id, ((((data -> 'slot'::text) -> 'start_time_ts'::text))::integer) WHERE data ? 'slot'::text
- space_id, ((id)::text), lower(code), lower(label)
- space_id, branch_id, service_id, (((data -> 'slot'::text) ->> 'id'::text)), ((((data -> 'slot'::text) -> 'start_time_ts'::text))::integer) WHERE data ? 'slot'::text
- space_id, created_at
- space_id, searchable GIN
- space_id, service_id, branch_session_id
- to_timestamp(((((data -> 'slot'::text) -> 'end_time_ts'::text))::numeric)::double precision) WHERE (data ? 'email'::text) AND (data ? 'slot'::text) AND ((data ->> 'email'::text) <> ''::text)
- to_timestamp(((((data -> 'slot'::text) -> 'end_time_ts'::text))::numeric)::double precision) WHERE (data ? 'email'::text) AND (data ? 'slot'::text) AND ((data ->> 'email'::text) <> ''::text)
3,384 min 2% 0 ms 1,749,302,520 postgres 
INSERT INTO slot (id, space_id, branch_id, label, start_time_ts, end_time_ts, internal, stats)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
      ON CONFLICT (id, space_id, branch_id) DO UPDATE
      SET stats = EXCLUDED.stats, internal = EXCLUDED.internal
2,723 min 2% 9,109 ms 17,932 postgres 
UPDATE  tickets
  SET     data = jsonb_set(data, $1, $2::jsonb)
  WHERE   data ? $3
  AND     data->>$4 <> $5
  AND     data ? $6
  AND     to_timestamp((data->$7->$8)::numeric) + $9::interval < now()
2,235 min 1% 1,166 ms 115,016 postgres 
--tickets-stats-total
    WITH data1 AS (
      SELECT  COALESCE(sum((values->$4)::numeric), $5)                                                  AS total_count,
              round((sum((values->$6)::numeric) / sum((values->$7)::numeric)))     AS waiting_average,
              round(sum((values->$8)::numeric) / sum((values->$9)::numeric))   AS attending_average
      FROM    ticket_stats
      WHERE   space_id = $1
      AND     (keys->>$10)::integer in ($11)
      AND     tr <@ numrange($12, $13)
    ), 
    data2 AS (
      SELECT  COALESCE(COUNT(tickets.id) FILTER (WHERE state = $14), $15)                               AS waiting_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $16), $17)                             AS attending_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $18), $19)                                AS paused_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $20), $21)                            AS terminated_count
      FROM    tickets
      WHERE   space_id = $2
      AND     branch_id in ($22)
      AND     EXTRACT($23 FROM created_at)::numeric <@ 
              numrange($24, $25)
    ),
    data3 AS (
      SELECT  coalesce(sum((stats->>$26)::integer), $27) as slot_noshow_count,
              coalesce(sum((stats->>$28)::integer), $29) as slot_booked_count,
              coalesce(sum((stats->>$30)::integer), $31) as slot_checked_count
      from    slot
      where   space_id = $3
      and     numrange(start_time_ts, end_time_ts) <@ numrange($32, $33)
    )
    select json_agg(data4)::jsonb as aggdata
      from (
        SELECT 
          data1.total_count,
          data1.waiting_average,
          data1.attending_average,
          data2.waiting_count,
          data2.attending_count,
          data2.paused_count,
          data2.terminated_count,
          data3.slot_noshow_count,
          data3.slot_booked_count,
          data3.slot_checked_count
        FROM data1, data2, data3
      ) data4
2,190 min 1% 243 ms 541,127 postgres 
--tickets-stats-total
    WITH data1 AS (
      SELECT  COALESCE(sum((values->$3)::numeric), $4)                                                  AS total_count,
              round((sum((values->$5)::numeric) / sum((values->$6)::numeric)))     AS waiting_average,
              round(sum((values->$7)::numeric) / sum((values->$8)::numeric))   AS attending_average
      FROM    ticket_stats
      WHERE   space_id = $1
      AND     (keys->>$9)::integer in ($10)
      AND     tr <@ numrange($11, $12)
    ), 
    data2 AS (
      SELECT  COALESCE(COUNT(tickets.id) FILTER (WHERE state = $13), $14)                               AS waiting_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $15), $16)                             AS attending_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $17), $18)                                AS paused_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $19), $20)                            AS terminated_count
      FROM    tickets
      WHERE   space_id = $2
      AND     branch_id in ($21)
      AND     EXTRACT($22 FROM created_at)::numeric <@ 
              numrange($23, $24)
    )
    select json_agg(data3)::jsonb as aggdata
      from (
        SELECT 
          data1.total_count,
          data1.waiting_average,
          data1.attending_average,
          data2.waiting_count,
          data2.attending_count,
          data2.paused_count,
          data2.terminated_count
        FROM data1, data2
      ) data3
2,177 min 1% 219 ms 595,887 postgres 
-- count-tickets-by-service-profile.js
    with data as (
        SELECT    (service->>$2)::integer as service_profile_id, count(id) as cnt
        FROM      tickets
        WHERE     space_id = $1
        AND       branch_id in ($3)
        AND       extract($4 from created_at)::numeric <@ numrange($5, $6)
        GROUP BY  1
        ORDER BY  2 desc
        LIMIT     $7
    ),
    data2 as (
        SELECT      data.cnt, 
                    service_profiles.id,
                    service_profiles.name, 
                    service_profiles.code, 
                    service_profiles.color
        FROM        data
        INNER JOIN  service_profiles
        ON          data.service_profile_id = service_profiles.id
        ORDER BY    data.cnt DESC
    )
    SELECT json_agg(data2)::jsonb as aggdata FROM data2
1,354 min 0.8% 169 ms 481,899 postgres 
-- count-tickets-by-service-profile.js
    with data as (
        SELECT    (service->>$2)::integer as service_profile_id, count(id) as cnt
        FROM      tickets
        WHERE     space_id = $1
        GROUP BY  1
        ORDER BY  2 desc
        LIMIT     $3
    ),
    data2 as (
        SELECT      data.cnt, 
                    service_profiles.id,
                    service_profiles.name, 
                    service_profiles.code, 
                    service_profiles.color
        FROM        data
        INNER JOIN  service_profiles
        ON          data.service_profile_id = service_profiles.id
        ORDER BY    data.cnt DESC
    )
    SELECT json_agg(data2)::jsonb as aggdata FROM data2
1,342 min 0.8% 2,377 ms 33,877 postgres 
WITH steps AS (
        SELECT  jsonb_array_elements(path) AS step
        FROM    tickets
        WHERE   space_id = $1
        AND     service_id = $2
        AND     branch_session_id = $3
      )
      SELECT  count($5) AS calls_after
      FROM    steps
      WHERE   step->>$6 = $7
      AND     step->>$8 = $9
      AND     (step->$10)::numeric > $4
1,264 min 0.8% 4 ms 21,623,574 postgres 
select "integrations".* from "integrations" where "space_id" = $1 and "type" = $2 and "enabled" = $3
1,180 min 0.7% 129 ms 549,057 postgres 
-- count-tickets-by-terminal
    with data AS (
        SELECT        counter_id, 
                      count(id) AS cnt
        FROM          tickets
        WHERE         space_id = $1
        AND           state = $2
        GROUP BY      1
        ORDER BY      2 DESC
        LIMIT         $3
    ),
    data2 AS (
        SELECT      data.cnt, 
                    counters.id, 
                    counters.name, 
                    counters.code, 
                    counters.slug
        FROM        data
        INNER JOIN  counters
        ON          data.counter_id = counters.id
        ORDER BY    data.cnt DESC
    )
    SELECT json_agg(data2)::jsonb as aggdata FROM data2
1,091 min 0.6% 89 ms 738,261 postgres 
-- influx-by-service-profile
    WITH top_profiles AS (
      SELECT        (keys->>$3)::integer AS id,
                    SUM(COALESCE((values->>$4)::integer, $5)) AS sp_tickets
      FROM          ticket_stats
      WHERE         space_id = $1
      AND           (keys->>$6)::integer in ($7)
      AND           tr <@ numrange($8, $9)
      GROUP BY      1
      ORDER BY      sp_tickets DESC
      LIMIT         $10
    ),
    data AS (
      SELECT        extract($11 from to_timestamp(lower(tr))::timestamptz at time zone $12) as val,
                    SUM(COALESCE((values->>$13)::integer, $14)) AS cnt,
                    (keys->>$15)::integer AS service_profile_id
      FROM          ticket_stats
      WHERE         space_id = $2
      AND           numrange(lower(tr), upper(tr)) &&
                    numrange($16, $17)
      GROUP BY      service_profile_id,
                    val
      ORDER BY      cnt DESC,
                    service_profile_id ASC
    ),
    filtered_data AS (
      SELECT        *
      FROM          data
      JOIN          top_profiles
      ON            data.service_profile_id = top_profiles.id
    ),
    service_profile2 AS (
      SELECT        filtered_data.cnt,
                    filtered_data.val,
                    service_profiles.id,
                    name,
                    code,
                    color
      FROM          filtered_data
      INNER JOIN    service_profiles
      ON            service_profiles.id = filtered_data.service_profile_id::int
      ORDER BY      filtered_data.cnt DESC
    ),
    data3 AS (
      SELECT        id,
                    name,
                    color,
                    code,
                    $18 AS time_interval,
                    jsonb_agg(JSONB_BUILD_OBJECT($19, val, $20, cnt)) AS unit_count
      FROM          service_profile2
      GROUP BY      id, name, code, color
    )
    SELECT json_agg(data3)::jsonb as aggdata
    FROM   data3
1,053 min 0.6% 2 ms 32,762,469 postgres 
-- runner get next pending job
      SELECT id
      FROM jobs
      WHERE (command like $2)
      AND (
        (status = $3 AND attempts < $1 AND now() >= next_run)
        OR (status = $4 AND (now() >= next_run OR next_run IS NULL))
      )
      ORDER BY next_run ASC NULLS FIRST, updated_at ASC
      LIMIT $5
      FOR UPDATE SKIP LOCKED
942 min 0.6% 58 ms 975,068 postgres 
SELECT    *, coalesce(manual->$7, external->$8, internal->$9, $10::jsonb)::numeric as capacity
      FROM      slot
      WHERE     space_id = $1
      AND       branch_id = $2
      AND       start_time_ts >= extract($11 from now())
      AND       start_time_ts >= $3 AND end_time_ts <= $4
      AND       coalesce(manual->$12, external->$13, internal->$14, $15::jsonb)::numeric - (stats->$16)::numeric >= $5
      ORDER BY  id
      LIMIT     $6
911 min 0.5% 50 ms 1,102,271 postgres 
SELECT *
      FROM flight_airport
      WHERE space_id = $1
      AND iata_code = $2
      LIMIT $3
Details
CREATE INDEX CONCURRENTLY ON flight_airport (iata_code)
 
Rows: 832152
Row progression: 832152, 1

Row estimates
- iata_code (=): 1
- space_id (=): 416076

Existing indexes
- id PRIMARY
- space_id, ((provider ->> 'name'::text)), ((provider ->> 'id'::text)) UNIQUE
- space_id, iata_code
867 min 0.5% 185 ms 280,579 postgres 
-- lookup ticket
      SELECT *
      FROM tickets
      WHERE space_id = $1
      AND (id::text = $2::text OR lower(code) = lower($3::text))
      LIMIT $4
651 min 0.4% 27 ms 1,428,848 postgres 
-- tickets-stats-average
    WITH DATA AS (
      SELECT    extract($2 from to_timestamp(lower(tr))::timestamptz at time zone $3) as val,
                (keys->>$4)::integer AS branch_id,
                jsonb_strip_nulls(jsonb_build_object(
                  $5,
                      round(sum((values->$6)::NUMERIC) FILTER (WHERE (values->$7)::numeric IS NOT NULL)
                        /
                        sum((values->$8)::NUMERIC) FILTER (WHERE (values->$9)::numeric IS NOT NULL), $10),
                    $11,
                        round(sum((values->$12)::NUMERIC) FILTER (WHERE (values->$13)::numeric IS NOT NULL)
                        /
                        sum((values->$14)::NUMERIC) FILTER (WHERE (values->$15)::numeric IS NOT NULL), $16)
                )) AS unit_count
      FROM      ticket_stats
      WHERE     space_id = $1
      AND       (keys->>$17)::integer in ($18)
      AND       tr <@ numrange($19, $20)
      GROUP BY  val, branch_id
    ),
    AGG_DATA AS (
        SELECT    b.name,
                  b.metadata->>$21 as color,
                  jsonb_agg(jsonb_build_object($22, d.val, $23, d.unit_count)) as unit_count,
                  $24 as time_interval
        FROM      DATA d
        JOIN      branches b ON d.branch_id = b.id
        GROUP BY  b.name, color
    ),
    TOTALS AS (
        SELECT $25 as name,
                $26 as color,
                jsonb_agg(jsonb_build_object($27, val, $28, unit_count)) as unit_count,
                $29 as time_interval
        FROM (
          SELECT    val,
                    jsonb_strip_nulls(jsonb_build_object(
                        $30, round(AVG((unit_count->>$31)::numeric), $32),
                        $33, round(AVG((unit_count->>$34)::numeric), $35)
                    )) AS unit_count
          FROM      DATA
          GROUP BY  val
        ) AS sub
    )
    SELECT jsonb_agg(data) as aggdata
    FROM (
      SELECT * FROM AGG_DATA
      UNION ALL
      SELECT * FROM TOTALS
    ) as data
604 min 0.4% 21 ms 1,751,838 postgres 
SELECT      airline_iata, arr_iata, dep_time_ts, flight_iata, id, provider
    FROM        flight_schedule
    WHERE       dep_time_ts >= $1 AND dep_time_ts <= $2
    AND         space_id = $3
    AND         enabled
    
    ORDER BY    dep_time_ts ASC
538 min 0.3% 1,085 ms 29,743 postgres 
SELECT tickets.data->$1->>$2 AS slot_id, sum(coalesce(tickets.data->>$3, $4)::int) AS count
      FROM tickets
      WHERE space_id = $5
        AND branch_id = $6
        AND service_id = $7
        AND data ? $8
        AND (data->$9->$10)::int > extract($11 from now())
      GROUP BY 1
534 min 0.3% 96 ms 334,643 postgres 
--tickets-stats-total
    WITH data1 AS (
      SELECT  COALESCE(sum((values->$3)::numeric), $4)                                                  AS total_count,
              round((sum((values->$5)::numeric) / sum((values->$6)::numeric)))     AS waiting_average,
              round(sum((values->$7)::numeric) / sum((values->$8)::numeric))   AS attending_average
      FROM    ticket_stats
      WHERE   space_id = $1
      AND     (keys->>$9)::integer in ($10)
      AND     tr <@ numrange($11, $12)
    ), 
    data2 AS (
      SELECT  COALESCE(COUNT(tickets.id) FILTER (WHERE state = $13), $14)                               AS waiting_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $15), $16)                             AS attending_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $17), $18)                                AS paused_count,
              COALESCE(COUNT(tickets.id) FILTER (WHERE state = $19), $20)                            AS terminated_count
      FROM    tickets
      WHERE   space_id = $2
      AND     EXTRACT($21 FROM created_at)::numeric <@ 
              numrange($22, $23)
    )
    select json_agg(data3)::jsonb as aggdata
      from (
        SELECT 
          data1.total_count,
          data1.waiting_average,
          data1.attending_average,
          data2.waiting_count,
          data2.attending_count,
          data2.paused_count,
          data2.terminated_count
        FROM data1, data2
      ) data3
492 min 0.3% 27 ms 1,108,629 postgres 
-- slot-occupancy
    WITH data AS (
      SELECT    label, 
                internal->$2 as capacity, 
                stats,
                start_time_ts,
                trunc(extract($3 from now())) as now
      FROM      slot
      WHERE     space_id = $1
      AND       to_timestamp(start_time_ts) >= (now() - $4::interval) and to_timestamp(start_time_ts) < (now() + $5::interval)
      order by  start_time_ts
    )
    SELECT json_agg(d) AS aggdata FROM (SELECT * FROM data) d
465 min 0.3% 15,429 ms 1,808 postgres 
SELECT slot.id "slot_id", SUM(COALESCE(tickets.data->>$4, $5)::int) "count"
      FROM tickets
      JOIN slot ON slot.id = tickets.slot_id
      WHERE tickets.space_id = $1
      AND tickets.branch_id = $2
      AND tickets.service_id = $3
      AND tickets.state = $6
      AND slot.start_time_ts > extract($7 from now())
      GROUP BY 1
330 min 0.2% 41 ms 481,900 postgres 
-- influx-by-service-profile
    WITH top_profiles AS (
      SELECT        (keys->>$3)::integer AS id,
                    SUM(COALESCE((values->>$4)::integer, $5)) AS sp_tickets
      FROM          ticket_stats
      WHERE         space_id = $1
      AND           tr <@ numrange($6, $7)
      GROUP BY      1
      ORDER BY      sp_tickets DESC
      LIMIT         $8
    ),
    data AS (
      SELECT        extract($9 from to_timestamp(lower(tr))::timestamptz at time zone $10) as val,
                    SUM(COALESCE((values->>$11)::integer, $12)) AS cnt,
                    (keys->>$13)::integer AS service_profile_id
      FROM          ticket_stats
      WHERE         space_id = $2
      AND           numrange(lower(tr), upper(tr)) &&
                    numrange($14, $15)
      GROUP BY      service_profile_id,
                    val
      ORDER BY      cnt DESC,
                    service_profile_id ASC
    ),
    filtered_data AS (
      SELECT        *
      FROM          data
      JOIN          top_profiles
      ON            data.service_profile_id = top_profiles.id
    ),
    service_profile2 AS (
      SELECT        filtered_data.cnt,
                    filtered_data.val,
                    service_profiles.id,
                    name,
                    code,
                    color
      FROM          filtered_data
      INNER JOIN    service_profiles
      ON            service_profiles.id = filtered_data.service_profile_id::int
      ORDER BY      filtered_data.cnt DESC
    ),
    data3 AS (
      SELECT        id,
                    name,
                    color,
                    code,
                    $16 AS time_interval,
                    jsonb_agg(JSONB_BUILD_OBJECT($17, val, $18, cnt)) AS unit_count
      FROM          service_profile2
      GROUP BY      id, name, code, color
    )
    SELECT json_agg(data3)::jsonb as aggdata
    FROM   data3
330 min 0.2% 210 ms 94,110 postgres 
-- slot-occupancy
    WITH data AS (
      SELECT    label, 
                coalesce(manual->$2, external->$3, internal->$4, $5::jsonb)::numeric as capacity,
                stats,
                start_time_ts,
                trunc(extract($6 from now())) as now
      FROM      slot
      WHERE     space_id = $1
      AND       to_timestamp(start_time_ts) >= (now() - $7::interval) and to_timestamp(start_time_ts) < (now() + $8::interval)
      order by  start_time_ts
    )
    SELECT json_agg(d) AS aggdata FROM (SELECT * FROM data) d
150 min < 0.1% 187 ms 48,053 postgres 
-- count-tickets-by-user
    with data AS (
        SELECT    user_id, 
                  count(id) as cnt, 
                  avg((stats->>$2)::numeric)::int as avg_attending_time
        FROM      tickets
        WHERE     space_id = $1
        AND       state = $3
        AND       branch_id in ($4)
        AND       extract($5 from updated_at)::numeric <@ numrange($6, $7)
        AND       user_id IS NOT NULL
        GROUP BY  1
        ORDER BY  2 DESC
        LIMIT     $8
    ),
    data2 AS (
        SELECT      data.cnt, 
                    users.id, 
                    users.name,
                    data.avg_attending_time
        FROM        data
        INNER JOIN  users
        ON          data.user_id = users.id
        ORDER BY    data.cnt DESC
    )
    SELECT json_agg(data2)::jsonb as aggdata FROM data2
125 min < 0.1% 6,017 ms 1,251 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 2ea237e0e4c10cd251a88149fa7395c93c8191cfdeafe9c72750fb28e476c4b6
select
    to_char(date_trunc($1, updated_at), $2) as "Access Day",
    count($3) filter (where updated_at < to_timestamp((data->$4->$5)::numeric)) as "Before",
    count($6) filter (where updated_at between to_timestamp((data->$7->$8)::numeric) and to_timestamp((data->$9->$10)::numeric) + $11::interval) as "In 5min",
    count($12) filter (where updated_at between to_timestamp((data->$13->$14)::numeric) + $15 and to_timestamp((data->$16->$17)::numeric) + $18::interval) as "5 to 10 Min",
    count($19) filter (where updated_at between to_timestamp((data->$20->$21)::numeric) + $22 and to_timestamp((data->$23->$24)::numeric) + $25::interval) as "10 to 15 Min",
    count($26) filter (where updated_at >= to_timestamp((data->$27->$28)::numeric) + $29) as "After 15 Min"
from tickets
where space_id = $30
and data ? $31
and state = $32
and created_at > now() - $33::interval
group by date_trunc('day', updated_at)
order by date_trunc($34, updated_at) asc
114 min < 0.1% 5,520 ms 1,243 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 2548a187c5d4591a48cd695efb23eac40ceee60809268b6cebcfab6a18a1b998
with hs as (
    select generate_series($1, $2, $3) as h
),
data as (
    select
        extract($4 from to_timestamp((data->$5->>$6)::integer) at time zone $7) as h,
        data
    from tickets
    where space_id = $8 and (to_timestamp((data->$9->>$10)::integer) at time zone $11)::date = (now() at time zone $12)::date - $13::interval
)
select 
    hs.h as "Hour",
    coalesce(count($14) filter (where data ? $15), $16) as "Booking",
    coalesce(sum((data->>$17)::integer) filter (where data ? $18), $19) as "Party Size" --,
    -- coalesce(sum((data->>'party_size')::integer) filter (where data ? 'boarding_pass'), 0) as "Scan"
from hs
left join data
on hs.h = data.h
group by hs.h
order by hs.h asc
95 min < 0.1% 9,327 ms 612 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 60a0d69ac8cce4a2a36b7d98b8d1d64be9a579d37b18d704c751447f82d7cca3
with airport as (
    select distinct on (iata_code) iata_code, name, provider->$1->>$2 as city_name
    from flight_airport
    where space_id = $3
    order by iata_code
),
airline as (
    select distinct on (replace(iata_code, '*', '')) replace(iata_code, $4, $5) as iata_code, name
    from flight_airline
    where space_id = $6
    order by iata_code
),
bookings as (
    select (data->$7->>$8)::integer as dep_time_ts, data->$9->>$10 as flight_iata, count(id) as cnt, sum((data->>$11)::integer) as sum_party_size
    from tickets
    where space_id = $12
    and state = $13
    and data ? $14
    group by 1, 2
)
select      to_timestamp(flight_schedule.dep_time_ts) at time zone $15 as "Departure (CEST)",
            flight_schedule.flight_iata as "Flight Number",
            flight_schedule.airline_iata || $16 || coalesce(airline.name, $17) as "Airline",
            flight_schedule.arr_iata || $18 || coalesce(airport.city_name, $19) as "Airport",
            to_char((to_timestamp(floor(flight_schedule.dep_time_ts::numeric / $20 / $21) * $22 * $23) - (case when (flight_schedule.data->>$24)::bool then $25::interval else $26::interval end)) at time zone $27, $28) as "Earliest Slot",
            to_char((to_timestamp(floor(flight_schedule.dep_time_ts::numeric / $29 / $30) * $31 * $32 - $33 * $34) - (case when (flight_schedule.data->>$35)::bool then $36::interval else $37::interval end)) at time zone $38, $39) as "Latest Slot",
            coalesce(bookings.cnt, $40) as "Bookings",
            coalesce(bookings.sum_party_size, $41) as "People"
            --,flight_schedule.data, flight_schedule.provider
from        flight_schedule
left join   airline on flight_schedule.airline_iata = replace(airline.iata_code, $42, $43)
left join   airport on flight_schedule.arr_iata = airport.iata_code
left join   bookings on bookings.flight_iata = flight_schedule.flight_iata and bookings.dep_time_ts = flight_schedule.dep_time_ts
where       flight_schedule.space_id = $44 --and flight_schedule.enabled
and         flight_schedule.flight_iata is not null
and         to_timestamp(flight_schedule.dep_time_ts) >= (now())
and         to_timestamp(flight_schedule.dep_time_ts) <= (now() + $45::interval)
-- filter by a specific flight
-- and flight_schedule.flight_iata = 'HV6533'
order by    flight_schedule.dep_time_ts, airport.name
91 min < 0.1% 10,632 ms 513 postgres 
-- Metabase:: userID: 1 queryType: native queryHash: adfa643c251d008b6e7361acca57082f1026b1fc664862892e7c442d9e601448
select sum((data->$1)::integer) as "Total"
from ticket_history
where space_id = $2
and state = $3
and next_state is null
Covered by index on (state)
Rows: 1269427
Row progression: 1269427, 253885, 94792

Row estimates
- state (=): 253885
- next_state (null): 473962
- space_id (=): 634714

Existing indexes
- id PRIMARY
- branch_session_id, counter_id
- branch_session_id, service_id
- branch_session_id, state
- branch_session_id, user_id
- session_id, branch_session_id
- space_id
- space_id, branch_session_id, service_id, serial DESC
- state
- ticket_id
- tstzrange(created_at, next_created_at, '[]'::text) GIST
80 min < 0.1% 4 ms 1,214,214 postgres 
select "integrations".* from "integrations" where "enabled" = $1 and "id" = $2 and "space_id" = $3
75 min < 0.1% 245 ms 18,269 postgres 
-- core/janitor finished-jobs/notifications
    WITH deleted_jobs AS (
      DELETE FROM   jobs
      WHERE         last_run_ts < now() - $1::interval
      AND           status = $2
      RETURNING     notification_id
    )
    DELETE FROM     notifications
    WHERE           id IN (SELECT notification_id FROM deleted_jobs)
74 min < 0.1% 8 ms 550,457 postgres 
insert into "notifications" ("created_at", "data", "space_id", "state", "type", "updated_at") values ($1, $2, $3, $4, $5, $6) returning *
70 min < 0.1% 6,829 ms 619 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: f2e3a51aa5e0d1c7e9ecec58e75ce227eaf1ed9b57ff4f17fe22f2410c0aaabc
select          to_char(to_timestamp((data->$1->>$2)::integer) at time zone $3, $4) as "Date",
                to_char(to_timestamp((data->$5->>$6)::integer) at time zone $7, $8) as "Slot Time",
                data->$9->>$10 as "Flight Number",
                (data->$11->>$12) || $13 || (data->$14->>$15) as "Destination",
                to_char(to_timestamp((data->$16->>$17)::integer) at time zone $18, $19) as "Departure Time",
                to_char(created_at at time zone $20, $21) as "Registration",
                (data->>$22)::integer as "Party Size"
from            tickets
where           space_id = $23
and             data ? $24
and             state = $25
and             to_timestamp((data->$26->>$27)::integer) > now()
and             to_timestamp((data->$28->>$29)::integer) > now()
order by        (data->$30->>$31)::integer asc
--limit 100
--{"slot": {"id": 1679664600, "label": "14C", "offset": 120, "end_time": "2023-03-24 14:45", "start_time": "2023-03-24 14:30", "end_time_ts": 1679665500, "start_time_ts": 1679664600}, "schedule": {"id": 158924, "data": {"flight_number": "LH 1983", "flight_status": "", "schedule_time": "20230324 16:40:00", "take_off_time": "", "estimated_time": ""}, "enabled": true, "arr_iata": "MUC", "arr_name": "Munich International Airport", "dep_iata": "CGN", "dep_time_ts": 1679672400, "flight_iata": "LH1983", "airline_iata": "LH", "airline_name": "Lufthansa", "arr_city_name": "Munich", "cs_flight_iata": null, "cs_airline_iata": null, "arr_country_code": "DE", "arr_country_name": "Germany"}, "party_size": 1, "boarding_pass": {}}
65 min < 0.1% 6,275 ms 621 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: de2b789ecdfac18b2aa629e419886f513d111a07b6725095c98073607fff217b
with xs as (
    select generate_series(
        date_trunc($1, now() at time zone $2) - $3::interval,
        date_trunc($4, now() at time zone $5),
        $6::interval) as x
),
ds as (
    select
        date_trunc($7, to_timestamp((data->$8->>$9)::integer) at time zone $10) as d,
        data
    from tickets
    where space_id = $11
)
select
    to_char(xs.x, $12) as "Day",
    coalesce(sum((ds.data->>$13)::integer) filter (where ds.data ? $14), $15) as "Party Size",
    coalesce(count($16) filter (where ds.data ? $17), $18) as "Booking"
    --coalesce(sum((ds.data->>'party_size')::integer) filter (where ds.data ? 'boarding_pass'), 0) as "Scan"
from xs
left join ds on xs.x = ds.d
group by xs.x
order by xs.x asc
64 min < 0.1% 6,340 ms 610 postgres 
-- Metabase:: userID: 1 queryType: native queryHash: d4523429e79774b5a816ac0213b671627047659cdd2f4e8a57a8d4403d27f884
select count(distinct ticket_id) from ticket_history where space_id = $1
63 min < 0.1% 10 ms 376,734 postgres 
SELECT $2 FROM ONLY "public"."jobs" x WHERE $1 OPERATOR(pg_catalog.=) "notification_id" FOR KEY SHARE OF x
59 min < 0.1% 280 ms 12,680 postgres 
SELECT
      (data->$1->$2)::numeric AS slot_id,
      jsonb_build_object(
        $3, count($4) filter (where state = $5),
        $6, coalesce(sum((data->$7)::numeric) filter (where state = $8 and (data->$9->$10)::numeric > extract($11 from now())), $12),
        $13, coalesce(sum((data->$14)::numeric) filter (where state = $15 and (data->$16->$17)::numeric <= extract($18 from now())), $19)
      ) as stats
    FROM tickets
    WHERE space_id = $20
      AND branch_id = $21
      AND service_id = $22
      AND data ? $23
    and state in ($24, $25)
    and (data->$26->$28)::numeric between extract($30 from now()) - $31*$32 and extract($33 from now()) + $34*$35*$36
    group by 1
58 min < 0.1% 2,768 ms 1,267 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 0cac62d8df8a9fadec616eb5c32301d811cccc3cf7d197baf12da235d03d64ab
select
    to_char(date_trunc($1, created_at), $2) as "Booking Day",
    --date_trunc('day', created_at) as "Booking Day",
    count($3) filter (where created_at < to_timestamp((data->$4->$5)::numeric) - $6::interval) as "> 24",
    count($7) filter (where created_at between to_timestamp((data->$8->$9)::numeric) - $10::interval and to_timestamp((data->$11->$12)::numeric) - $13::interval) as "24 - 12",
    count($14) filter (where created_at between to_timestamp((data->$15->$16)::numeric) - $17::interval and to_timestamp((data->$18->$19)::numeric) -  $20::interval) as "12 - 6",
    count($21) filter (where created_at between to_timestamp((data->$22->$23)::numeric) -  $24::interval and to_timestamp((data->$25->$26)::numeric) -  $27::interval) as "6 - 3",
    count($28) filter (where created_at between to_timestamp((data->$29->$30)::numeric) -  $31::interval and to_timestamp((data->$32->$33)::numeric) -  $34::interval) as "3 - 1",
    count($35) filter (where created_at > to_timestamp((data->$36->$37)::numeric) - $38::interval) as "< 1"
    --count(1) filter (where ) as "1 Hours Before Departure",
    
from tickets
where space_id = $39
and date_trunc($40, created_at) > now() - $41::interval
group by date_trunc('day', created_at)
order by date_trunc($42, created_at) asc
58 min < 0.1% 20 ms 176,377 postgres 
-- influx-tickets-stats
    WITH data AS (
      SELECT    extract($2 from to_timestamp(lower(tr))::timestamptz at time zone $3) as val,
                (keys->>$4)::integer AS branch_id,
                coalesce(sum((values->>$5)::numeric), $6) as cnt
      FROM      ticket_stats
      WHERE     space_id = $1
      AND       (keys->>$7)::integer in ($8)
      AND       tr <@ numrange($9, $10)
      GROUP BY  2, 1
    ), 
    data2 AS (
      SELECT      b.name,
                  metadata->>$11 as color,
                  jsonb_agg(JSONB_BUILD_OBJECT($12, data.val, $13, data.cnt)) AS unit_count,
                  $14 as time_interval
      FROM        data
      INNER JOIN  branches b ON b.id = data.branch_id
      GROUP BY    1, 2
    ), 
    total as (
      SELECT    $15   AS name,
                $16      AS color,
                val,
                sum(cnt)  AS total_cnt
      FROM      data
      GROUP BY  3
    ), 
    total_agg as (
      SELECT        name,
                    color,
                    jsonb_agg(
                      JSONB_BUILD_OBJECT(
                        $17, val, 
                        $18, total_cnt
                      )
                    ) AS unit_count,
                    $19 as time_interval
      FROM          total
      GROUP BY      1, 2
    )
    SELECT json_agg(t)::jsonb as aggdata
    FROM (
      SELECT * FROM data2
      UNION ALL
      SELECT * FROM total_agg
    ) t
53 min < 0.1% 0 ms 25,519,478 postgres 
insert into request (method, uri, headers, space_id, route, ua, status, dur, ip, geo, errors, body)
    values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
49 min < 0.1% 13 ms 220,902 postgres 
-- count-tickets-stats
    WITH data AS (
      SELECT    (keys->>$2)::integer AS branch_id,
                SUM(COALESCE((values->>$3)::numeric, $4)) as cnt
      FROM      ticket_stats
      WHERE     space_id = $1
      AND       tr <@ numrange($5, $6)
      AND       (keys->>$7)::integer in ($8)
      GROUP BY  1
    ),
    data3 AS (
      SELECT    branches.id as id,
                branches.name as name,
                branches.metadata->>$9 as color,
                data.cnt
      FROM      data
      JOIN      branches ON data.branch_id = branches.id
    )
    SELECT json_agg(data3) as aggdata FROM data3
44 min < 0.1% 10 ms 256,374 postgres 
insert into "tickets" ("available_actions", "branch", "branch_id", "branch_session_id", "code", "created_at", "data", "label", "priority", "queue_id", "reason", "serial", "service", "service_id", "session_id", "sm_transition", "source", "space_id", "state", "stats", "tz", "updated_at", "user", "user_id") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24) returning *
42 min < 0.1% 0 ms 18,475,467 postgres 
INSERT INTO flight_airline (space_id, name, iata_code, provider, created_at, updated_at)
          VALUES ($1, $2, $3, $4, now(), now())
          ON CONFLICT (space_id, (provider->>$5), (provider->>$6))
          DO UPDATE SET
            name = EXCLUDED.name,
            iata_code = EXCLUDED.iata_code,
            provider = EXCLUDED.provider,
            updated_at = EXCLUDED.updated_at
42 min < 0.1% 0 ms 18,173,901 postgres 
INSERT INTO flight_airport (space_id, name, iata_code, provider, created_at, updated_at)
          VALUES ($1, $2, $3, $4, now(), now())
          ON CONFLICT (space_id, (provider->>$5), (provider->>$6))
          DO UPDATE SET
            name = EXCLUDED.name,
            iata_code = EXCLUDED.iata_code,
            provider = EXCLUDED.provider,
            updated_at = EXCLUDED.updated_at
41 min < 0.1% 1 ms 2,292,267 postgres 
select "datasets".* from "datasets" where "dashboard_id" = $1 order by "created_at" asc
39 min < 0.1% 0 ms 16,760,992 postgres 
INSERT INTO flight_schedule (space_id, dep_time_ts, arr_iata, dep_iata, flight_iata, airline_iata, data, provider, created_at, updated_at)
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, now(), now())
          ON CONFLICT (space_id, (provider->>$9), (provider->>$10))
          DO UPDATE SET
            dep_time_ts = EXCLUDED.dep_time_ts,
            data = EXCLUDED.data,
            updated_at = EXCLUDED.updated_at
30 min < 0.1% 2,912 ms 628 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 6d2419962aa4f66a53f383fc8e43ed3fdd21e6ba49a4413a51438fe9e02745de
with slots as (
    select generate_series(
    floor(extract($1 from now()) / $2 / $3)::integer * $4 * $5,
    floor(extract($6 from now()) / $7 / $8)::integer * $9 * $10 + $11 * $12 * $13 * $14,
    $15 * $16) as t
),
data as (
    select to_timestamp(slots.t) at time zone $17 as t,
    count(tickets.id) as c0,
    coalesce(sum((tickets.data->>$18)::integer), $19) as c,
    coalesce(sum((tickets.data->>$20)::integer) filter (where data ? $21), $22) as c1,
    coalesce(sum((tickets.data->>$23)::integer) filter (where data ? $24), $25) as c2
    from slots
    left join tickets on slots.t = (tickets.data->$26->>$27)::integer and tickets.state = $28 and tickets.space_id = $29
    group by 1
    order by 1 asc
)
select to_char(t, $30) as "Hour", c0 as "Booking", c1 as "Party Size" --, c2 as "Scan"
from data
27 min < 0.1% 0 ms 6,352,805 postgres 
update "datasets" set "echart" = $1, "computed_at" = $2, "updated_at" = $3 where "id" = $4 returning *
27 min < 0.1% 0 ms 10,808,104 postgres 
INSERT INTO flight_schedule (space_id, dep_time_ts, dep_iata, arr_iata, airline_iata, flight_iata, data, provider, enabled, created_at, updated_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, now(), now())
        ON CONFLICT (space_id, (provider->>$10), (provider->>$11))
        DO UPDATE SET dep_time_ts = EXCLUDED.dep_time_ts, data = EXCLUDED.data, provider = EXCLUDED.provider, updated_at = EXCLUDED.updated_at, enabled = EXCLUDED.enabled
26 min < 0.1% 14 ms 110,770 postgres 
-- ticket-count-heatmap
    WITH data AS (
      SELECT    keys->$2 as day, 
                keys->$3 as hour, 
                SUM((values->$4)::numeric) AS count
      FROM      ticket_stats
      WHERE     space_id = $1
      AND       (keys->>$5)::integer in ($6)
      AND       tr <@ numrange($7, $8)
      AND       values ? $9
      AND       keys ? $10
      AND       keys ? $11
      GROUP BY  1, 2
    )
    SELECT json_agg(d) as aggdata FROM (SELECT * FROM data) d
25 min < 0.1% 304 ms 4,996 postgres 
select "tickets".* from "tickets" where "space_id" = $1 and "service_id" = $2 and "branch_session_id" = $3 and "state" = $4 order by "created_at" asc
Details
CREATE INDEX CONCURRENTLY ON tickets (state)
 
Rows: 334112
Row progression: 334112, 83528, 41764

Row estimates
- state (=): 83528
- branch_session_id (=): 167056
- service_id (=): 167056
- space_id (=): 167056
- created_at (sort): 1

Existing indexes
- id PRIMARY
- (((metadata -> 'boarding_pass'::text) ->> 'encoded'::text)) WHERE metadata ? 'boarding_pass'::text UNIQUE
- counter_id
- serial, branch_session_id, service_id, space_id UNIQUE
- service_id WHERE state = 'waiting'::text
- space_id, ((((data -> 'slot'::text) -> 'start_time_ts'::text))::integer) WHERE data ? 'slot'::text
- space_id, ((id)::text), lower(code), lower(label)
- space_id, branch_id, service_id, (((data -> 'slot'::text) ->> 'id'::text)), ((((data -> 'slot'::text) -> 'start_time_ts'::text))::integer) WHERE data ? 'slot'::text
- space_id, created_at
- space_id, searchable GIN
- space_id, service_id, branch_session_id
- to_timestamp(((((data -> 'slot'::text) -> 'end_time_ts'::text))::numeric)::double precision) WHERE (data ? 'email'::text) AND (data ? 'slot'::text) AND ((data ->> 'email'::text) <> ''::text)
- to_timestamp(((((data -> 'slot'::text) -> 'end_time_ts'::text))::numeric)::double precision) WHERE (data ? 'email'::text) AND (data ? 'slot'::text) AND ((data ->> 'email'::text) <> ''::text)
24 min < 0.1% 4 ms 365,799 postgres 
select "integrations".* from "integrations" where "space_id" = $1 and "enabled" = $2 and "type" = $3
24 min < 0.1% 1 ms 1,022,343 postgres 
SELECT *
      FROM flight_airline
      WHERE space_id = $1
      AND replace(iata_code, $3, $4) = $2
      LIMIT $5
23 min < 0.1% 1 ms 1,205,022 postgres 
UPDATE jobs
    SET next_run = $2, updated_at = now()
    WHERE status = $3
    AND dashboard_id IN (
      SELECT dashboard_id
      FROM datasets
      WHERE type = $4
      AND space_id = $1
    )
19 min < 0.1% 0 ms 6,442,901 postgres 
update "jobs" set "status" = $1, "error" = $2, "updated_at" = $3 where "id" = $4 returning *
Covered by index on (id)
Rows: 39069
Row progression: 39069, 1

Row estimates
- id (=): 1

Existing indexes
- id PRIMARY
- dashboard_id
- next_run WHERE ((status = 'failed'::text) AND (attempts < 3)) OR (status = 'pending'::text)
- space_id, command, dashboard_id
17 min < 0.1% 1 ms 1,379,114 postgres 
select "notifications".* from "notifications" where "space_id" = $1 and "id" = $2 and "state" = $3
Covered by index on (id)
Rows: 175749
Row progression: 175749, 1

Row estimates
- id (=): 1
- state (=): 58583
- space_id (=): 87875

Existing indexes
- id PRIMARY
- created_at WHERE (state = 'undelivered'::text) AND (NOT (type = 'terminal'::text))
- space_id, type
15 min < 0.1% 9,093 ms 101 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 44c88dda0805552c6ad86dad6b3ffdb0c216d38f0dd2cbb0a79ae44e6890dd00
(
  select
    $1 as " ",
    $2 as "Day",
    to_char(date_trunc($3, avg(to_timestamp((data->$4->>$5)::integer) - to_timestamp((data->$6->>$7)::integer))), $8) as "Avg Time To STD",
    sum((data->>$9)::integer) as "Total",
    sum((data->>$10)::integer) filter (where to_timestamp((data->$11->>$12)::integer) < now()) as "Completed",
    sum((data->>$13)::integer) filter (where to_timestamp((data->$14->>$15)::integer) >= now()) as "Waiting"
  from tickets
  where space_id = $16 and data ? $17
)
union all
(
  select
    (case when (to_timestamp((data->$18->>$19)::integer)::date = now()::date) then $20 else $21 end) as "\",
    to_char(to_timestamp((data->$22->>$23)::integer)::date, $24) as day,
    to_char(date_trunc($25, avg(to_timestamp((data->$26->>$27)::integer) - to_timestamp((data->$28->>$29)::integer))), $30) as "avg time to dep",
    coalesce(sum((data->>$31)::integer), $32) as total,
    coalesce(sum((data->>$33)::integer) filter (where to_timestamp((data->$34->>$35)::integer) < now()), $36) as completed,
    coalesce(sum((data->>$37)::integer) filter (where to_timestamp((data->$38->>$39)::integer) >= now()), $40) as waiting
  from tickets
  where space_id = $41 and data ? $42
  group by to_timestamp((data->'slot'->>'start_time_ts')::integer)::date
  order by to_timestamp((data->$43->>$44)::integer)::date desc
)
15 min < 0.1% 0 ms 21,164,269 postgres 
SELECT *
      FROM applications
      WHERE referer IS NOT NULL
      AND $1 ^@ referer
14 min < 0.1% 0 ms 17,801,795 postgres 
select "branches".* from "branches" where "id" = $1
14 min < 0.1% 0 ms 4,786,441 postgres 
update "jobs" set "status" = $1, "next_run" = $2, "last_run_ts" = $3, "last_run_duration" = $4, "attempts" = $5, "updated_at" = $6 where "id" = $7 returning *
Covered by index on (id)
Rows: 39069
Row progression: 39069, 1

Row estimates
- id (=): 1

Existing indexes
- id PRIMARY
- dashboard_id
- next_run WHERE ((status = 'failed'::text) AND (attempts < 3)) OR (status = 'pending'::text)
- space_id, command, dashboard_id
14 min < 0.1% 3,790 ms 216 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 0cf8f43b953255c50e73b158826e13b02833a77766c00a75a9bf69cb70915d1a
with hs as (
    select generate_series($1, $2, $3) as h
),
data as (
    select
        extract($4 from to_timestamp((data->$5->>$6)::integer) at time zone $7) as h,
        data
    from tickets
    where space_id = $8 and (to_timestamp((data->$9->>$10)::integer) at time zone $11)::date = (now() at time zone $12)::date - $13::interval
)
select 
    hs.h as "Hour",
    coalesce(sum((data->>$14)::integer) filter (where data ? $15), $16) as "Booking",
    coalesce(sum((data->>$17)::integer) filter (where data ? $18), $19) as "Scan"
from hs
left join data
on hs.h = data.h
group by hs.h
order by hs.h asc
13 min < 0.1% 0 ms 4,653,542 postgres 
INSERT INTO flight_schedule (space_id, dep_time_ts, dep_iata, arr_iata, airline_iata, flight_iata, data, provider, enabled, created_at, updated_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, now(), now())
        ON CONFLICT (space_id, (provider->>$10), (provider->>$11))
        DO UPDATE SET
          dep_time_ts = EXCLUDED.dep_time_ts,
          dep_iata = EXCLUDED.dep_iata,
          arr_iata = EXCLUDED.arr_iata,
          airline_iata = EXCLUDED.airline_iata,
          data = EXCLUDED.data,
          provider = EXCLUDED.provider,
          updated_at = EXCLUDED.updated_at,
          enabled = EXCLUDED.enabled
13 min < 0.1% 468 ms 1,688 postgres 
SELECT
      (data->$1->$2)::numeric AS slot_id,
      jsonb_build_object(
        $3, count($4) filter (where state = $5),
        $6, coalesce(sum((data->$7)::numeric) filter (where state = $8 and (data->$9->$10)::numeric > extract($11 from now())), $12),
        $13, coalesce(sum((data->$14)::numeric) filter (where state = $15 and (data->$16->$17)::numeric <= extract($18 from now())), $19)
      ) as stats
    FROM tickets
    WHERE space_id = $20
      AND branch_id = $21
      AND service_id = $22
      AND data ? $23
    and state in ($24, $25)
    and (data->$26->$28)::numeric between extract($30 from now()) - $31*$32*$33 and extract($34 from now()) + $35*$36*$37
    group by 1
13 min < 0.1% 1 ms 778,119 postgres 
-- lookup integration
      SELECT *
      FROM integrations
      WHERE space_id = $1
      AND (id::text = $2::text OR type = $3::text)
      LIMIT $4
13 min < 0.1% 0 ms 21,164,335 postgres 
select "applications".* from "applications" where "api_key" = $1
12 min < 0.1% 8,383 ms 85 postgres 
-- Metabase:: userID: 1 queryType: native queryHash: 3a0604cd5314ff31ccaae53e2f6f6104821515a9dcbe39a3ab5873679bdf4b0f
select sum((data->$1)::numeric) from ticket_history
where space_id = $2
and state = $3
and next_state is null
Covered by index on (state)
Rows: 1269427
Row progression: 1269427, 253885, 94792

Row estimates
- state (=): 253885
- next_state (null): 473962
- space_id (=): 634714

Existing indexes
- id PRIMARY
- branch_session_id, counter_id
- branch_session_id, service_id
- branch_session_id, state
- branch_session_id, user_id
- session_id, branch_session_id
- space_id
- space_id, branch_session_id, service_id, serial DESC
- state
- ticket_id
- tstzrange(created_at, next_created_at, '[]'::text) GIST
12 min < 0.1% 0 ms 41,428,834 postgres 
select id, enabled from spaces where name = $1
11 min < 0.1% 6,579 ms 102 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: dc65aa15f20b4d21be8b3f87fd4543d607390bd0cec0c18fb3fde0ed08b02ee5
select (data->$1->>$2)::text || $3 ||  (data->$4->>$5)::text as "Airport", sum((data->>$6)::integer) as "Count"
from tickets
where space_id = $7 and data ? $8 and state = $9
group by 1
order by 2 desc
limit $10
11 min < 0.1% 2 ms 400,974 postgres 
INSERT INTO ticket_history (
          ticket_id,
          label,
          serial,
          code,
          transition,
          state,
          branch_id,
          branch,
          branch_session_id,
          service_id,
          counter_id,
          counter,
          user_id,
          "user",
          metadata,
          first_created_at,
          created_at,
          priority,
          session_id,
          evaluate_at,
          service,
          data,
          sm_transition,
          reason,
          available_actions,
          form,
          space_id,
          stats,
          tz,
          path,
          appointment_id
        )
        SELECT
          NEW.id,
          NEW.label,
          NEW.serial,
          NEW.code,
          NEW.transition,
          NEW.state,
          NEW.branch_id,
          CASE WHEN NEW.branch IS NULL THEN $49
          ELSE jsonb_strip_nulls(jsonb_build_object(
            $50, NEW.branch->>$51,
            $52, NEW.branch->>$53,
            $54, NEW.branch->>$55,
            $56, NEW.branch->>$57,
            $58, NEW.branch->>$59,
            $60, NEW.branch->>$61
          ))
          END,
          NEW.branch_session_id,
          NEW.service_id,
          NEW.counter_id,
          CASE WHEN NEW.counter IS NULL THEN $62
          ELSE jsonb_strip_nulls(jsonb_build_object(
            $63, NEW.counter->>$64,
            $65, NEW.counter->>$66,
            $67, NEW.counter->>$68
          ))
          END,
          NEW.user_id,
          NEW.user,
          NEW.metadata,
          NEW.created_at,
          NEW.updated_at,
          NEW.priority,
          NEW.session_id,
          NEW.evaluate_at,
          CASE WHEN NEW.service IS NULL THEN $69
          ELSE jsonb_strip_nulls(
            jsonb_build_object(
              $70, NEW.service->>$71,
              $72, NEW.service->>$73,
              $74, NEW.service->>$75,
              $76, NEW.service->>$77,
              $78, NEW.service->>$79,
              $80, NEW.service->>$81
          ))
          END,
          NEW.data,
          NEW.sm_transition,
          NEW.reason,
          NEW.available_actions,
          NEW.form,
          NEW.space_id,
          NEW.stats,
          NEW.tz,
          NEW.path,
          NEW.appointment_id
11 min < 0.1% 163,006 ms 4 postgres 
vacuum analyse notifications
11 min < 0.1% 392 ms 1,664 postgres 
SELECT tickets.data->$1->>$2 AS slot_id, count($3) as count, sum(coalesce(tickets.data->>$4, $5)::int) AS party_size
    FROM tickets
    WHERE space_id = $6
      AND branch_id = $7
      AND service_id = $8
      AND data ? $9
      AND (data->$10->$11)::int > extract($12 from now())
      AND state NOT IN ($13)
    GROUP BY 1
11 min < 0.1% 6,351 ms 102 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 325e36fd131985e485614b1df819c41f37d0ea0d5aad3105af0c5171975090b8
select data->$1->>$2 as "Flight", data->$3->>$4 as "Airline", data->$5->>$6 as "Destination", count($7)
from tickets
where space_id = $8 and data ? $9 and state = $10
group by 1, 2, 3
order by 4 desc
limit $11
11 min < 0.1% 1 ms 1,099,261 postgres 
UPDATE ticket_history
      SET next_state = NEW.state, next_created_at = NEW.updated_at
      WHERE ticket_id = NEW.id AND next_state IS NULL
Covered by index on (ticket_id)
Rows: 1269427
Row progression: 1269427, 5

Row estimates
- ticket_id (=): 5
- next_state (null): 473962

Existing indexes
- id PRIMARY
- branch_session_id, counter_id
- branch_session_id, service_id
- branch_session_id, state
- branch_session_id, user_id
- session_id, branch_session_id
- space_id
- space_id, branch_session_id, service_id, serial DESC
- state
- ticket_id
- tstzrange(created_at, next_created_at, '[]'::text) GIST
11 min < 0.1% 2 ms 400,974 postgres 
INSERT INTO ticket_archive (id, state, branch_session_id, service_id, counter_id, user_id, "user", metadata, created_at, priority, serial, label, transition, branch_id, session_id, evaluate_at, service, space_id, source, code, data, sm_transition, reason, available_actions, stats, form, counter, branch, searchable, queue_id, tz, path, updated_at, notes, appointment_id)
        VALUES (NEW.id, NEW.state, NEW.branch_session_id, NEW.service_id, NEW.counter_id, NEW.user_id, NEW.user, NEW.metadata, NEW.created_at, NEW.priority, NEW.serial, NEW.label, NEW.transition, NEW.branch_id, NEW.session_id, NEW.evaluate_at, NEW.service, NEW.space_id, NEW.source, NEW.code, NEW.data, NEW.sm_transition, NEW.reason, NEW.available_actions, NEW.stats, NEW.form, NEW.counter, NEW.branch, NEW.searchable, NEW.queue_id, NEW.tz, NEW.path, NEW.updated_at, NEW.notes, NEW.appointment_id)
        ON CONFLICT (id) DO UPDATE
        SET
          state = NEW.state,
          branch_session_id = NEW.branch_session_id,
          service_id = NEW.service_id,
          counter_id = NEW.counter_id,
          user_id = NEW.user_id,
          "user" = NEW.user,
          metadata = NEW.metadata,
          created_at = NEW.created_at,
          priority = NEW.priority,
          serial = NEW.serial,
          label = NEW.label,
          transition = NEW.transition,
          branch_id = NEW.branch_id,
          session_id = NEW.session_id,
          evaluate_at = NEW.evaluate_at,
          service = NEW.service,
          space_id = NEW.space_id,
          source = NEW.source,
          code = NEW.code,
          data = NEW.data,
          sm_transition = NEW.sm_transition,
          reason = NEW.reason,
          available_actions = NEW.available_actions,
          stats = NEW.stats,
          form = NEW.form,
          counter = NEW.counter,
          branch = NEW.branch,
          searchable = NEW.searchable,
          queue_id = NEW.queue_id,
          tz = NEW.tz,
          path = NEW.path,
          notes = NEW.notes,
          appointment_id = NEW.appointment_id,
          updated_at = NEW.updated_at
10 min < 0.1% 6,120 ms 102 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: ee5b470143f7bfca9507ef3b57165f45efa89ccd011afb67557546aedbbb3b56
select (data->>$1)::integer as "Party Size", count(id) as "Count"
from tickets
where space_id = $2 and data ? $3 and state = $4 and data ? $5
group by (data->>'party_size')::integer
order by (data->>'party_size')::integer desc
10 min < 0.1% 6,080 ms 102 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 94041d63db24e8eb64765e149673715d28866676b885f9b8a29b5f8023259388
select data->$1->>$2 as "Airline", sum((data->>$3)::integer) as "Count"
from tickets
where space_id = $4 and data ? $5 and state = $6
group by 1
order by 2 desc
limit $7
10 min < 0.1% 0 ms 3,014,536 postgres 
INSERT INTO flight_schedule (space_id, dep_time_ts, dep_iata, arr_iata, airline_iata, flight_iata, data, provider, enabled, created_at, updated_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, now(), now())
        ON CONFLICT (space_id, (provider->>$10), (provider->>$11))
        DO UPDATE SET data = EXCLUDED.data, provider = EXCLUDED.provider, updated_at = EXCLUDED.updated_at, enabled = EXCLUDED.enabled
9 min < 0.1% 11 ms 50,138 postgres 
-- tickets-states-stats
    WITH data AS (
      SELECT    extract($2 from to_timestamp(lower(tr))::timestamptz at time zone $3) as val,
                VALUES AS tickets
      FROM      ticket_stats
      WHERE     space_id = $1
      AND       (keys->>$4)::integer in ($5)
      AND       tr <@ numrange($6, $7)
      GROUP BY  val, tickets
    ),
    data_aggregated AS (
      SELECT    val, key, sum(value::numeric) agg_tickets
      FROM      data, jsonb_each_text(tickets)
      GROUP BY  val, key
    ),
    data2 AS (
      SELECT    data_aggregated.val as val, json_object_agg(data_aggregated.key, data_aggregated.agg_tickets) as tickets
      FROM      data_aggregated
      GROUP BY  data_aggregated.val
    ),
    final_data AS (
      SELECT  jsonb_agg(JSONB_BUILD_OBJECT($8, data2.val, $9, data2.tickets)) AS unit_count,
              $10 AS time_interval
      FROM    data2
    )
    SELECT json_agg(final_data)::jsonb as aggdata FROM final_data
9 min < 0.1% 5,387 ms 104 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 59575cefbb7f895726aaaa2b759f7697a3a72a2e8209de139eb8a58fe77849e5
select extract($1 from date_trunc($2, to_timestamp((data->$3->>$4)::integer) - created_at)) as "Days", count($5) as "Count"
from tickets
where space_id = $6 and data ? $7 and state = $8
group by 1
order by 1 desc
9 min < 0.1% 4,728 ms 108 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 8618abccbfaa361df9de8ed1b9e4edaa4d3deb1f04a2762b527597a2a361d3e1
with xs as (
    select generate_series(
        date_trunc($1, now() at time zone $2) - $3::interval,
        date_trunc($4, now() at time zone $5),
        $6::interval) as x
),
ds as (
    select
        date_trunc($7, to_timestamp((data->$8->>$9)::integer) at time zone $10) as d,
        data
    from tickets
    where space_id = $11
)
select
    to_char(xs.x, $12) as "Day",
    coalesce(sum((ds.data->>$13)::integer) filter (where ds.data ? $14), $15) as "Booking",
    coalesce(sum((ds.data->>$16)::integer) filter (where ds.data ? $17), $18) as "Scan"
from xs
left join ds on xs.x = ds.d
group by xs.x
order by xs.x asc
8 min < 0.1% 4,666 ms 108 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: 92da4ef3ca66d8382a327be5eeea0e7085795d18305fc2c9b2d409974e5a82ce
with data as (
select
    data->>$1,
    created_at at time zone $2 as scan_time,
    to_timestamp((data->$3->>$4)::integer) at time zone $5 as slot_end_time,
    to_timestamp((data->$6->>$7)::integer) at time zone $8 as std,
    data->$9->>$10 as flight,
    (data->$11->>$12) || $13 || (data->$14->>$15) as dst,
    (data->>$16)::integer as size
from tickets
where space_id = $17 and data ? $18 and (created_at at time zone $19)::date = (now() at time zone $20)::date
order by created_at desc
)
select
    to_char(scan_time, $21) as "Scan Time",
    to_char(slot_end_time - scan_time, $22) as "Time to Slot Close",
    to_char(std - scan_time, $23) as "Time to Departure",
    flight as "Flight",
    dst as "Destination",
    size as "Party Size"
from data
8 min < 0.1% 4,333 ms 105 postgres 
-- Metabase:: userID: 4 queryType: native queryHash: a28e18dec5175faed2830133055247b47a3170faba9ae6d4e113397797e5710e
select
    to_char(created_at at time zone $1, $2) as "Scan Time",
    to_char(to_timestamp((data->$3->>$4)::integer) at time zone $5, $6) as "Slot Time",
    round(date_part($7, to_timestamp((data->$8->>$9)::integer) - created_at) / $10) as "Hours Apart",
    data->$11->>$12 as "Flight",
    id as "Database ID"
from tickets
where space_id = $13 and
    data ? $14
    and (to_timestamp((data->$15->>$16)::integer) - created_at) > $17
order by to_timestamp((data->$18->>$19)::integer) - created_at desc
7 min < 0.1% 5 ms 87,741 postgres 
update "tickets" set "data" = $1, "updated_at" = $2 where "id" = $3 returning *
Covered by index on (id)
Rows: 334112
Row progression: 334112, 1

Row estimates
- id (=): 1

Existing indexes
- id PRIMARY
- (((metadata -> 'boarding_pass'::text) ->> 'encoded'::text)) WHERE metadata ? 'boarding_pass'::text UNIQUE
- counter_id
- serial, branch_session_id, service_id, space_id UNIQUE
- service_id WHERE state = 'waiting'::text
- space_id, ((((data -> 'slot'::text) -> 'start_time_ts'::text))::integer) WHERE data ? 'slot'::text
- space_id, ((id)::text), lower(code), lower(label)
- space_id, branch_id, service_id, (((data -> 'slot'::text) ->> 'id'::text)), ((((data -> 'slot'::text) -> 'start_time_ts'::text))::integer) WHERE data ? 'slot'::text
- space_id, created_at
- space_id, searchable GIN
- space_id, service_id, branch_session_id
- to_timestamp(((((data -> 'slot'::text) -> 'end_time_ts'::text))::numeric)::double precision) WHERE (data ? 'email'::text) AND (data ? 'slot'::text) AND ((data ->> 'email'::text) <> ''::text)
- to_timestamp(((((data -> 'slot'::text) -> 'end_time_ts'::text))::numeric)::double precision) WHERE (data ? 'email'::text) AND (data ? 'slot'::text) AND ((data ->> 'email'::text) <> ''::text)
7 min < 0.1% 1 ms 365,505 postgres 
insert into "incidents" ("branch_id", "created_at", "data", "slug", "space_id", "status", "type", "updated_at") values ($1, $2, $3, $4, $5, $6, $7, $8) returning *
7 min < 0.1% 2,955 ms 143 postgres 
-- Metabase
select          to_char(to_timestamp((data->$5->>$6)::integer) at time zone $7, $8) as "Date",
                to_char(to_timestamp((data->$9->>$10)::integer) at time zone $11, $12) as "Slot Time",
                data->$13->>$14 as "Flight Number",
                (data->$15->>$16) || $17 || (data->$18->>$19) as "Destination",
                to_char(to_timestamp((data->$20->>$21)::integer) at time zone $22, $23) as "Departure Time",
                to_char(created_at at time zone $24, $25) as "Registration",
                case when state = $26 then to_char(updated_at at time zone $27, $28) else $29 end "Access time",
                case when state = $30 and now() > to_timestamp((data->$31->>$32)::integer) then $33 when state = $34 then $35 else $36 end as "Status",
                (data->>$37)::integer as "Party Size"--,
                --overlay(data->>'email' placing repeat('*', position('@' in data->>'email') - 3) from 2 for position('@' in data->>'email') - 3)  as "Email"
from            tickets
where           space_id = $38
and             data ? $39
and             to_timestamp((data->$40->>$41)::integer)::date >= $1
and             to_timestamp((data->$42->>$43)::integer)::date >= $2
and             to_timestamp((data->$44->>$45)::integer)::date <= $3
and             to_timestamp((data->$46->>$47)::integer)::date <= $4

order by        (data->$48->>$49)::integer desc