explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kHTU

Settings
# exclusive inclusive rows x rows loops node
1. 44.716 25,086.724 ↓ 49.5 61,806 1

Hash Left Join (cost=557,059.76..558,041.55 rows=1,248 width=47) (actual time=24,949.557..25,086.724 rows=61,806 loops=1)

  • Output: t.ticket_id, COALESCE(lkup_payment_types.payment_type_id, $0), l.currency_id, COALESCE(l_1.card_type_id, '-1'::integer), s.transaction_date, COALESCE(lkup_dates.date_id, '-1'::integer), (s.payment_value)::double precision, s.entry_mode, t.journey_id
  • Hash Cond: ((s.card_type_code)::text = (l_1.code)::text)
  • Buffers: shared hit=66475 read=123631 written=3092, temp read=3842 written=3259
2.          

Initplan (for Hash Left Join)

3. 0.002 0.008 ↑ 1.0 1 1

Limit (cost=0.00..1.32 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)

  • Output: o.payment_type_id
  • Buffers: shared hit=1
4. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on scv.lkup_payment_types o (cost=0.00..1.32 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)

  • Output: o.payment_type_id
  • Filter: ((o.name)::text = 'Unknown'::text)
  • Buffers: shared hit=1
5. 22.694 25,041.723 ↓ 49.5 61,806 1

Hash Left Join (cost=557,042.14..558,003.64 rows=1,248 width=44) (actual time=24,949.251..25,041.723 rows=61,806 loops=1)

  • Output: s.transaction_date, s.payment_value, s.entry_mode, s.card_type_code, t.ticket_id, t.journey_id, l.currency_id, lkup_payment_types.payment_type_id, lkup_dates.date_id
  • Hash Cond: ((s.payment_type)::text = (lkup_payment_types.name)::text)
  • Buffers: shared hit=66474 read=123630 written=3092, temp read=3842 written=3259
6. 32.353 25,018.717 ↓ 49.5 61,806 1

Hash Left Join (cost=557,040.55..557,983.34 rows=1,248 width=61) (actual time=24,948.929..25,018.717 rows=61,806 loops=1)

  • Output: s.transaction_date, s.payment_value, s.entry_mode, s.payment_type, s.card_type_code, t.ticket_id, t.journey_id, l.currency_id, lkup_dates.date_id
  • Hash Cond: ((s.currency_type)::text = (l.code)::text)
  • Buffers: shared hit=66474 read=123629 written=3092, temp read=3842 written=3259
7. 52.429 24,985.839 ↓ 49.5 61,806 1

Merge Right Join (cost=557,024.25..557,949.88 rows=1,248 width=61) (actual time=24,948.387..24,985.839 rows=61,806 loops=1)

  • Output: s.transaction_date, s.payment_value, s.entry_mode, s.currency_type, s.payment_type, s.card_type_code, t.ticket_id, t.journey_id, lkup_dates.date_id
  • Merge Cond: (lkup_dates.the_date = s.transaction_date)
  • Buffers: shared hit=66474 read=123628 written=3092, temp read=3842 written=3259
8. 196.429 320.494 ↑ 1.0 175,809 1

Sort (cost=27,106.96..27,566.33 rows=183,749 width=12) (actual time=280.032..320.494 rows=175,809 loops=1)

  • Output: lkup_dates.date_id, lkup_dates.the_date
  • Sort Key: lkup_dates.the_date
  • Sort Method: external merge Disk: 4688kB
  • Buffers: shared read=708 written=460, temp read=586 written=588
9. 66.998 124.065 ↑ 1.0 183,749 1

Group (cost=0.42..6,060.03 rows=183,749 width=12) (actual time=2.028..124.065 rows=183,749 loops=1)

  • Output: lkup_dates.date_id, lkup_dates.the_date
  • Group Key: lkup_dates.date_id
  • Buffers: shared read=708 written=460
10. 57.067 57.067 ↑ 1.0 183,749 1

