Result: 26c

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
7747.878	32912.832	↑ 1.0	1	1	→ HashAggregate (cost=255323.16..255323.25 rows=1 width=71) (actual time=32912.829..32912.832 rows=1 loops=1) Output: sum(CASE WHEN (t.principal_id IS NOT NULL) THEN 1 ELSE 0 END), sum(CASE WHEN ((t.principal_id IS NULL) AND (NOT ((t.token_shutdown_date < now()) OR (toob.event_remain_counter = 0::numeric)))) THEN 1 ELSE 0 END), sum(CASE WHEN ((t.token_shutdown_date < now()) OR (toob.event_remain_counter = 0::numeric)) THEN 1 ELSE 0 END), sum(CASE WHEN ((t.token_shutdown_date > now()) AND (t.token_shutdown_date < (now() + '90 days'::interval))) THEN 1 ELSE 0 END), sum(CASE WHEN (t.is_enabled AND (NOT (t.token_shutdown_date < now()))) THEN 1 ELSE 0 END), sum(CASE WHEN ((t.principal_id IS NOT NULL) AND (NOT t.is_enabled) AND (NOT (t.token_shutdown_date < now()))) THEN 1 ELSE 0 END), sum(CASE WHEN t.is_token_lost THEN 1 ELSE 0 END), sum(CASE WHEN (t.replacement_mode = 2::numeric) THEN 1 ELSE 0 END), sum(CASE WHEN (t.replacement_mode = 1::numeric) THEN 1 ELSE 0 END), sum(CASE WHEN ((t.last_da_code_time IS NOT NULL) AND (t.last_da_code_time > now()) AND t.is_da_data_downloaded) THEN 1 ELSE 0 END), sum(CASE WHEN (toob.event_remain_counter = (-500000)::numeric) THEN 0 ELSE 1 END), t.token_type Buffers: shared hit=816 read=137887, temp read=19076 written=18950
11460.991	25164.954	↑ 1.0	876002	1	→ Hash Join (cost=55756.65..229042.92 rows=876008 width=71) (actual time=6681.781..25164.954 rows=876002 loops=1) Output: t.principal_id, t.token_shutdown_date, t.is_enabled, t.is_token_lost, t.replacement_mode, t.last_da_code_time, t.is_da_data_downloaded, t.token_type, toob.event_remain_counter Hash Cond: ((t.id)::text = (toob.am_token_id)::text) Buffers: shared hit=816 read=137887, temp read=19076 written=18950
7070.358	7070.358	↑ 1.0	876002	1	→ Seq Scan on rsa_rep.am_token t (cost=0.00..120451.10 rows=876008 width=97) (actual time=0.884..7070.358 rows=876002 loops=1) Output: t.principal_id, t.token_shutdown_date, t.is_enabled, t.is_token_lost, t.replacement_mode, t.last_da_code_time, t.is_da_data_downloaded, t.token_type, t.id Filter: ((t.owner_id)::text = '000000000000000000001000e0011000'::text) Buffers: shared hit=606 read=108895
3661.478	6633.605	↑ 1.0	876002	1	→ Hash (cost=37962.29..37962.29 rows=876029 width=40) (actual time=6633.605..6633.605 rows=876002 loops=1) Output: toob.event_remain_counter, toob.am_token_id Buckets: 2048 Batches: 64 Memory Usage: 872kB Buffers: shared hit=210 read=28992, temp written=6286
2972.127	2972.127	↑ 1.0	876002	1	→ Seq Scan on rsa_batchrep.am_token_oob toob (cost=0.00..37962.29 rows=876029 width=40) (actual time=0.192..2972.127 rows=876002 loops=1) Output: toob.event_remain_counter, toob.am_token_id Buffers: shared hit=210 read=28992

