#	exclusive	inclusive	rows x	rows	loops	node
1.	2.263	2,445.798	↑ 1.0	1	1	→ Aggregate (cost=2,447,273.40..2,447,273.41 rows=1 width=0) (actual time=2,445.797..2,445.798 rows=1 loops=1)
2.	552.613	2,443.535	↑ 16.2	4,201	1	→ Hash Semi Join (cost=157,898.16..2,447,103.26 rows=68,053 width=0) (actual time=523.914..2,443.535 rows=4,201 loops=1) Hash Cond: (action_states.ad_id = ads.ad_id)
3.	1,490.564	1,809.019	↑ 1.9	789,436	1	→ Bitmap Heap Scan on action_states (cost=84,149.35..2,367,557.06 rows=1,514,900 width=4) (actual time=441.813..1,809.019 rows=789,436 loops=1) Recheck Cond: (((state)::text = 'reg'::text) AND ("timestamp" >= (now() - '7 days'::interval)))
4.	318.455	318.455	↑ 1.9	789,459	1	→ Bitmap Index Scan on index_action_states_state_timestamp3 (cost=0.00..83,770.62 rows=1,514,900 width=0) (actual time=318.455..318.455 rows=789,459 loops=1) Index Cond: (((state)::text = 'reg'::text) AND ("timestamp" >= (now() - '7 days'::interval)))
5.	6.133	81.903	↑ 2.2	9,312	1	→ Hash (cost=73,489.99..73,489.99 rows=20,706 width=4) (actual time=81.903..81.903 rows=9,312 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 328kB
6.	73.208	75.770	↑ 2.2	9,312	1	→ Bitmap Heap Scan on ads (cost=1,200.41..73,489.99 rows=20,706 width=4) (actual time=4.176..75.770 rows=9,312 loops=1) Recheck Cond: (user_id = 1930749)
7.	2.562	2.562	↑ 1.9	11,118	1	→ Bitmap Index Scan on index_ads_user_id (cost=0.00..1,195.23 rows=20,706 width=0) (actual time=2.562..2.562 rows=11,118 loops=1) Index Cond: (user_id = 1930749)

Total runtime	:	2,447.270 ms

 Aggregate  (cost=2447273.40..2447273.41 rows=1 width=0) (actual time=2445.797..2445.798 rows=1 loops=1)
   ->  Hash Semi Join  (cost=157898.16..2447103.26 rows=68053 width=0) (actual time=523.914..2443.535 rows=4201 loops=1)
         Hash Cond: (action_states.ad_id = ads.ad_id)
         ->  Bitmap Heap Scan on action_states  (cost=84149.35..2367557.06 rows=1514900 width=4) (actual time=441.813..1809.019 rows=789436 loops=1)
               Recheck Cond: (((state)::text = 'reg'::text) AND ("timestamp" >= (now() - '7 days'::interval)))
               ->  Bitmap Index Scan on index_action_states_state_timestamp3  (cost=0.00..83770.62 rows=1514900 width=0) (actual time=318.455..318.455 rows=789459 loops=1)
                     Index Cond: (((state)::text = 'reg'::text) AND ("timestamp" >= (now() - '7 days'::interval)))
         ->  Hash  (cost=73489.99..73489.99 rows=20706 width=4) (actual time=81.903..81.903 rows=9312 loops=1)
               Buckets: 4096  Batches: 1  Memory Usage: 328kB
               ->  Bitmap Heap Scan on ads  (cost=1200.41..73489.99 rows=20706 width=4) (actual time=4.176..75.770 rows=9312 loops=1)
                     Recheck Cond: (user_id = 1930749)
                     ->  Bitmap Index Scan on index_ads_user_id  (cost=0.00..1195.23 rows=20706 width=0) (actual time=2.562..2.562 rows=11118 loops=1)
                           Index Cond: (user_id = 1930749)
 Total runtime: 2447.270 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	2.263 ms	0.1 %
Bitmap Heap Scan	2	1,563.772 ms	63.9 %
Bitmap Index Scan	2	321.017 ms	13.1 %
Hash	1	6.133 ms	0.3 %
Hash Semi Join	1	552.613 ms	22.6 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
action_states	1	1,490.564 ms	60.9 %
Bitmap Heap Scan	1	1,490.564 ms	100.0 %
ads	1	73.208 ms	3.0 %
Bitmap Heap Scan	1	73.208 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6dE24

Color mode:

Visible columns:

Per node type stats

Per table stats