Index Only Scan using idx_lkup_dates_date_id_date on scv.lkup_dates (cost=0.42..5,600.65 rows=183,749 width=12) (actual time=2.024..57.067 rows=183,749 loops=1)

  • Output: lkup_dates.date_id, lkup_dates.the_date
  • Heap Fetches: 0
  • Buffers: shared read=708 written=460
11. 142.484 24,612.916 ↓ 49.5 61,806 1

Sort (cost=529,917.29..529,920.41 rows=1,248 width=57) (actual time=24,601.353..24,612.916 rows=61,806 loops=1)

  • Output: s.transaction_date, s.payment_value, s.entry_mode, s.currency_type, s.payment_type, s.card_type_code, t.ticket_id, t.journey_id
  • Sort Key: s.transaction_date
  • Sort Method: external sort Disk: 4392kB
  • Buffers: shared hit=66474 read=122920 written=2632, temp read=3255 written=2671
12. 99.124 24,470.432 ↓ 49.4 61,604 1

Merge Join (cost=528,738.13..529,853.11 rows=1,248 width=57) (actual time=24,337.606..24,470.432 rows=61,604 loops=1)

  • Output: s.transaction_date, s.payment_value, s.entry_mode, s.currency_type, s.payment_type, s.card_type_code, t.ticket_id, t.journey_id
  • Merge Cond: ((t.ticket_number)::text = (s.ticketno)::text)
  • Buffers: shared hit=66474 read=122920 written=2632, temp read=2156 written=1572
13. 1,165.108 23,757.618 ↑ 3.3 59,792 1

Sort (cost=525,586.90..526,074.84 rows=195,173 width=17) (actual time=23,737.318..23,757.618 rows=59,792 loops=1)

  • Output: t.ticket_id, t.journey_id, t.ticket_number
  • Sort Key: t.ticket_number
  • Sort Method: external merge Disk: 1592kB
  • Buffers: shared hit=66170 read=122163 written=2319, temp read=976 written=981
14. 14.908 22,592.510 ↑ 3.3 59,792 1

Subquery Scan on t (cost=481,674.74..504,432.67 rows=195,173 width=17) (actual time=22,240.743..22,592.510 rows=59,792 loops=1)

  • Output: t.ticket_id, t.journey_id, t.ticket_number
  • Buffers: shared hit=66170 read=122163 written=2319, temp read=777 written=781
15. 75.653 22,577.602 ↑ 3.3 59,792 1

Group (cost=481,674.74..502,480.94 rows=195,173 width=737) (actual time=22,240.741..22,577.602 rows=59,792 loops=1)

  • Output: c.transaction_id, NULL::integer, c.ticket_id, NULL::integer, NULL::timestamp without time zone, NULL::integer, NULL::character varying(26), NULL::character varying(255), NULL::character varying(1), NULL::character varying(1), NULL::character varying(3), NULL::numeric(8,2), NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::integer, NULL::character varying(2), NULL::integer, t_1.ticket_number, j.journey_id
  • Group Key: c.transaction_id, t_1.ticket_number, j.journey_id
  • Buffers: shared hit=66170 read=122163 written=2319, temp read=777 written=781
16. 1,044.161 22,501.949 ↓ 1.1 174,660 1

Gather Merge (cost=481,674.74..501,261.11 rows=162,644 width=21) (actual time=22,240.740..22,501.949 rows=174,660 loops=1)

  • Output: c.transaction_id, t_1.ticket_number, j.journey_id, c.ticket_id
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=204747 read=357892 written=3059, temp read=2312 written=2323
17. 58.262 21,457.788 ↑ 1.4 58,220 3 / 3

