|
53,673 min
32%
|
34 ms
|
94,561,704
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,808
postgres
|
SELECT $2 FROM services WHERE id = $1 FOR UPDATE
|
|
10,643 min
6%
|
145 ms
|
4,416,432
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,804
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,542
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,488 min
3%
|
3,065 ms
|
87,864
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,137 min
2%
|
1,659 ms
|
149,621
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,088 min
2%
|
1,888 ms
|
129,915
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,796
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)
|
|
2,722 min
2%
|
9,108 ms
|
17,930
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,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,237
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
|
|
942 min
0.6%
|
58 ms
|
975,011
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,256
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,824
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,761
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,086
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
|
|
75 min
< 0.1%
|
245 ms
|
18,267
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)
|
|
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
|
|
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
|
|
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
|
|
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)
|
|
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
)
|
|
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%
|
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
|
|
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%
|
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
|
|
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
|
|
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%
|
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
|