"HashAggregate  (cost=255323.16..255323.25 rows=1 width=71) (actual time=32912.829..32912.832 rows=1 loops=1)"
"  Output: sum(CASE WHEN (t.principal_id IS NOT NULL) THEN 1 ELSE 0 END), sum(CASE WHEN ((t.principal_id IS NULL) AND (NOT ((t.token_shutdown_date < now()) OR (toob.event_remain_counter = 0::numeric)))) THEN 1 ELSE 0 END), sum(CASE WHEN ((t.token_shutdown_date < now()) OR (toob.event_remain_counter = 0::numeric)) THEN 1 ELSE 0 END), sum(CASE WHEN ((t.token_shutdown_date > now()) AND (t.token_shutdown_date < (now() + '90 days'::interval))) THEN 1 ELSE 0 END), sum(CASE WHEN (t.is_enabled AND (NOT (t.token_shutdown_date < now()))) THEN 1 ELSE 0 END), sum(CASE WHEN ((t.principal_id IS NOT NULL) AND (NOT t.is_enabled) AND (NOT (t.token_shutdown_date < now()))) THEN 1 ELSE 0 END), sum(CASE WHEN t.is_token_lost THEN 1 ELSE 0 END), sum(CASE WHEN (t.replacement_mode = 2::numeric) THEN 1 ELSE 0 END), sum(CASE WHEN (t.replacement_mode = 1::numeric) THEN 1 ELSE 0 END), sum(CASE WHEN ((t.last_da_code_time IS NOT NULL) AND (t.last_da_code_time > now()) AND t.is_da_data_downloaded) THEN 1 ELSE 0 END), sum(CASE WHEN (toob.event_remain_counter = (-500000)::numeric) THEN 0 ELSE 1 END), t.token_type"
"  Buffers: shared hit=816 read=137887, temp read=19076 written=18950"
"  ->  Hash Join  (cost=55756.65..229042.92 rows=876008 width=71) (actual time=6681.781..25164.954 rows=876002 loops=1)"
"        Output: t.principal_id, t.token_shutdown_date, t.is_enabled, t.is_token_lost, t.replacement_mode, t.last_da_code_time, t.is_da_data_downloaded, t.token_type, toob.event_remain_counter"
"        Hash Cond: ((t.id)::text = (toob.am_token_id)::text)"
"        Buffers: shared hit=816 read=137887, temp read=19076 written=18950"
"        ->  Seq Scan on rsa_rep.am_token t  (cost=0.00..120451.10 rows=876008 width=97) (actual time=0.884..7070.358 rows=876002 loops=1)"
"              Output: t.principal_id, t.token_shutdown_date, t.is_enabled, t.is_token_lost, t.replacement_mode, t.last_da_code_time, t.is_da_data_downloaded, t.token_type, t.id"
"              Filter: ((t.owner_id)::text = '000000000000000000001000e0011000'::text)"
"              Buffers: shared hit=606 read=108895"
"        ->  Hash  (cost=37962.29..37962.29 rows=876029 width=40) (actual time=6633.605..6633.605 rows=876002 loops=1)"
"              Output: toob.event_remain_counter, toob.am_token_id"
"              Buckets: 2048  Batches: 64  Memory Usage: 872kB"
"              Buffers: shared hit=210 read=28992, temp written=6286"
"              ->  Seq Scan on rsa_batchrep.am_token_oob toob  (cost=0.00..37962.29 rows=876029 width=40) (actual time=0.192..2972.127 rows=876002 loops=1)"
"                    Output: toob.event_remain_counter, toob.am_token_id"
"                    Buffers: shared hit=210 read=28992"
"Total runtime: 32913.884 ms"

Per node type stats

node type	count	sum of times	% of query
Hash	1	3661.478 ms	11.1 %
Hash Join	1	11460.991 ms	34.8 %
HashAggregate	1	7747.878 ms	23.5 %
Seq Scan	2	10042.485 ms	30.5 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
rsa_batchrep.am_token_oob	1	2972.127 ms	9.0 %
Seq Scan	1	2972.127 ms	100.0 %
rsa_rep.am_token	1	7070.358 ms	21.5 %
Seq Scan	1	7070.358 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 26c

Colorize:

Columns visibility:

Per node type stats

Per table stats