explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u3vM

Settings
# exclusive inclusive rows x rows loops node
1. 174.147 91,421.923 ↓ 633,092.0 633,092 1

WindowAgg (cost=358,803.68..358,803.71 rows=1 width=36) (actual time=91,148.548..91,421.923 rows=633,092 loops=1)

  • Buffers: shared hit=110977334 read=1127686 dirtied=16 written=57, local hit=633092, temp read=1066675 written=12093
2.          

CTE clients

3. 204.581 478.058 ↑ 1.1 257,512 1

Hash Join (cost=19,968.04..64,836.37 rows=277,725 width=16) (actual time=137.55..478.058 rows=257,512 loops=1)

  • Buffers: shared hit=4 read=28729, temp read=4134 written=4120
4. 136.071 136.071 ↑ 1.0 729,921 1

Seq Scan on client_back cb (cost=0..34,311.49 rows=732,509 width=16) (actual time=0.007..136.071 rows=729,921 loops=1)

  • Filter: (cb.bank_id = 8)
  • Buffers: shared hit=2 read=21819
5. 50.998 137.406 ↓ 1.2 436,898 1

Hash (cost=13,752.38..13,752.38 rows=378,853 width=8) (actual time=137.406..137.406 rows=436,898 loops=1)

  • Buffers: shared hit=2 read=6910, temp written=1302
6. 86.408 86.408 ↓ 1.2 436,898 1

Seq Scan on client_front cf (cost=0..13,752.38 rows=378,853 width=8) (actual time=0.006..86.408 rows=436,898 loops=1)

  • Filter: ((cf.first_login IS NOT NULL) AND ((cf.phase)::text = ANY ('{READY,PASSWORD_RESET}'::text[])))
  • Buffers: shared hit=2 read=6910
7. 293.723 91,247.776 ↓ 633,092.0 633,092 1

Sort (cost=293,967.31..293,967.32 rows=1 width=28) (actual time=91,148.537..91,247.776 rows=633,092 loops=1)

  • Sort Key: oca_dateio.client_id, (COALESCE((w.score_total)::integer, '-1'::integer)) DESC
  • Sort Method: external merge Disk: 26048kB
  • Buffers: shared hit=110977334 read=1127686 dirtied=16 written=57, local hit=633092, temp read=1066675 written=12093
8. 285.338 90,954.053 ↓ 633,092.0 633,092 1

Nested Loop (cost=15,275.83..293,967.3 rows=1 width=28) (actual time=72,594.434..90,954.053 rows=633,092 loops=1)

  • Buffers: shared hit=110977334 read=1127686 dirtied=16 written=57, local hit=633092, temp read=1062870 written=8273
9. 405.635 90,668.715 ↓ 633,092.0 633,092 1

Nested Loop (cost=15,275.68..293,967.11 rows=1 width=26) (actual time=72,594.427..90,668.715 rows=633,092 loops=1)

  • Buffers: shared hit=110977334 read=1127686 dirtied=16 written=57, temp read=1062870 written=8273
10. 16,530.607 89,004.172 ↓ 1,258,908.0 1,258,908 1

Nested Loop (cost=15,275.4..293,966.79 rows=1 width=42) (actual time=57,227.144..89,004.172 rows=1,258,908 loops=1)

  • Buffers: shared hit=107200630 read=1127666 dirtied=16 written=57, temp read=1062870 written=8273
11. 8.707 8.707 ↓ 1.5 542 1

Index Scan using unique_offer_bank_publish_offer_bank on offer_bank_publish obp (cost=0.28..549.12 rows=358 width=16) (actual time=0.032..8.707 rows=542 loops=1)

  • Filter: (obp.visible_to > (CURRENT_DATE + 3))
  • Buffers: shared hit=3448 read=101 dirtied=15
12. 17,651.388 72,464.858 ↓ 468,467.0 468,467 542

Materialize (cost=15,275.12..293,412.31 rows=1 width=26) (actual time=3.118..133.699 rows=468,467 loops=542)

  • Buffers: shared hit=107197182 read=1127565 dirtied=1 written=57, temp read=1062870 written=8273
13. 257.674 54,813.470 ↓ 468,467.0 468,467 1

Nested Loop (cost=15,275.12..293,412.3 rows=1 width=26) (actual time=1,688.747..54,813.47 rows=468,467 loops=1)

  • Buffers: shared hit=107197182 read=1127565 dirtied=1 written=57, temp read=10084 written=6328
14. 61.976 54,555.796 ↓ 468,467.0 468,467 1

Nested Loop (cost=15,274.97..293,412.13 rows=1 width=32) (actual time=1,688.739..54,555.796 rows=468,467 loops=1)

  • Buffers: shared hit=106728716 read=1127564 dirtied=1 written=57, temp read=10084 written=6328
