#	exclusive	inclusive	rows x	rows	loops	node
1.	320.085	163534.672	↑ 1.0	1	1	→ Aggregate (cost=15781380.69..15781380.70 rows=1 width=0) (actual time=163534.672..163534.672 rows=1 loops=1) Output: count(*)
2.	99018.692	163214.587	↓ 2.4	1090869	1	→ WindowAgg (cost=8945652.51..15775778.55 rows=448171 width=643) (actual time=9158.722..163214.587 rows=1090869 loops=1) Output: sf.id, min(sf.id) OVER (?), rank() OVER (?), ((round(((sf.outcome_odds - 1::double precision) * 100::double precision)))::integer / CASE WHEN ((round(((sf.outcom
3.	2031.936	9485.965	↓ 2.4	1090869	1	→ Sort (cost=8945652.51..8946772.94 rows=448171 width=643) (actual time=9158.498..9485.965 rows=1090869 loops=1) Output: sf.id, sf.outcome_odds, od.category0, od.sport, od.region, od.category1, od.category2, od.category3, od.season_name, od.game_name, od.outcome_set_type, sf. Sort Key: sf.src_bet_id, sf.skin_dim_id, sf.id Sort Method: quicksort Memory: 233220kB
4.	3090.553	7454.029	↓ 2.4	1090869	1	→ Nested Loop (cost=0.00..8903583.40 rows=448171 width=643) (actual time=0.061..7454.029 rows=1090869 loops=1) Output: sf.id, sf.outcome_odds, od.category0, od.sport, od.region, od.category1, od.category2, od.category3, od.season_name, od.game_name, od.outcome_set_typ -> Index Scan using selection_fact_by_resolve on public.selection_fact sf (cost=0.00..601220.82 rows=448171 width=28) (actual time=0.046..1778.174 rows=109 Output: sf.id, sf.outcome_odds, sf.src_bet_id, sf.skin_dim_id, sf.outcome_dim_id Index Cond: (sf.resolve >= '2012-04-29 00:00:00'::timestamp without time zone)
5.	4363.476	4363.476	↑ 1.0	1	1090869	→ Index Scan using outcome_dim_pkey on public.outcome_dim od (cost=0.00..18.51 rows=1 width=631) (actual time=0.004..0.004 rows=1 loops=1090869) Output: od.breadcrumb, od.outcome_set_special, od.created, od.betting_start, od.betting_end, od.game_start, od.game_end, od.sport, od.region, od.outcom Index Cond: (od.id = sf.outcome_dim_id)
6.						SubPlan (forWindowAgg)
7.	7636.083	7636.083	↑ 1.0	1	1090869	→ Seq Scan on public.report_emv_sport_map (cost=0.00..2.03 rows=1 width=10) (actual time=0.007..0.007 rows=1 loops=1090869) Output: public.report_emv_sport_map.category Filter: ((public.report_emv_sport_map.sport)::text = ($0)::text)
8.	8.188	8.188	↑ 1.0	1	356	→ Seq Scan on public.report_emv_region_map (cost=0.00..5.28 rows=1 width=11) (actual time=0.004..0.023 rows=1 loops=356) Output: public.report_emv_region_map.category Filter: ((public.report_emv_region_map.region)::text = ($2)::text)
9.	7633.591	7633.591	↑ 1.0	1	1090513	→ Seq Scan on public.report_emv_sport_map (cost=0.00..2.03 rows=1 width=10) (actual time=0.006..0.007 rows=1 loops=1090513) Output: public.report_emv_sport_map.category Filter: ((public.report_emv_sport_map.sport)::text = ($0)::text)
10.	17448.208	17448.208	↑ 1.0	1	1090513	→ Seq Scan on public.report_emv_region_map (cost=0.00..5.28 rows=1 width=11) (actual time=0.010..0.016 rows=1 loops=1090513) Output: public.report_emv_region_map.category Filter: ((public.report_emv_region_map.region)::text = ($2)::text)
11.	4960.060	4960.060	↑ 1.0	1	708580	→ Seq Scan on public.report_emv_sport_map (cost=0.00..2.03 rows=1 width=10) (actual time=0.006..0.007 rows=1 loops=708580) Output: public.report_emv_sport_map.category Filter: ((public.report_emv_sport_map.sport)::text = ($0)::text)
12.	17005.920	17005.920	↑ 1.0	1	708580	→ Seq Scan on public.report_emv_region_map (cost=0.00..5.28 rows=1 width=11) (actual time=0.011..0.024 rows=1 loops=708580) Output: public.report_emv_region_map.category Filter: ((public.report_emv_region_map.region)::text = ($2)::text)
13.	4.172	4.172	↑ 1.0	1	596	→ Seq Scan on public.report_emv_sport_map (cost=0.00..2.03 rows=1 width=10) (actual time=0.007..0.007 rows=1 loops=596) Output: public.report_emv_sport_map.category Filter: ((public.report_emv_sport_map.sport)::text = ($0)::text)
14.	13.708	13.708	↑ 1.0	1	596	→ Seq Scan on public.report_emv_region_map (cost=0.00..5.28 rows=1 width=11) (actual time=0.023..0.023 rows=1 loops=596) Output: public.report_emv_region_map.category Filter: ((public.report_emv_region_map.region)::text = ($2)::text)

 Aggregate  (cost=15781380.69..15781380.70 rows=1 width=0) (actual time=163534.672..163534.672 rows=1 loops=1)
   Output: count(*)
   ->  WindowAgg  (cost=8945652.51..15775778.55 rows=448171 width=643) (actual time=9158.722..163214.587 rows=1090869 loops=1)
         Output: sf.id, min(sf.id) OVER (?), rank() OVER (?), ((round(((sf.outcome_odds - 1::double precision) * 100::double precision)))::integer / CASE WHEN ((round(((sf.outcom
e_odds - 1::double precision) * 100::double precision)))::integer = 0) THEN 100 ELSE gcd((round(((sf.outcome_odds - 1::double precision) * 100::double precision)))::integer, (100
 % (round(((sf.outcome_odds - 1::double precision) * 100::double precision)))::integer)) END), (100 / CASE WHEN ((round(((sf.outcome_odds - 1::double precision) * 100::double pre
cision)))::integer = 0) THEN 100 ELSE gcd((round(((sf.outcome_odds - 1::double precision) * 100::double precision)))::integer, (100 % (round(((sf.outcome_odds - 1::double precisi
on) * 100::double precision)))::integer)) END), CASE WHEN ((NOT (SubPlan 1)) AND (NOT (SubPlan 2))) THEN od.category0 WHEN ((NOT (SubPlan 3)) AND (NOT (SubPlan 4))) THEN od.categ
ory1 WHEN ((NOT (SubPlan 5)) AND (NOT (SubPlan 6))) THEN od.category2 WHEN ((NOT (SubPlan 7)) AND (NOT (SubPlan 8))) THEN od.category3 ELSE NULL::character varying END, od.sport,
 od.season_name, od.game_name, od.region, od.outcome_set_type, sf.src_bet_id, sf.skin_dim_id
         ->  Sort  (cost=8945652.51..8946772.94 rows=448171 width=643) (actual time=9158.498..9485.965 rows=1090869 loops=1)
               Output: sf.id, sf.outcome_odds, od.category0, od.sport, od.region, od.category1, od.category2, od.category3, od.season_name, od.game_name, od.outcome_set_type, sf.
src_bet_id, sf.skin_dim_id
               Sort Key: sf.src_bet_id, sf.skin_dim_id, sf.id
               Sort Method:  quicksort  Memory: 233220kB
               ->  Nested Loop  (cost=0.00..8903583.40 rows=448171 width=643) (actual time=0.061..7454.029 rows=1090869 loops=1)
                     Output: sf.id, sf.outcome_odds, od.category0, od.sport, od.region, od.category1, od.category2, od.category3, od.season_name, od.game_name, od.outcome_set_typ
e, sf.src_bet_id, sf.skin_dim_id
                     ->  Index Scan using selection_fact_by_resolve on public.selection_fact sf  (cost=0.00..601220.82 rows=448171 width=28) (actual time=0.046..1778.174 rows=109
0869 loops=1)
                           Output: sf.id, sf.outcome_odds, sf.src_bet_id, sf.skin_dim_id, sf.outcome_dim_id
                           Index Cond: (sf.resolve >= '2012-04-29 00:00:00'::timestamp without time zone)
                     ->  Index Scan using outcome_dim_pkey on public.outcome_dim od  (cost=0.00..18.51 rows=1 width=631) (actual time=0.004..0.004 rows=1 loops=1090869)
                           Output: od.breadcrumb, od.outcome_set_special, od.created, od.betting_start, od.betting_end, od.game_start, od.game_end, od.sport, od.region, od.outcom
e_state, od.comments_internal, od.comments_external, od.outcome_set_type, od.high_risk, od.singles_only, od.outcome_name, od.id, od.responsible_employee_dim_id, od.audit_employee
_dim_id, od.maxwin_user, od.maxwin_all, od.category0, od.category1, od.category2, od.category3, od.category4, od.category5, od.category6, od.category7, od.category8, od.game_end_
date, od.category9, od.category10, od.participant, od.category11, od.category12, od.category13, od.category14, od.game_name, od.season_name
                           Index Cond: (od.id = sf.outcome_dim_id)

         SubPlan 1
           ->  Seq Scan on public.report_emv_sport_map  (cost=0.00..2.03 rows=1 width=10) (actual time=0.007..0.007 rows=1 loops=1090869)
                 Output: public.report_emv_sport_map.category
                 Filter: ((public.report_emv_sport_map.sport)::text = ($0)::text)
         SubPlan 2
           ->  Seq Scan on public.report_emv_region_map  (cost=0.00..5.28 rows=1 width=11) (actual time=0.004..0.023 rows=1 loops=356)
                 Output: public.report_emv_region_map.category
                 Filter: ((public.report_emv_region_map.region)::text = ($2)::text)
         SubPlan 3
           ->  Seq Scan on public.report_emv_sport_map  (cost=0.00..2.03 rows=1 width=10) (actual time=0.006..0.007 rows=1 loops=1090513)
                 Output: public.report_emv_sport_map.category
                 Filter: ((public.report_emv_sport_map.sport)::text = ($0)::text)
         SubPlan 4
           ->  Seq Scan on public.report_emv_region_map  (cost=0.00..5.28 rows=1 width=11) (actual time=0.010..0.016 rows=1 loops=1090513)
                 Output: public.report_emv_region_map.category
                 Filter: ((public.report_emv_region_map.region)::text = ($2)::text)
         SubPlan 5
           ->  Seq Scan on public.report_emv_sport_map  (cost=0.00..2.03 rows=1 width=10) (actual time=0.006..0.007 rows=1 loops=708580)
                 Output: public.report_emv_sport_map.category
                 Filter: ((public.report_emv_sport_map.sport)::text = ($0)::text)
         SubPlan 6
           ->  Seq Scan on public.report_emv_region_map  (cost=0.00..5.28 rows=1 width=11) (actual time=0.011..0.024 rows=1 loops=708580)
                 Output: public.report_emv_region_map.category
                 Filter: ((public.report_emv_region_map.region)::text = ($2)::text)
         SubPlan 7
           ->  Seq Scan on public.report_emv_sport_map  (cost=0.00..2.03 rows=1 width=10) (actual time=0.007..0.007 rows=1 loops=596)
                 Output: public.report_emv_sport_map.category
                 Filter: ((public.report_emv_sport_map.sport)::text = ($0)::text)
         SubPlan 8
           ->  Seq Scan on public.report_emv_region_map  (cost=0.00..5.28 rows=1 width=11) (actual time=0.023..0.023 rows=1 loops=596)
                 Output: public.report_emv_region_map.category
                 Filter: ((public.report_emv_region_map.region)::text = ($2)::text)
 Total runtime: 163550.893 ms
(49 rows)

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	320.085 ms	0.2 %
Index Scan	1	4363.476 ms	2.7 %
Nested Loop	1	3090.553 ms	1.9 %
Seq Scan	8	54709.930 ms	33.5 %
Sort	1	2031.936 ms	1.2 %
WindowAgg	1	99018.692 ms	60.5 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
public.outcome_dim	1	4363.476 ms	2.7 %
Index Scan	1	4363.476 ms	100.0 %
public.report_emv_region_map	4	34476.024 ms	21.1 %
Seq Scan	4	34476.024 ms	100.0 %
public.report_emv_sport_map	4	20233.906 ms	12.4 %
Seq Scan	4	20233.906 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: EU5l

Colorize:

Columns visibility:

Per node type stats

Per table stats