explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 56QP

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

HashAggregate (cost=443,251.46..443,255.32 rows=309 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=442,507.23..442,531.95 rows=309 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=442,507.23..442,508.00 rows=309 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=154,730.92..442,494.45 rows=309 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=154,730.49..442,179.83 rows=309 width=266) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Join (cost=154,729.94..442,001.14 rows=309 width=245) (actual rows= loops=)

  • Hash Cond: ((t.id)::text = f_ta.ticket_id)
  • Join Filter: (((f_ta.creation_timestamp_utc >= ('2020-08-02 00:00:00+00'::timestamp with time zone - '01:00:00'::interval)) AND (f_ta.creation_timestamp_utc < ('2020-08-02 00:00:00+00'::timestamp with time zone + '24:00:00'::interval))) OR ((t.updated_at >= ('2020-08-02 00:00:00+00'::timestamp with time zone - '01:00:00'::interval)) AND (t.updated_at < ('2020-08-02 00:00:00+00'::timestamp with time zone + '24:00:00'::interval))))
8. 0.000 0.000 ↓ 0.0

Seq Scan on ticketing_db_ticket t (cost=0.00..253,458.02 rows=5,066,002 width=212) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash (cost=153,899.76..153,899.76 rows=66,414 width=57) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash Join (cost=12.50..153,899.76 rows=66,414 width=57) (actual rows= loops=)

  • Hash Cond: (f_ta.ticket_type_dwid = d_tt.ticket_type_dwid)
11. 0.000 0.000 ↓ 0.0

Index Scan using fact_ticket_unique on fact_ticket f_ta (cost=0.56..141,444.97 rows=4,693,225 width=57) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash (cost=11.86..11.86 rows=6 width=4) (actual rows= loops=)

13. 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)
14. 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)
15. 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)
16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..712.56 rows=309 width=67) (actual rows= loops=)

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

Nested Loop Left Join (cost=0.43..531.56 rows=309 width=140) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

CTE Scan on cte (cost=0.00..6.18 rows=309 width=103) (actual rows= loops=)

19. 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())))
20. 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)