#	exclusive	inclusive	rows x	rows	loops	node
1.	0.001	0.873	↑ 1.0	1	1	→ Nested Loop (cost=5.42..732.33 rows=1 width=32) (actual time=0.841..0.873 rows=1 loops=1)
2.	0.006	0.843	↑ 1.0	1	1	→ Nested Loop (cost=5.42..724.04 rows=1 width=39) (actual time=0.813..0.843 rows=1 loops=1)
3.	0.005	0.117	↑ 1.0	1	1	→ Nested Loop (cost=0.00..55.49 rows=1 width=39) (actual time=0.116..0.117 rows=1 loops=1)
4.	0.082	0.082	↑ 1.0	1	1	→ Index Scan using ra1wqaix on edb_prod_ver pv (cost=0.00..47.16 rows=1 width=32) (actual time=0.082..0.082 rows=1 loops=1) Index Cond: (prod_id = '38M5045D500'::bpchar) Filter: ((strt_dt <= '2012-12-12'::date) AND (end_dt >= '2012-12-12'::date) AND ((data_verif_stts_txt)::text = ANY ('{Active,"Active Pending Replacement"}'::text[])))
5.	0.030	0.030	↑ 1.0	1	1	→ Index Scan using fr703aix on edb_spec ps (cost=0.00..8.32 rows=1 width=19) (actual time=0.030..0.030 rows=1 loops=1) Index Cond: (spec_id = '38M5045D500'::bpchar)
6.	0.032	0.720	↑ 1.0	1	1	→ Nested Loop (cost=5.42..668.54 rows=1 width=12) (actual time=0.692..0.720 rows=1 loops=1)
7.	0.051	0.110	↑ 2.5	17	1	→ Bitmap Heap Scan on edb_spec_xref xr (cost=5.42..318.54 rows=42 width=24) (actual time=0.091..0.110 rows=17 loops=1) Recheck Cond: ((spec_id_left = '38M5045D500'::bpchar) AND (strt_dt <= '2012-12-12'::date)) Filter: (end_dt >= '2012-12-12'::date)
8.	0.059	0.059	↑ 1.6	52	1	→ Bitmap Index Scan on fr707bix (cost=0.00..5.41 rows=82 width=0) (actual time=0.059..0.059 rows=52 loops=1) Index Cond: ((spec_id_left = '38M5045D500'::bpchar) AND (strt_dt <= '2012-12-12'::date))
9.	0.578	0.578	↓ 0.0	0	17	→ Index Scan using fr703aix on edb_spec s (cost=0.00..8.32 rows=1 width=12) (actual time=0.034..0.034 rows=0 loops=17) Index Cond: (spec_id = xr.spec_id_right) Filter: (sys_name = 'APPLY_FOR_POLICY'::bpchar)
10.	0.029	0.029	↑ 1.0	1	1	→ Index Scan using fr705gix on edb_prod p (cost=0.00..8.28 rows=1 width=17) (actual time=0.027..0.029 rows=1 loops=1) Index Cond: (prod_id = '38M5045D500'::bpchar) Filter: ((strt_dt <= '2012-12-12'::date) AND (end_dt >= '2012-12-12'::date))

"Nested Loop  (cost=5.42..732.33 rows=1 width=32) (actual time=0.841..0.873 rows=1 loops=1)"
"  ->  Nested Loop  (cost=5.42..724.04 rows=1 width=39) (actual time=0.813..0.843 rows=1 loops=1)"
"        ->  Nested Loop  (cost=0.00..55.49 rows=1 width=39) (actual time=0.116..0.117 rows=1 loops=1)"
"              ->  Index Scan using ra1wqaix on edb_prod_ver pv  (cost=0.00..47.16 rows=1 width=32) (actual time=0.082..0.082 rows=1 loops=1)"
"                    Index Cond: (prod_id = '38M5045D500'::bpchar)"
"                    Filter: ((strt_dt <= '2012-12-12'::date) AND (end_dt >= '2012-12-12'::date) AND ((data_verif_stts_txt)::text = ANY ('{Active,"Active Pending Replacement"}'::text[])))"
"              ->  Index Scan using fr703aix on edb_spec ps  (cost=0.00..8.32 rows=1 width=19) (actual time=0.030..0.030 rows=1 loops=1)"
"                    Index Cond: (spec_id = '38M5045D500'::bpchar)"
"        ->  Nested Loop  (cost=5.42..668.54 rows=1 width=12) (actual time=0.692..0.720 rows=1 loops=1)"
"              ->  Bitmap Heap Scan on edb_spec_xref xr  (cost=5.42..318.54 rows=42 width=24) (actual time=0.091..0.110 rows=17 loops=1)"
"                    Recheck Cond: ((spec_id_left = '38M5045D500'::bpchar) AND (strt_dt <= '2012-12-12'::date))"
"                    Filter: (end_dt >= '2012-12-12'::date)"
"                    ->  Bitmap Index Scan on fr707bix  (cost=0.00..5.41 rows=82 width=0) (actual time=0.059..0.059 rows=52 loops=1)"
"                          Index Cond: ((spec_id_left = '38M5045D500'::bpchar) AND (strt_dt <= '2012-12-12'::date))"
"              ->  Index Scan using fr703aix on edb_spec s  (cost=0.00..8.32 rows=1 width=12) (actual time=0.034..0.034 rows=0 loops=17)"
"                    Index Cond: (spec_id = xr.spec_id_right)"
"                    Filter: (sys_name = 'APPLY_FOR_POLICY'::bpchar)"
"  ->  Index Scan using fr705gix on edb_prod p  (cost=0.00..8.28 rows=1 width=17) (actual time=0.027..0.029 rows=1 loops=1)"
"        Index Cond: (prod_id = '38M5045D500'::bpchar)"
"        Filter: ((strt_dt <= '2012-12-12'::date) AND (end_dt >= '2012-12-12'::date))"
"Total runtime: 1.118 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	0.051 ms	5.8 %
Bitmap Index Scan	1	0.059 ms	6.8 %
Index Scan	4	0.719 ms	82.4 %
Nested Loop	4	0.044 ms	5.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
edb_prod	1	0.029 ms	3.3 %
Index Scan	1	0.029 ms	100.0 %
edb_prod_ver	1	0.082 ms	9.4 %
Index Scan	1	0.082 ms	100.0 %
edb_spec	2	0.608 ms	69.6 %
Index Scan	2	0.608 ms	100.0 %
edb_spec_xref	1	0.051 ms	5.8 %
Bitmap Heap Scan	1	0.051 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: GRu

Colorize:

Columns visibility:

Per node type stats

Per table stats