explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ildw

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=414,823.31..414,834.45 rows=297 width=60) (actual rows= loops=)

  • Group Key: cte.ticket_id, cte.ticket_type_dwid, cte.has_bag, cte.is_after_short_layover, cte.deadline_timestamp_utc, cte.resolved_before_deadline_second, cte.has_boarding_pass_sent, ((cte.creation_timestamp_utc)::date)
2.          

CTE cte

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=412,768.19..412,791.95 rows=297 width=90) (actual rows= loops=)

  • Group Key: f_ta.ticket_id, f_ta.creation_timestamp_utc, b_t_1.flight_id, b_t_1.bid, d_tt.ticket_type_dwid, (((t.additional_data ->> 'has_bags_due_checkin'::text))::boolean), (((t.additional_data ->> 'is_after_short_layover'::text))::boolean), (to_timestamp((((t.additional_data ->> 'deadline_utc'::text))::numeric)::double precision)), (((((t.additional_data ->> 'deadline_utc'::text))::numeric)::double precision - date_part('epoch'::text, f_ta.resolution_timestamp_utc)))
4. 0.000 0.000 ↓ 0.0

Sort (cost=412,768.19..412,768.93 rows=297 width=96) (actual rows= loops=)

  • Sort Key: f_ta.ticket_id, f_ta.creation_timestamp_utc, b_t_1.flight_id, b_t_1.bid, d_tt.ticket_type_dwid, (((t.additional_data ->> 'has_bags_due_checkin'::text))::boolean), (((t.additional_data ->> 'is_after_short_layover'::text))::boolean), (to_timestamp((((t.additional_data ->> 'deadline_utc'::text))::numeric)::double precision)), (((((t.additional_data ->> 'deadline_utc'::text))::numeric)::double precision - date_part('epoch'::text, f_ta.resolution_timestamp_utc)))
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.42..412,755.99 rows=297 width=96) (actual rows= loops=)

  • Join Filter: (k.inserted_at >= t.created_at)
6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.11..411,722.69 rows=297 width=267) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..411,489.09 rows=297 width=246) (actual rows= loops=)

  • Join Filter: (f_ta.ticket_type_dwid = d_tt.ticket_type_dwid)
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..409,584.91 rows=21,012 width=246) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on ticketing_db_ticket t (cost=0.00..354,270.25 rows=21,012 width=205) (actual rows= loops=)

  • Filter: (((created_at >= ('2020-08-02 00:00:00+00'::timestamp with time zone - '01:00:00'::interval)) AND (created_at < ('2020-08-02 00:00:00+00'::timestamp with time zone + '24:00:00'::interval))) OR ((updated_at >= ('2020-08-02 00:00:00+00'::timestamp with time zone - '01:00:00'::interval)) AND (updated_at < ('2020-08-02 00:00:00+00'::timestamp with time zone + '24:00:00'::interval))))
10. 0.000 0.000 ↓ 0.0

Index Scan using idx_fact_ticket_id on fact_ticket f_ta (cost=0.56..2.63 rows=1 width=57) (actual rows= loops=)

  • Index Cond: (ticket_id = (t.id)::text)
11. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..16.33 rows=6 width=4) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on dim_ticket_type d_tt (cost=0.00..16.30 rows=6 width=4) (actual rows= loops=)

  • Filter: (ticket_type_group = 'checkin'::text)
13. 0.000 0.000 ↓ 0.0

Index Only Scan using bridge_ticket_x_segment_unique on bridge_ticket_x_segment b_t_1 (cost=0.55..0.78 rows=1 width=58) (actual rows= loops=)

  • Index Cond: (ticket_id = f_ta.ticket_id)
14. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on kiosk_db_files k (cost=2.31..3.42 rows=1 width=31) (actual rows= loops=)

  • Recheck Cond: ((b_t_1.bid = booking_id) AND (b_t_1.flight_id = (segment_id)::text))
15. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=2.31..2.31 rows=1 width=0) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on kiosk_db_files_booking_id_idx (cost=0.00..0.86 rows=22 width=0) (actual rows= loops=)

  • Index Cond: (b_t_1.bid = booking_id)
17. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on kiosk_db_files_segment_id_idx (cost=0.00..1.20 rows=39 width=0) (actual rows= loops=)

  • Index Cond: (b_t_1.flight_id = (segment_id)::text)
18. 0.000 0.000 ↓ 0.0

Sort (cost=2,031.36..2,032.10 rows=297 width=67) (actual rows= loops=)

  • Sort Key: cte.ticket_id, cte.ticket_type_dwid, cte.has_bag, cte.is_after_short_layover, cte.deadline_timestamp_utc, cte.resolved_before_deadline_second, cte.has_boarding_pass_sent, ((cte.creation_timestamp_utc)::date)
19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..2,019.16 rows=297 width=67) (actual rows= loops=)

  • Join Filter: ((cte.flight_id = b_t.flight_id) AND (cte.bid = b_t.bid))
20. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.43..1,844.16 rows=297 width=140) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

CTE Scan on cte (cost=0.00..5.94 rows=297 width=103) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Scan using idx_fact_ticket_bid on fact_ticket f_t (cost=0.43..6.18 rows=1 width=65) (actual rows= loops=)

  • Index Cond: (cte.bid = bid)
  • Filter: ((cte.ticket_id <> ticket_id) AND (cte.creation_timestamp_utc >= creation_timestamp_utc) AND (cte.ticket_type_dwid = ticket_type_dwid) AND (cte.creation_timestamp_utc < COALESCE((resolution_timestamp_utc)::timestamp with time zone, now())))
23. 0.000 0.000 ↓ 0.0

Index Only Scan using bridge_ticket_x_segment_unique on bridge_ticket_x_segment b_t (cost=0.55..0.57 rows=1 width=58) (actual rows= loops=)

  • Index Cond: (ticket_id = f_t.ticket_id)