Group (cost=480,674.71..481,487.93 rows=81,322 width=21) (actual time=21,333.777..21,457.788 rows=58,220 loops=3)

  • Output: c.transaction_id, t_1.ticket_number, j.journey_id, c.ticket_id
  • Group Key: c.transaction_id, t_1.ticket_number, j.journey_id
  • Buffers: shared hit=204747 read=357892 written=3059, temp read=2312 written=2323
  • Worker 0: actual time=20879.939..21000.730 rows=57524 loops=1
  • Buffers: shared hit=69314 read=116956 written=367, temp read=756 written=759
  • Worker 1: actual time=20881.847..21006.645 rows=58590 loops=1
  • Buffers: shared hit=69263 read=118773 written=373, temp read=779 written=783
18. 264.745 21,399.526 ↓ 2.3 184,868 3 / 3

Sort (cost=480,674.71..480,878.02 rows=81,322 width=21) (actual time=21,333.775..21,399.526 rows=184,868 loops=3)

  • Output: c.transaction_id, t_1.ticket_number, j.journey_id, c.ticket_id
  • Sort Key: c.transaction_id, t_1.ticket_number, j.journey_id
  • Sort Method: external merge Disk: 6216kB
  • Buffers: shared hit=204747 read=357892 written=3059, temp read=2312 written=2323
  • Worker 0: actual time=20879.936..20943.805 rows=181324 loops=1
  • Buffers: shared hit=69314 read=116956 written=367, temp read=756 written=759
  • Worker 1: actual time=20881.845..20947.337 rows=186846 loops=1
  • Buffers: shared hit=69263 read=118773 written=373, temp read=779 written=783
19. 2,339.999 21,134.781 ↓ 2.3 184,868 3 / 3

Hash Join (cost=15,381.82..474,042.35 rows=81,322 width=21) (actual time=1,929.156..21,134.781 rows=184,868 loops=3)

  • Output: c.transaction_id, t_1.ticket_number, j.journey_id, c.ticket_id
  • Hash Cond: ((p.ticketno)::text = (t_1.ticket_number)::text)
  • Buffers: shared hit=204725 read=357890 written=3059
  • Worker 0: actual time=2240.928..20665.404 rows=181324 loops=1
  • Buffers: shared hit=69302 read=116956 written=367
  • Worker 1: actual time=888.850..20691.586 rows=186846 loops=1
  • Buffers: shared hit=69253 read=118771 written=373
20. 17,820.988 17,820.988 ↑ 1.2 6,032,746 3 / 3

Parallel Seq Scan on etl.payments p (cost=0.00..429,579.86 rows=7,537,986 width=9) (actual time=0.260..17,820.988 rows=6,032,746 loops=3)

  • Output: p.id, p.sale_id, p.ticketno, p.transaction_date, p.payment_type, p.payment_value, p.currency_type, p.card_type_code, p.card_description, p.entry_mode, p.etl_batch_file_id, p.imported, p.created_at, p.updated_at, p.customer_id
  • Buffers: shared read=354200 written=40
  • Worker 0: actual time=0.051..17807.029 rows=5965295 loops=1
  • Buffers: shared read=116773 written=12
  • Worker 1: actual time=0.051..17826.045 rows=6057018 loops=1
  • Buffers: shared read=118539 written=11
21. 31.127 973.794 ↑ 1.0 60,490 3 / 3

Hash (cost=14,625.70..14,625.70 rows=60,490 width=21) (actual time=973.794..973.794 rows=60,490 loops=3)

  • Output: c.transaction_id, c.ticket_id, t_1.ticket_number, j.journey_id
  • Buckets: 65536 Batches: 1 Memory Usage: 3821kB
  • Buffers: shared hit=204677 read=3684 written=3019
  • Worker 0: actual time=521.138..521.138 rows=60490 loops=1
  • Buffers: shared hit=69275 read=183 written=355
  • Worker 1: actual time=521.039..521.039 rows=60490 loops=1
  • Buffers: shared hit=69232 read=226 written=362
22. 42.643 942.667 ↑ 1.0 60,490 3 / 3