15. 399.330 3,224.492 ↓ 129,468.0 129,468 1

Merge Join (cost=15,274.53..291,761.11 rows=1 width=8) (actual time=1,688.417..3,224.492 rows=129,468 loops=1)

  • Filter: (oce.id IS NULL)
  • Buffers: shared hit=1560837 read=480012 dirtied=1 written=57, temp read=10084 written=6328
16. 2,046.116 2,046.116 ↓ 1.0 2,845,597 1

Index Scan using offer_client_event_client_id_offer_id on offer_client_event oce (cost=0.43..209,064.56 rows=2,845,133 width=16) (actual time=0.016..2,046.116 rows=2,845,597 loops=1)

  • Buffers: shared hit=1560833 read=451283 dirtied=1 written=57
17. 210.547 779.046 ↓ 12.5 1,153,918 1

Sort (cost=15,274.1..15,505.54 rows=92,575 width=8) (actual time=666.489..779.046 rows=1,153,918 loops=1)

  • Sort Key: c.client_id
  • Sort Method: external sort Disk: 5560kB
  • Buffers: shared hit=4 read=28729, temp read=7803 written=6328
18. 568.499 568.499 ↓ 2.8 257,512 1

CTE Scan on clients c (cost=0..7,637.44 rows=92,575 width=8) (actual time=137.555..568.499 rows=257,512 loops=1)

  • Filter: (c.registration_time <= (CURRENT_DATE - 30))
  • Buffers: shared hit=4 read=28729, temp read=4134 written=4937
19. 129.468 51,269.328 ↑ 3.0 4 129,468

Append (cost=0.44..1,650.9 rows=12 width=24) (actual time=0.017..0.396 rows=4 loops=129,468)

  • Buffers: shared hit=105167879 read=647552
20. 4,531.380 4,531.380 ↑ 2.8 4 129,468

Index Scan using offer_client_allocation_client_id on offer_client_allocation_dateio oca_dateio (cost=0.44..16.27 rows=11 width=24) (actual time=0.013..0.035 rows=4 loops=129,468)

  • Index Cond: (oca_dateio.client_id = c.client_id)
  • Filter: ((oca_dateio.visible_from < now()) AND (oca_dateio.visible_to > now()))
  • Buffers: shared hit=1723160 read=647339
21. 1,683.084 46,608.480 ↓ 0.0 0 129,468

Subquery Scan on *SELECT* 2 (cost=0.56..1,634.64 rows=1 width=24) (actual time=0.36..0.36 rows=0 loops=129,468)

  • Filter: (c.client_id = "*SELECT* 2".client_id)
  • Buffers: shared hit=103444719 read=213
22. 15,018.288 44,925.396 ↓ 191.0 191 129,468

Nested Loop (cost=0.56..1,634.62 rows=1 width=628) (actual time=0.003..0.347 rows=191 loops=129,468)

  • Filter: (ad.id IS NULL)
  • Buffers: shared hit=103444719 read=213
23. 4,401.912 4,401.912 ↓ 1.0 197 129,468

Seq Scan on offer_client_allocation_client ac (cost=0..10.64 rows=189 width=24) (actual time=0.001..0.034 rows=197 loops=129,468)

  • Filter: ((ac.visible_from < now()) AND (ac.visible_to > now()))
  • Buffers: shared hit=647335 read=5
24. 25,505.196 25,505.196 ↓ 0.0 0 25,505,196

Index Scan using unique_client_id on offer_client_allocation_dateio ad (cost=0.56..8.58 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=25,505,196)

  • Index Cond: ((ac.offer_id = ad.offer_id) AND (ac.client_id = ad.client_id))
  • Buffers: shared hit=102797384 read=208
25. 0.000 0.000 ↓ 0.0 0 468,467

Index Scan using unique_offer_client_allowed on offer_client_allowed w (cost=0.14..0.16 rows=1 width=18) (actual time=0..0 rows=0 loops=468,467)

  • Index Cond: (w.offer_id = oca_dateio.offer_id)
  • Buffers: shared hit=468466 read=1
26. 1,258.908 1,258.908 ↑ 1.0 1 1,258,908

Index Scan using offer_pkey on offer o (cost=0.28..0.31 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=1,258,908)

  • Index Cond: (o.id = oca_dateio.offer_id)
  • Filter: ((o.country = 'CZ'::bpchar) AND (o.contract_to > (CURRENT_DATE + 10)))
  • Buffers: shared hit=3776704 read=20
27. 0.000 0.000 ↓ 0.0 0 633,092

Index Only Scan using index_temporary_message_ab_crm_acq on temporary_message_ab_crm_acq acq_sent (cost=0.15..0.29 rows=8 width=8) (actual time=0..0 rows=0 loops=633,092)

  • Index Cond: (acq_sent.client_id = oca_dateio.client_id)
  • Buffers: local hit=633092