explain.depesz.com

A tool for finding a real cause for slow queries.

Result: ZPZ

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 0.290 53,264.309 ↑ 482.5 697 1

Subquery Scan on open_prefetch_ticket (cost=9,023,695.79..9,396,419.95 rows=336,307 width=16) (actual time=52,342.820..53,264.309 rows=697 loops=1)

2. 39.441 53,264.019 ↑ 482.5 697 1

GroupAggregate (cost=9,023,695.79..9,392,216.11 rows=336,307 width=406) (actual time=52,342.814..53,264.019 rows=697 loops=1)

  • Filter: ((((now() - (min(entry_prefetch."time"))) > '00:03:00'::interval) AND (prefetch_ticket.user_agent_stream IS NULL)) OR (((now() - max(entry_listen."time")) > '01:00:00'::interval) AND (sum(entry_listen.amount) = 0)))
3. 4,151.877 53,224.578 ↑ 300.5 21,736 1

Sort (cost=9,023,695.79..9,040,026.33 rows=6,532,216 width=406) (actual time=52,342.665..53,224.578 rows=21,736 loops=1)

  • Sort Key: channel_setting.identifier, codec_profile.bitrate, station.title, station.source_url, station.source_url_is_playlist, prefetch_ticket.codec_profile_id, prefetch_ticket.id, prefetch_ticket.account_id, prefetch_ticket.station_id, prefetch_ticket.user_agent_prefetch, prefetch_ticket.ip_prefetch, prefetch_ticket.user_agent_stream, prefetch_ticket.prefetch_type_id, (sum(entry_prefetch.amount)), (min(entry_prefetch."time"))
  • Sort Method: external merge Disk: 8528kB
4. 521.510 49,072.701 ↑ 300.5 21,736 1

Merge Left Join (cost=1,709,208.88..2,255,999.49 rows=6,532,216 width=406) (actual time=46,802.989..49,072.701 rows=21,736 loops=1)

  • Merge Cond: (prefetch_ticket.id = entry_listen.prefetch_ticket_id)
5. 1,701.509 31,649.255 ↑ 210.6 7,984 1

GroupAggregate (cost=1,709,208.88..1,772,266.29 rows=1,681,531 width=374) (actual time=30,687.805..31,649.255 rows=7,984 loops=1)

  • Filter: (sum(entry_prefetch.amount) <> 0)
6. 21,600.587 29,947.746 ↓ 1.0 1,712,598 1

Sort (cost=1,709,208.88..1,713,412.71 rows=1,681,531 width=374) (actual time=28,398.345..29,947.746 rows=1,712,598 loops=1)

  • Sort Key: prefetch_ticket.id, station.source_url, station.source_url_is_playlist, station.title, codec_profile.bitrate, channel_setting.identifier
  • Sort Method: external merge Disk: 586512kB
7. 3,379.831 8,347.159 ↓ 1.0 1,712,598 1

Hash Join (cost=205,634.90..385,829.28 rows=1,681,531 width=374) (actual time=3,640.453..8,347.159 rows=1,712,598 loops=1)

  • Hash Cond: (entry_prefetch.prefetch_ticket_id = prefetch_ticket.id)
8. 1,332.489 1,332.489 ↓ 1.0 1,778,833 1

Seq Scan on listen_entry entry_prefetch (cost=0.00..92,532.31 rows=1,774,550 width=18) (actual time=0.049..1,332.489 rows=1,778,833 loops=1)

  • Filter: (listen_ledger_id = 1)
9. 1,488.899 3,634.839 ↑ 1.0 860,298 1

Hash (cost=153,700.43..153,700.43 rows=860,518 width=364) (actual time=3,634.839..3,634.839 rows=860,298 loops=1)

  • Buckets: 1024 Batches: 512 Memory Usage: 621kB
10. 486.259 2,145.940 ↑ 1.0 860,298 1

Hash Join (cost=1,143.15..153,700.43 rows=860,518 width=364) (actual time=31.107..2,145.940 rows=860,298 loops=1)

  • Hash Cond: (prefetch_ticket.codec_profile_id = codec_profile.id)
11. 851.875 1,659.558 ↑ 1.0 860,298 1

Hash Join (cost=1,140.00..141,865.15 rows=860,518 width=358) (actual time=30.951..1,659.558 rows=860,298 loops=1)

  • Hash Cond: (prefetch_ticket.station_id = station.id)
12. 776.940 776.940 ↑ 1.0 893,429 1

Seq Scan on prefetch_ticket (cost=0.00..49,617.50 rows=908,118 width=289) (actual time=0.024..776.940 rows=893,429 loops=1)

  • Filter: (id <> ALL ('{1728,393960}'::bigint[]))
13. 12.667 30.743 ↑ 1.0 15,963 1

Hash (cost=737.46..737.46 rows=15,963 width=77) (actual time=30.743..30.743 rows=15,963 loops=1)

  • Buckets: 1024 Batches: 2 Memory Usage: 912kB
14. 18.076 18.076 ↑ 1.0 15,963 1

Seq Scan on station (cost=0.00..737.46 rows=15,963 width=77) (actual time=0.017..18.076 rows=15,963 loops=1)

  • Filter: active
15. 0.027 0.123 ↑ 1.0 30 1

Hash (cost=2.78..2.78 rows=30 width=14) (actual time=0.123..0.123 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
16. 0.064 0.096 ↑ 1.0 30 1

Hash Join (cost=1.07..2.78 rows=30 width=14) (actual time=0.043..0.096 rows=30 loops=1)

  • Hash Cond: (codec_profile.channel_setting_id = channel_setting.id)
17. 0.017 0.017 ↑ 1.0 30 1

Seq Scan on codec_profile (cost=0.00..1.30 rows=30 width=20) (actual time=0.004..0.017 rows=30 loops=1)

18. 0.008 0.015 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=10) (actual time=0.015..0.015 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
19. 0.007 0.007 ↑ 1.0 3 1

Seq Scan on channel_setting (cost=0.00..1.03 rows=3 width=10) (actual time=0.003..0.007 rows=3 loops=1)

20. 512.641 16,901.936 ↑ 1.0 1,762,596 1

Materialize (cost=0.00..369,148.30 rows=1,766,995 width=34) (actual time=0.115..16,901.936 rows=1,762,596 loops=1)

21. 16,389.295 16,389.295 ↑ 1.0 1,762,596 1

Index Scan using in_listen_entry_prefetch_ticket_id on listen_entry entry_listen (cost=0.00..364,730.82 rows=1,766,995 width=34) (actual time=0.110..16,389.295 rows=1,762,596 loops=1)

  • Filter: (listen_ledger_id = 2)