#	exclusive	inclusive	rows x	rows	loops	node
1.	2.921	25572.078	↑ 1385.3	7	1	→ HashAggregate (cost=2506583.15..2506680.12 rows=9697 width=112) (actual time=25572.034..25572.078 rows=7 loops=1)
2.	35.727	25569.157	↑ 5.2	2016	1	→ Hash Join (cost=787303.52..2506347.26 rows=10484 width=112) (actual time=25157.597..25569.157 rows=2016 loops=1) Hash Cond: ((f.banner_id = z.banner_id) AND (upper((a.acct_desc)::text) = upper((y.term_desc)::text)))
3.	336.861	25468.449	↑ 10.6	19800	1	→ Hash Join (cost=666158.76..2373564.77 rows=209689 width=100) (actual time=25092.575..25468.449 rows=19800 loops=1) Hash Cond: ((d.cookie_id)::bpchar = f.id) Join Filter: ((a.date > f.create_time) AND (((a.date)::timestamp without time zone - '60 days'::interval) < f.create_time))
4.	31.804	1114.193	↑ 1.5	29955	1	→ Hash Join (cost=39647.40..52177.72 rows=44599 width=133) (actual time=1070.149..1114.193 rows=29955 loops=1) Hash Cond: ((a.adic)::text = (d.adic)::text)
5.	4.915	227.845	↓ 1.9	4866	1	→ Nested Loop (cost=10240.84..17549.86 rows=2535 width=133) (actual time=215.323..227.845 rows=4866 loops=1)
6.	0.060	0.060	↑ 1.0	1	1	→ Seq Scan on sgmt_clients b (cost=0.00..1.78 rows=1 width=38) (actual time=0.030..0.060 rows=1 loops=1) Filter: (lower((userid)::text) = 'mchcu'::text)
7.	8.167	222.870	↓ 1.9	4866	1	→ Bitmap Heap Scan on attribution_summary2 a (cost=10240.84..17522.73 rows=2535 width=105) (actual time=215.283..222.870 rows=4866 loops=1) Recheck Cond: ((a.date >= '2012-01-01'::date) AND (a.date <= '2012-01-31'::date) AND ((a.userid)::text = 'mchcu'::text))
8.	31.170	214.703	↓ 0.0	0	1	→ BitmapAnd (cost=10240.84..10240.84 rows=2535 width=0) (actual time=214.703..214.703 rows=0 loops=1)
9.	51.836	51.836	↓ 1.1	179635	1	→ Bitmap Index Scan on attsumm_date2 (cost=0.00..2911.00 rows=159434 width=0) (actual time=51.836..51.836 rows=179635 loops=1) Index Cond: ((a.date >= '2012-01-01'::date) AND (a.date <= '2012-01-31'::date))
10.	131.697	131.697	↓ 1.1	510302	1	→ Bitmap Index Scan on attsumm_userid2 (cost=0.00..7328.32 rows=465155 width=0) (actual time=131.697..131.697 rows=510302 loops=1) Index Cond: ((a.userid)::text = 'mchcu'::text)
11.	464.919	854.544	↑ 1.0	820647	1	→ Hash (cost=19148.47..19148.47 rows=820647 width=74) (actual time=854.544..854.544 rows=820647 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 84950kB
12.	389.625	389.625	↑ 1.0	820647	1	→ Seq Scan on sgmt_adic d (cost=0.00..19148.47 rows=820647 width=74) (actual time=0.014..389.625 rows=820647 loops=1)
13.	13564.598	24017.395	↑ 1.0	17484016	1	→ Hash (cost=407961.16..407961.16 rows=17484016 width=49) (actual time=24017.395..24017.395 rows=17484016 loops=1) Buckets: 2097152 Batches: 1 Memory Usage: 1502533kB
14.	10452.797	10452.797	↑ 1.0	17484016	1	→ Seq Scan on sgmt_user_tracker f (cost=0.00..407961.16 rows=17484016 width=49) (actual time=0.021..10452.797 rows=17484016 loops=1)
15.	18.264	64.981	↑ 2.0	9983	1	→ Hash (cost=120852.41..120852.41 rows=19490 width=42) (actual time=64.981..64.981 rows=9983 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 730kB
16.	10.815	46.717	↑ 2.0	9983	1	→ Nested Loop (cost=32.15..120852.41 rows=19490 width=42) (actual time=0.057..46.717 rows=9983 loops=1)
17.	0.820	0.820	↑ 1.0	1949	1	→ Seq Scan on attr_lookup z (cost=0.00..108.49 rows=1949 width=61) (actual time=0.008..0.820 rows=1949 loops=1)
18.	13.643	35.082	↑ 2.0	5	1949	→ Bitmap Heap Scan on wandterms y (cost=32.15..61.73 rows=10 width=26) (actual time=0.012..0.018 rows=5 loops=1949) Recheck Cond: (y.term_code = ANY (z.target_kli))
19.	21.439	21.439	↑ 2.0	5	1949	→ Bitmap Index Scan on idx_wandterms_term_code (cost=0.00..32.15 rows=10 width=0) (actual time=0.011..0.011 rows=5 loops=1949) Index Cond: (y.term_code = ANY (z.target_kli))

HashAggregate  (cost=2506583.15..2506680.12 rows=9697 width=112) (actual time=25572.034..25572.078 rows=7 loops=1)
  ->  Hash Join  (cost=787303.52..2506347.26 rows=10484 width=112) (actual time=25157.597..25569.157 rows=2016 loops=1)
        Hash Cond: ((f.banner_id = z.banner_id) AND (upper((a.acct_desc)::text) = upper((y.term_desc)::text)))
        ->  Hash Join  (cost=666158.76..2373564.77 rows=209689 width=100) (actual time=25092.575..25468.449 rows=19800 loops=1)
              Hash Cond: ((d.cookie_id)::bpchar = f.id)
              Join Filter: ((a.date > f.create_time) AND (((a.date)::timestamp without time zone - '60 days'::interval) < f.create_time))
              ->  Hash Join  (cost=39647.40..52177.72 rows=44599 width=133) (actual time=1070.149..1114.193 rows=29955 loops=1)
                    Hash Cond: ((a.adic)::text = (d.adic)::text)
                    ->  Nested Loop  (cost=10240.84..17549.86 rows=2535 width=133) (actual time=215.323..227.845 rows=4866 loops=1)
                          ->  Seq Scan on sgmt_clients b  (cost=0.00..1.78 rows=1 width=38) (actual time=0.030..0.060 rows=1 loops=1)
                                Filter: (lower((userid)::text) = 'mchcu'::text)
                          ->  Bitmap Heap Scan on attribution_summary2 a  (cost=10240.84..17522.73 rows=2535 width=105) (actual time=215.283..222.870 rows=4866 loops=1)
                                Recheck Cond: ((a.date >= '2012-01-01'::date) AND (a.date <= '2012-01-31'::date) AND ((a.userid)::text = 'mchcu'::text))
                                ->  BitmapAnd  (cost=10240.84..10240.84 rows=2535 width=0) (actual time=214.703..214.703 rows=0 loops=1)
                                      ->  Bitmap Index Scan on attsumm_date2  (cost=0.00..2911.00 rows=159434 width=0) (actual time=51.836..51.836 rows=179635 loops=1)
                                            Index Cond: ((a.date >= '2012-01-01'::date) AND (a.date <= '2012-01-31'::date))
                                      ->  Bitmap Index Scan on attsumm_userid2  (cost=0.00..7328.32 rows=465155 width=0) (actual time=131.697..131.697 rows=510302 loops=1)
                                            Index Cond: ((a.userid)::text = 'mchcu'::text)
                    ->  Hash  (cost=19148.47..19148.47 rows=820647 width=74) (actual time=854.544..854.544 rows=820647 loops=1)
                          Buckets: 131072  Batches: 1  Memory Usage: 84950kB
                          ->  Seq Scan on sgmt_adic d  (cost=0.00..19148.47 rows=820647 width=74) (actual time=0.014..389.625 rows=820647 loops=1)
              ->  Hash  (cost=407961.16..407961.16 rows=17484016 width=49) (actual time=24017.395..24017.395 rows=17484016 loops=1)
                    Buckets: 2097152  Batches: 1  Memory Usage: 1502533kB
                    ->  Seq Scan on sgmt_user_tracker f  (cost=0.00..407961.16 rows=17484016 width=49) (actual time=0.021..10452.797 rows=17484016 loops=1)
        ->  Hash  (cost=120852.41..120852.41 rows=19490 width=42) (actual time=64.981..64.981 rows=9983 loops=1)
              Buckets: 2048  Batches: 1  Memory Usage: 730kB
              ->  Nested Loop  (cost=32.15..120852.41 rows=19490 width=42) (actual time=0.057..46.717 rows=9983 loops=1)
                    ->  Seq Scan on attr_lookup z  (cost=0.00..108.49 rows=1949 width=61) (actual time=0.008..0.820 rows=1949 loops=1)
                    ->  Bitmap Heap Scan on wandterms y  (cost=32.15..61.73 rows=10 width=26) (actual time=0.012..0.018 rows=5 loops=1949)
                          Recheck Cond: (y.term_code = ANY (z.target_kli))
                          ->  Bitmap Index Scan on idx_wandterms_term_code  (cost=0.00..32.15 rows=10 width=0) (actual time=0.011..0.011 rows=5 loops=1949)
                                Index Cond: (y.term_code = ANY (z.target_kli))
Total runtime: 25802.839 ms

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	2	21.810 ms	0.1 %
Bitmap Index Scan	3	204.972 ms	0.8 %
BitmapAnd	1	31.170 ms	0.1 %
Hash	3	14047.781 ms	54.9 %
Hash Join	3	404.392 ms	1.6 %
HashAggregate	1	2.921 ms	0.0 %
Nested Loop	2	15.730 ms	0.1 %
Seq Scan	4	10843.302 ms	42.4 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
attr_lookup	1	0.820 ms	0.0 %
Seq Scan	1	0.820 ms	100.0 %
attribution_summary2	1	8.167 ms	0.0 %
Bitmap Heap Scan	1	8.167 ms	100.0 %
sgmt_adic	1	389.625 ms	1.5 %
Seq Scan	1	389.625 ms	100.0 %
sgmt_clients	1	0.060 ms	0.0 %
Seq Scan	1	0.060 ms	100.0 %
sgmt_user_tracker	1	10452.797 ms	40.9 %
Seq Scan	1	10452.797 ms	100.0 %
wandterms	1	13.643 ms	0.1 %
Bitmap Heap Scan	1	13.643 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: g3F

Colorize:

Columns visibility:

Per node type stats

Per table stats