Result: cri

options

HTML
TEXT
STATS

exclusive	inclusive	rows x	rows	loops	node
0.143	4624.603	↓ 59.0	59	1	→ HashAggregate (cost=773492.74..773492.75 rows=1 width=34) (actual time=4624.581..4624.603 rows=59 loops=1)
0.052	4624.460	↓ 13.0	78	1	→ Nested Loop (cost=5341.32..773492.69 rows=6 width=34) (actual time=3654.220..4624.460 rows=78 loops=1)
0.034	0.034	↑ 1.0	1	1	→ Index Scan using v2organization_pkey on v2organization (cost=0.00..8.27 rows=1 width=26) (actual time=0.033..0.034 rows=1 loops=1) Index Cond: (id = 473)
63.800	4624.374	↓ 13.0	78	1	→ Bitmap Heap Scan on overtime_report (cost=5341.32..773484.36 rows=6 width=32) (actual time=3654.180..4624.374 rows=78 loops=1) Recheck Cond: ((ot_period_start >= '2011-10-29 00:00:00'::timestamp without time zone) AND (ot_period_start < '2012-01-07 00:00:00'::timestamp without time zone)) Filter: ((v2org_id = 473) AND (snapshot_datetime = (SubPlan 2)))
44.974	44.974	↑ 1.0	251536	1	→ Bitmap Index Scan on overtime_report_ot_period_start_idx (cost=0.00..5341.32 rows=254104 width=0) (actual time=44.974..44.974 rows=251536 loops=1) Index Cond: ((ot_period_start >= '2011-10-29 00:00:00'::timestamp without time zone) AND (ot_period_start < '2012-01-07 00:00:00'::timestamp without time zone))
					SubPlan (forBitmap Heap Scan)
2.840	4515.600	↑ 1.0	1	1420	→ Result (cost=2.97..2.98 rows=1 width=0) (actual time=3.180..3.180 rows=1 loops=1420)
					Initplan (forResult)
1.420	4512.760	↑ 1.0	1	1420	→ Limit (cost=0.00..2.97 rows=1 width=8) (actual time=3.178..3.178 rows=1 loops=1420)
4511.340	4511.340	↑ 4467.0	1	1420	→ Index Scan Backward using overtime_report_snapshot_datetime_idx on overtime_report oreport (cost=0.00..13278.00 rows=4467 width=8) (actual time=3.177..3.177 rows=1 loops=1420) Index Cond: (snapshot_datetime IS NOT NULL) Filter: (ot_period_start = overtime_report.ot_period_start)

"HashAggregate  (cost=773492.74..773492.75 rows=1 width=34) (actual time=4624.581..4624.603 rows=59 loops=1)"
"  ->  Nested Loop  (cost=5341.32..773492.69 rows=6 width=34) (actual time=3654.220..4624.460 rows=78 loops=1)"
"        ->  Index Scan using v2organization_pkey on v2organization  (cost=0.00..8.27 rows=1 width=26) (actual time=0.033..0.034 rows=1 loops=1)"
"              Index Cond: (id = 473)"
"        ->  Bitmap Heap Scan on overtime_report  (cost=5341.32..773484.36 rows=6 width=32) (actual time=3654.180..4624.374 rows=78 loops=1)"
"              Recheck Cond: ((ot_period_start >= '2011-10-29 00:00:00'::timestamp without time zone) AND (ot_period_start < '2012-01-07 00:00:00'::timestamp without time zone))"
"              Filter: ((v2org_id = 473) AND (snapshot_datetime = (SubPlan 2)))"
"              ->  Bitmap Index Scan on overtime_report_ot_period_start_idx  (cost=0.00..5341.32 rows=254104 width=0) (actual time=44.974..44.974 rows=251536 loops=1)"
"                    Index Cond: ((ot_period_start >= '2011-10-29 00:00:00'::timestamp without time zone) AND (ot_period_start < '2012-01-07 00:00:00'::timestamp without time zone))"
"              SubPlan 2"
"                ->  Result  (cost=2.97..2.98 rows=1 width=0) (actual time=3.180..3.180 rows=1 loops=1420)"
"                      InitPlan 1 (returns $1)"
"                        ->  Limit  (cost=0.00..2.97 rows=1 width=8) (actual time=3.178..3.178 rows=1 loops=1420)"
"                              ->  Index Scan Backward using overtime_report_snapshot_datetime_idx on overtime_report oreport  (cost=0.00..13278.00 rows=4467 width=8) (actual time=3.177..3.177 rows=1 loops=1420)"
"                                    Index Cond: (snapshot_datetime IS NOT NULL)"
"                                    Filter: (ot_period_start = overtime_report.ot_period_start)"
"Total runtime: 4624.714 ms"

Per node type stats

node type	count	sum of times	% of query
Bitmap Heap Scan	1	63.800 ms	1.4 %
Bitmap Index Scan	1	44.974 ms	1.0 %
HashAggregate	1	0.143 ms	0.0 %
Index Scan	1	0.034 ms	0.0 %
Index Scan Backward	1	4511.340 ms	97.6 %
Limit	1	1.420 ms	0.0 %
Nested Loop	1	0.052 ms	0.0 %
Result	1	2.840 ms	0.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
overtime_report	2	4575.140 ms	98.9 %
Bitmap Heap Scan	1	63.800 ms	1.4 %
Index Scan Backward	1	4511.340 ms	98.6 %
v2organization	1	0.034 ms	0.0 %
Index Scan	1	0.034 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: cri

Colorize:

Columns visibility:

Per node type stats

Per table stats