Hash Join (cost=3,119.38..14,625.70 rows=60,490 width=21) (actual time=92.493..942.667 rows=60,490 loops=3)

  • Output: c.transaction_id, c.ticket_id, t_1.ticket_number, j.journey_id
  • Inner Unique: true
  • Hash Cond: (c.transaction_id = j.transaction_id)
  • Buffers: shared hit=204677 read=3684 written=3019
  • Worker 0: actual time=59.606..490.986 rows=60490 loops=1
  • Buffers: shared hit=69275 read=183 written=355
  • Worker 1: actual time=59.614..489.683 rows=60490 loops=1
  • Buffers: shared hit=69232 read=226 written=362
23. 46.849 836.072 ↑ 1.0 67,286 3 / 3

Merge Join (cost=62.35..11,392.04 rows=67,286 width=17) (actual time=28.145..836.072 rows=67,286 loops=3)

  • Output: c.transaction_id, c.ticket_id, t_1.ticket_number
  • Inner Unique: true
  • Merge Cond: (c.ticket_id = t_1.ticket_id)
  • Buffers: shared hit=201261 read=1990 written=1635
  • Worker 0: actual time=17.417..409.875 rows=67286 loops=1
  • Buffers: shared hit=67568 read=183 written=355
  • Worker 1: actual time=17.657..403.626 rows=67286 loops=1
  • Buffers: shared hit=67525 read=226 written=362
24. 738.379 738.379 ↑ 1.0 67,286 3 / 3

Index Scan using idx_ticket_xactions_ticket_id on scv.fact_ticket_transactions c (cost=0.29..5,640.96 rows=67,286 width=8) (actual time=0.314..738.379 rows=67,286 loops=3)

  • Output: c.transaction_id, c.customer_id, c.ticket_id, c.agent_id, c.sale_date, c.sale_date_id, c.sales_channel, c.transaction_type, c.ticket_type, c.ticket_queue, c.fare_type, c.fare_value, c.adults, c.children, c.concessionary, c.young_person_coach_card, c.eurolines_young_person, c.hm_forces, c.infants, c.eurolines_seniors, c.over_sixties, c.coach_card_id, c.coach_card_consumer_type_id, c.account_id, c.distribution_type, c.ticket_email_id
  • Buffers: shared hit=199749 read=1235 written=996
  • Worker 0: actual time=0.462..332.125 rows=67286 loops=1
  • Buffers: shared hit=66870 read=125 written=254
  • Worker 1: actual time=0.459..325.868 rows=67286 loops=1
  • Buffers: shared hit=66837 read=158 written=273
25. 50.844 50.844 ↑ 82.6 67,916 3 / 3

Index Scan using dim_tickets_pkey on scv.dim_tickets t_1 (cost=0.43..476,826.32 rows=5,606,996 width=13) (actual time=0.615..50.844 rows=67,916 loops=3)

  • Output: t_1.ticket_id, t_1.sale_id, t_1.ticket_number, t_1.sale_date, t_1.sale_date_id
  • Buffers: shared hit=1512 read=755 written=639
  • Worker 0: actual time=0.302..32.835 rows=67916 loops=1
  • Buffers: shared hit=698 read=58 written=101
  • Worker 1: actual time=0.299..30.608 rows=67916 loops=1
  • Buffers: shared hit=688 read=68 written=89
26. 20.730 63.952 ↑ 1.0 60,490 3 / 3

Hash (cost=2,300.90..2,300.90 rows=60,490 width=8) (actual time=63.952..63.952 rows=60,490 loops=3)

  • Output: j.journey_id, j.transaction_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2875kB
  • Buffers: shared hit=3394 read=1694 written=1384
  • Worker 0: actual time=41.715..41.715 rows=60490 loops=1
  • Buffers: shared hit=1696
  • Worker 1: actual time=41.570..41.570 rows=60490 loops=1
  • Buffers: shared hit=1696
27. 43.222 43.222 ↑ 1.0 60,490 3 / 3

