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 53264.309 ↑ 482.5 697 1

Subquery Scan on open_prefetch_ticket (cost=9023695.79..9396419.95 rows=336307 width=16) (actual time=52342.820..53264.309 rows=697 loops=1)

2. 39.441 53264.019 ↑ 482.5 697 1

GroupAggregate (cost=9023695.79..9392216.11 rows=336307 width=406) (actual time=52342.814..53264.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. 4151.877 53224.578 ↑ 300.5 21736 1

Sort (cost=9023695.79..9040026.33 rows=6532216 width=406) (actual time=52342.665..53224.578 rows=21736 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 49072.701 ↑ 300.5 21736 1

Merge Left Join (cost=1709208.88..2255999.49 rows=6532216 width=406) (actual time=46802.989..49072.701 rows=21736 loops=1)

  • Merge Cond: (prefetch_ticket.id = entry_listen.prefetch_ticket_id)
5. 1701.509 31649.255 ↑ 210.6 7984 1

GroupAggregate (cost=1709208.88..1772266.29 rows=1681531 width=374) (actual time=30687.805..31649.255 rows=7984 loops=1)

  • Filter: (sum(entry_prefetch.amount) <> 0)
6. 21600.587 29947.746 ↓ 1.0 1712598 1

Sort (cost=1709208.88..1713412.71 rows=1681531 width=374) (actual time=28398.345..29947.746 rows=1712598 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. 3379.831 8347.159 ↓ 1.0 1712598 1

Hash Join (cost=205634.90..385829.28 rows=1681531 width=374) (actual time=3640.453..8347.159 rows=1712598 loops=1)

  • Hash Cond: (entry_prefetch.prefetch_ticket_id = prefetch_ticket.id)
8. 1332.489 1332.489 ↓ 1.0 1778833 1

Seq Scan on listen_entry entry_prefetch (cost=0.00..92532.31 rows=1774550 width=18) (actual time=0.049..1332.489 rows=1778833 loops=1)

  • Filter: (listen_ledger_id = 1)
9. 1488.899 3634.839 ↑ 1.0 860298 1

Hash (cost=153700.43..153700.43 rows=860518 width=364) (actual time=3634.839..3634.839 rows=860298 loops=1)

  • Buckets: 1024 Batches: 512 Memory Usage: 621kB
10. 486.259 2145.940 ↑ 1.0 860298 1

Hash Join (cost=1143.15..153700.43 rows=860518 width=364) (actual time=31.107..2145.940 rows=860298 loops=1)

  • Hash Cond: (prefetch_ticket.codec_profile_id = codec_profile.id)
11. 851.875 1659.558 ↑ 1.0 860298 1

Hash Join (cost=1140.00..141865.15 rows=860518 width=358) (actual time=30.951..1659.558 rows=860298 loops=1)

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

Seq Scan on prefetch_ticket (cost=0.00..49617.50 rows=908118 width=289) (actual time=0.024..776.940 rows=893429 loops=1)

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

Hash (cost=737.46..737.46 rows=15963 width=77) (actual time=30.743..30.743 rows=15963 loops=1)

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

Seq Scan on station (cost=0.00..737.46 rows=15963 width=77) (actual time=0.017..18.076 rows=15963 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 16901.936 ↑ 1.0 1762596 1

Materialize (cost=0.00..369148.30 rows=1766995 width=34) (actual time=0.115..16901.936 rows=1762596 loops=1)

21. 16389.295 16389.295 ↑ 1.0 1762596 1

Index Scan using in_listen_entry_prefetch_ticket_id on listen_entry entry_listen (cost=0.00..364730.82 rows=1766995 width=34) (actual time=0.110..16389.295 rows=1762596 loops=1)

  • Filter: (listen_ledger_id = 2)