#	exclusive	inclusive	rows x	rows	loops	node
1.	0.062	113381.486	↑ 1.0	50	1	→ Limit (cost=12404.28..12404.78 rows=50 width=125) (actual time=113381.359..113381.486 rows=50 loops=1)
2.	106.383	113381.424	↑ 261.9	50	1	→ HashAggregate (cost=12404.28..12535.25 rows=13097 width=125) (actual time=113381.356..113381.424 rows=50 loops=1)
3.	78260.484	113275.041	↓ 3.5	45209	1	→ Nested Loop (cost=3589.12..12306.06 rows=13097 width=125) (actual time=0.798..113275.041 rows=45209 loops=1) Join Filter: (cdm.hier_orig_node_num ~~* ANY (hdm.original_node_num))
4.	3.622	3.732	↓ 197.5	1185	1	→ Bitmap Heap Scan on hdm_05_tdct_csr hdm (cost=4.30..15.07 rows=6 width=32) (actual time=0.121..3.732 rows=1185 loops=1) Recheck Cond: (level1 = 1)
5.	0.110	0.110	↓ 197.5	1185	1	→ Bitmap Index Scan on hdm_05_tdct_csr_level1_index (cost=0.00..4.30 rows=6 width=0) (actual time=0.110..0.110 rows=1185 loops=1) Index Cond: (level1 = 1)
6.	34951.377	35010.825	↓ 1.0	45209	1185	→ Materialize (cost=3584.82..4031.29 rows=44647 width=129) (actual time=0.001..29.545 rows=45209 loops=1185)
7.	59.448	59.448	↓ 1.0	45209	1	→ Seq Scan on rdm_05_test2_rdm cdm (cost=0.00..3540.18 rows=44647 width=129) (actual time=0.008..59.448 rows=45209 loops=1) Filter: ((case_date >= 20111101) AND (case_date <= 20120131))

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=12404.28..12404.78 rows=50 width=125) (actual time=113381.359..113381.486 rows=50 loops=1)
   ->  HashAggregate  (cost=12404.28..12535.25 rows=13097 width=125) (actual time=113381.356..113381.424 rows=50 loops=1)
         ->  Nested Loop  (cost=3589.12..12306.06 rows=13097 width=125) (actual time=0.798..113275.041 rows=45209 loops=1)
               Join Filter: (cdm.hier_orig_node_num ~~* ANY (hdm.original_node_num))
               ->  Bitmap Heap Scan on hdm_05_tdct_csr hdm  (cost=4.30..15.07 rows=6 width=32) (actual time=0.121..3.732 rows=1185 loops=1)
                     Recheck Cond: (level1 = 1)
                     ->  Bitmap Index Scan on hdm_05_tdct_csr_level1_index  (cost=0.00..4.30 rows=6 width=0) (actual time=0.110..0.110 rows=1185 loops=1)
                           Index Cond: (level1 = 1)
               ->  Materialize  (cost=3584.82..4031.29 rows=44647 width=129) (actual time=0.001..29.545 rows=45209 loops=1185)
                     ->  Seq Scan on rdm_05_test2_rdm cdm  (cost=0.00..3540.18 rows=44647 width=129) (actual time=0.008..59.448 rows=45209 loops=1)
                           Filter: ((case_date >= 20111101) AND (case_date <= 20120131))

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	3.622 ms	0.0 %
Bitmap Index Scan	1	0.110 ms	0.0 %
HashAggregate	1	106.383 ms	0.1 %
Limit	1	0.062 ms	0.0 %
Materialize	1	34951.377 ms	30.8 %
Nested Loop	1	78260.484 ms	69.0 %
Seq Scan	1	59.448 ms	0.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
hdm_05_tdct_csr	1	3.622 ms	0.0 %
Bitmap Heap Scan	1	3.622 ms	100.0 %
rdm_05_test2_rdm	1	59.448 ms	0.1 %
Seq Scan	1	59.448 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: ORK

Colorize:

Columns visibility:

Per node type stats

Per table stats