Seq Scan on scv.fact_journey j (cost=0.00..2,300.90 rows=60,490 width=8) (actual time=20.806..43.222 rows=60,490 loops=3)

  • Output: j.journey_id, j.transaction_id
  • Buffers: shared hit=3394 read=1694 written=1384
  • Worker 0: actual time=1.720..20.732 rows=60490 loops=1
  • Buffers: shared hit=1696
  • Worker 1: actual time=1.575..20.650 rows=60490 loops=1
  • Buffers: shared hit=1696
28. 552.740 613.690 ↓ 2.6 64,882 1

Sort (cost=3,151.22..3,214.54 rows=25,328 width=58) (actual time=599.812..613.690 rows=64,882 loops=1)

  • Output: s.transaction_date, s.payment_value, s.entry_mode, s.ticketno, s.currency_type, s.payment_type, s.card_type_code
  • Sort Key: s.ticketno
  • Sort Method: external sort Disk: 2360kB
  • Buffers: shared hit=304 read=757 written=313, temp read=885 written=591
29. 60.950 60.950 ↓ 1.3 33,093 1

Index Scan using "index_etl.payments_on_etl_batch_file_id" on etl.payments s (cost=0.44..1,298.68 rows=25,328 width=58) (actual time=1.331..60.950 rows=33,093 loops=1)

  • Output: s.transaction_date, s.payment_value, s.entry_mode, s.ticketno, s.currency_type, s.payment_type, s.card_type_code
  • Index Cond: (s.etl_batch_file_id = 506)
  • Buffers: shared hit=304 read=757 written=313
30. 0.004 0.525 ↑ 70.0 2 1

Hash (cost=14.55..14.55 rows=140 width=20) (actual time=0.525..0.525 rows=2 loops=1)

  • Output: l.currency_id, l.code
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
31. 0.011 0.521 ↑ 70.0 2 1

HashAggregate (cost=11.75..13.15 rows=140 width=20) (actual time=0.520..0.521 rows=2 loops=1)

  • Output: l.currency_id, l.code
  • Group Key: l.currency_id
  • Buffers: shared read=1
32. 0.510 0.510 ↑ 70.0 2 1

Seq Scan on scv.lkup_currencys l (cost=0.00..11.40 rows=140 width=20) (actual time=0.509..0.510 rows=2 loops=1)

  • Output: l.currency_id, l.code, l.name
  • Buffers: shared read=1
33. 0.026 0.312 ↑ 1.0 26 1

Hash (cost=1.26..1.26 rows=26 width=16) (actual time=0.312..0.312 rows=26 loops=1)

  • Output: lkup_payment_types.payment_type_id, lkup_payment_types.name
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared read=1
34. 0.286 0.286 ↑ 1.0 26 1

Seq Scan on scv.lkup_payment_types (cost=0.00..1.26 rows=26 width=16) (actual time=0.282..0.286 rows=26 loops=1)

  • Output: lkup_payment_types.payment_type_id, lkup_payment_types.name
  • Buffers: shared read=1
35. 0.008 0.277 ↑ 140.0 1 1

Hash (cost=14.55..14.55 rows=140 width=20) (actual time=0.277..0.277 rows=1 loops=1)

  • Output: l_1.card_type_id, l_1.code
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
36. 0.007 0.269 ↑ 140.0 1 1

HashAggregate (cost=11.75..13.15 rows=140 width=20) (actual time=0.268..0.269 rows=1 loops=1)

  • Output: l_1.card_type_id, l_1.code
  • Group Key: l_1.card_type_id
  • Buffers: shared read=1
37. 0.262 0.262 ↑ 140.0 1 1

Seq Scan on scv.lkup_card_types l_1 (cost=0.00..11.40 rows=140 width=20) (actual time=0.261..0.262 rows=1 loops=1)

  • Output: l_1.card_type_id, l_1.code, l_1.description
  • Buffers: shared read=1
Planning time : 13.267 ms