#	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)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 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)
   ->  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)))
         ->  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
               ->  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)
                     ->  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)
                           ->  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
                                 ->  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)
                                       ->  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)
                                       ->  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
                                             ->  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)
                                                   ->  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)
                                                         ->  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[]))
                                                         ->  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
                                                               ->  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
                                                   ->  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
                                                         ->  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)
                                                               ->  Seq Scan on codec_profile  (cost=0.00..1.30 rows=30 width=20) (actual time=0.004..0.017 rows=30 loops=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
                                                                     ->  Seq Scan on channel_setting  (cost=0.00..1.03 rows=3 width=10) (actual time=0.003..0.007 rows=3 loops=1)
                     ->  Materialize  (cost=0.00..369148.30 rows=1766995 width=34) (actual time=0.115..16901.936 rows=1762596 loops=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)
 Total runtime: 53456.780 ms
(41 rows)

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	2	1740.950 ms	3.3 %
Hash	4	1501.601 ms	2.8 %
Hash Join	4	4718.029 ms	8.9 %
Index Scan	1	16389.295 ms	30.8 %
Materialize	1	512.641 ms	1.0 %
Merge Left Join	1	521.510 ms	1.0 %
Seq Scan	5	2127.529 ms	4.0 %
Sort	2	25752.464 ms	48.3 %
Subquery Scan on open_prefetch_ticket	1	0.290 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
channel_setting	1	0.007 ms	0.0 %
Seq Scan	1	0.007 ms	100.0 %
codec_profile	1	0.017 ms	0.0 %
Seq Scan	1	0.017 ms	100.0 %
listen_entry	2	17721.784 ms	33.3 %
Index Scan	1	16389.295 ms	92.5 %
Seq Scan	1	1332.489 ms	7.5 %
prefetch_ticket	1	776.940 ms	1.5 %
Seq Scan	1	776.940 ms	100.0 %
station	1	18.076 ms	0.0 %
Seq Scan	1	18.076 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: ZPZ

Colorize:

Columns visibility:

Per node type stats

Per table stats