explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rUv0

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

HashAggregate (cost=376,830.71..376,834.24 rows=282 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=376,151.44..376,174.00 rows=282 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=376,151.44..376,152.14 rows=282 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=1.82..376,139.96 rows=282 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.39..375,852.83 rows=282 width=266) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.83..375,689.76 rows=282 width=245) (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..373,886.84 rows=19,934 width=245) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on ticketing_db_ticket t (cost=0.00..354,860.92 rows=19,934 width=204) (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 fact_ticket_unique on fact_ticket f_ta (cost=0.56..0.95 rows=1 width=57) (actual rows= loops=)

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

Materialize (cost=0.27..11.89 rows=6 width=4) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Index Scan using dim_ticket_type_pkey on dim_ticket_type d_tt (cost=0.27..11.86 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.57 rows=1 width=58) (actual rows= loops=)

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

Index Scan using kiosk_db_files_booking_id_idx on kiosk_db_files k (cost=0.43..0.96 rows=1 width=31) (actual rows= loops=)

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

Nested Loop Left Join (cost=0.98..650.37 rows=282 width=67) (actual rows= loops=)

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

Nested Loop Left Join (cost=0.43..485.18 rows=282 width=140) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

CTE Scan on cte (cost=0.00..5.64 rows=282 width=103) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using idx_fact_ticket_bid on fact_ticket f_t (cost=0.43..1.69 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())))
19. 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)