explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pCRG

Settings
# exclusive inclusive rows x rows loops node
1. 24.731 24,691.170 ↓ 49.4 61,676 1

Unique (cost=558,105.73..558,136.93 rows=1,248 width=47) (actual time=24,656.813..24,691.170 rows=61,676 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
  • Buffers: shared hit=67270 read=121809 written=3010, temp read=4313 written=3731
2.          

Initplan (for Unique)

3. 0.002 0.010 ↑ 1.0 1 1

Limit (cost=0.00..1.32 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)

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

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

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

Sort (cost=558,104.40..558,107.52 rows=1,248 width=47) (actual time=24,656.812..24,666.429 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
  • Sort Key: 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
  • Sort Method: external merge Disk: 3768kB
  • Buffers: shared hit=67270 read=121809 written=3010, temp read=4313 written=3731
6. 44.298 24,568.346 ↓ 49.5 61,806 1

Hash Left Join (cost=557,058.44..558,040.22 rows=1,248 width=47) (actual time=24,435.103..24,568.346 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=67270 read=121809 written=3010, temp read=3842 written=3259
7. 21.948 24,523.736 ↓ 49.5 61,806 1

Hash Left Join (cost=557,042.14..558,003.64 rows=1,248 width=44) (actual time=24,434.762..24,523.736 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=67269 read=121808 written=3010, temp read=3842 written=3259
8. 29.258 24,501.475 ↓ 49.5 61,806 1

Hash Left Join (cost=557,040.55..557,983.34 rows=1,248 width=61) (actual time=24,434.437..24,501.475 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=67269 read=121807 written=3010, temp read=3842 written=3259
9. 50.502 24,471.725 ↓ 49.5 61,806 1

Merge Right Join (cost=557,024.25..557,949.88 rows=1,248 width=61) (actual time=24,433.929..24,471.725 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=67269 read=121806 written=3010, temp read=3842 written=3259
10. 208.853 350.911 ↑ 1.0 175,809 1

Sort (cost=27,106.96..27,566.33 rows=183,749 width=12) (actual time=319.501..350.911 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=357, temp read=586 written=588
11. 73.025 142.058 ↑ 1.0 183,749 1

Group (cost=0.42..6,060.03 rows=183,749 width=12) (actual time=6.209..142.058 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=357
12. 69.033 69.033 ↑ 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=6.207..69.033 rows=183,749 loops=1)

  • Output: lkup_dates.date_id, lkup_dates.the_date
  • Heap Fetches: 0
  • Buffers: shared read=708 written=357
13. 92.974 24,070.312 ↓ 49.5 61,806 1

Sort (cost=529,917.29..529,920.41 rows=1,248 width=57) (actual time=24,058.843..24,070.312 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=67269 read=121098 written=2653, temp read=3255 written=2671
14. 98.430 23,977.338 ↓ 49.4 61,604 1

Merge Join (cost=528,738.13..529,853.11 rows=1,248 width=57) (actual time=23,845.477..23,977.338 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=67269 read=121098 written=2653, temp read=2156 written=1572
15. 1,259.176 23,252.178 ↑ 3.3 59,792 1

Sort (cost=525,586.90..526,074.84 rows=195,173 width=17) (actual time=23,232.170..23,252.178 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=66965 read=120341 written=2229, temp read=976 written=981
16. 15.380 21,993.002 ↑ 3.3 59,792 1

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

  • Output: t.ticket_id, t.journey_id, t.ticket_number
  • Buffers: shared hit=66965 read=120341 written=2229, temp read=777 written=781
17. 74.318 21,977.622 ↑ 3.3 59,792 1

Group (cost=481,674.74..502,480.94 rows=195,173 width=737) (actual time=21,638.144..21,977.622 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=66965 read=120341 written=2229, temp read=777 written=781
18. 275.433 21,903.304 ↓ 1.1 175,144 1

Gather Merge (cost=481,674.74..501,261.11 rows=162,644 width=21) (actual time=21,638.139..21,903.304 rows=175,144 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=3696, temp read=2310 written=2322
19. 57.851 21,627.871 ↑ 1.4 58,381 3 / 3

Group (cost=480,674.71..481,487.93 rows=81,322 width=21) (actual time=21,504.542..21,627.871 rows=58,381 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=3696, temp read=2310 written=2322
  • Worker 0: actual time=21437.413..21557.201 rows=58326 loops=1
  • Buffers: shared hit=68817 read=118882 written=761, temp read=761 written=765
  • Worker 1: actual time=21438.804..21560.951 rows=58490 loops=1
  • Buffers: shared hit=68965 read=118669 written=706, temp read=772 written=776
20. 267.780 21,570.020 ↓ 2.3 184,868 3 / 3

Sort (cost=480,674.71..480,878.02 rows=81,322 width=21) (actual time=21,504.540..21,570.020 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=3696, temp read=2310 written=2322
  • Worker 0: actual time=21437.411..21500.722 rows=182688 loops=1
  • Buffers: shared hit=68817 read=118882 written=761, temp read=761 written=765
  • Worker 1: actual time=21438.803..21502.860 rows=185272 loops=1
  • Buffers: shared hit=68965 read=118669 written=706, temp read=772 written=776
21. 2,395.409 21,302.240 ↓ 2.3 184,868 3 / 3

Hash Join (cost=15,381.82..474,042.35 rows=81,322 width=21) (actual time=1,824.083..21,302.240 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=3692
  • Worker 0: actual time=2558.847..21238.400 rows=182688 loops=1
  • Buffers: shared hit=68806 read=118881 written=759
  • Worker 1: actual time=1075.210..21237.769 rows=185272 loops=1
  • Buffers: shared hit=68954 read=118668 written=704
22. 18,168.422 18,168.422 ↑ 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.255..18,168.422 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=79
  • Worker 0: actual time=0.119..18185.119 rows=6039129 loops=1
  • Buffers: shared read=118202 written=27
  • Worker 1: actual time=0.082..18164.484 rows=6036465 loops=1
  • Buffers: shared read=118137 written=27
23. 32.823 738.409 ↑ 1.0 60,490 3 / 3

Hash (cost=14,625.70..14,625.70 rows=60,490 width=21) (actual time=738.409..738.409 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=3603
  • Worker 0: actual time=672.765..672.765 rows=60490 loops=1
  • Buffers: shared hit=68779 read=679 written=727
  • Worker 1: actual time=672.768..672.768 rows=60490 loops=1
  • Buffers: shared hit=68933 read=525 written=672
24. 42.974 705.586 ↑ 1.0 60,490 3 / 3

Hash Join (cost=3,119.38..14,625.70 rows=60,490 width=21) (actual time=100.624..705.586 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=3603
  • Worker 0: actual time=56.743..642.481 rows=60490 loops=1
  • Buffers: shared hit=68779 read=679 written=727
  • Worker 1: actual time=56.750..642.382 rows=60490 loops=1
  • Buffers: shared hit=68933 read=525 written=672
25. 46.961 594.108 ↑ 1.0 67,286 3 / 3

Merge Join (cost=62.35..11,392.04 rows=67,286 width=17) (actual time=31.628..594.108 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=2210
  • Worker 0: actual time=17.581..563.020 rows=67286 loops=1
  • Buffers: shared hit=67072 read=679 written=727
  • Worker 1: actual time=17.362..562.938 rows=67286 loops=1
  • Buffers: shared hit=67226 read=525 written=672
26. 498.367 498.367 ↑ 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.326..498.367 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=1479
  • Worker 0: actual time=0.480..481.547 rows=67286 loops=1
  • Buffers: shared hit=66473 read=522 written=566
  • Worker 1: actual time=0.480..483.320 rows=67286 loops=1
  • Buffers: shared hit=66604 read=391 written=523
27. 48.780 48.780 ↑ 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.719..48.780 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=731
  • Worker 0: actual time=0.407..36.847 rows=67916 loops=1
  • Buffers: shared hit=599 read=157 written=161
  • Worker 1: actual time=0.409..34.528 rows=67916 loops=1
  • Buffers: shared hit=622 read=134 written=149
28. 20.698 68.504 ↑ 1.0 60,490 3 / 3

Hash (cost=2,300.90..2,300.90 rows=60,490 width=8) (actual time=68.504..68.504 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=1393
  • Worker 0: actual time=38.648..38.648 rows=60490 loops=1
  • Buffers: shared hit=1696
  • Worker 1: actual time=38.873..38.873 rows=60490 loops=1
  • Buffers: shared hit=1696
29. 47.806 47.806 ↑ 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=17.990..47.806 rows=60,490 loops=3)

  • Output: j.journey_id, j.transaction_id
  • Buffers: shared hit=3394 read=1694 written=1393
  • Worker 0: actual time=1.527..19.123 rows=60490 loops=1
  • Buffers: shared hit=1696
  • Worker 1: actual time=1.750..19.373 rows=60490 loops=1
  • Buffers: shared hit=1696
30. 554.812 626.730 ↓ 2.6 64,882 1

Sort (cost=3,151.22..3,214.54 rows=25,328 width=58) (actual time=612.845..626.730 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=424, temp read=885 written=591
31. 71.918 71.918 ↓ 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.786..71.918 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=424
32. 0.005 0.492 ↑ 70.0 2 1

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

  • Output: l.currency_id, l.code
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
33. 0.012 0.487 ↑ 70.0 2 1

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

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

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

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

Hash (cost=1.26..1.26 rows=26 width=16) (actual time=0.313..0.313 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
36. 0.287 0.287 ↑ 1.0 26 1

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

  • Output: lkup_payment_types.payment_type_id, lkup_payment_types.name
  • Buffers: shared read=1
37. 0.011 0.312 ↑ 140.0 1 1

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

  • Output: l_1.card_type_id, l_1.code
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=1
38. 0.010 0.301 ↑ 140.0 1 1

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

  • Output: l_1.card_type_id, l_1.code
  • Group Key: l_1.card_type_id
  • Buffers: shared read=1
39. 0.291 0.291 ↑ 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.290..0.291 rows=1 loops=1)

  • Output: l_1.card_type_id, l_1.code, l_1.description
  • Buffers: shared read=1