#	exclusive	inclusive	rows x	rows	loops	node
1.	95.696	3066.615	↑ 1.0	1	1	→ Aggregate (cost=384458.81..384458.84 rows=1 width=56) (actual time=3066.615..3066.615 rows=1 loops=1) Total runtime: 3066.721 ms
2.	22.985	2970.919	↑ 4.3	41718	1	→ Hash Left Join (cost=4733.14..383561.83 rows=179395 width=56) (actual time=0.394..2970.919 rows=41718 loops=1) Hash Cond: (p.idficha = o.idficha)
3.	2934.815	2947.708	↑ 2.2	41718	1	→ Nested Loop (cost=4675.30..381261.57 rows=89697 width=32) (actual time=0.161..2947.708 rows=41718 loops=1) Join Filter: ((p.rest = 1) OR ((fn_getdaysinyearsfromdate(price.endday, price.endmonth, date_part('YEAR'::text, now())) - fn_getdaysinyearsfromdate(price.iniday, price.inimonth, ((date_part('YEAR'::text, now()))::intege
4.	0.893	0.893	↑ 1.2	375	1	→ Index Scan using index_price_idred on price (cost=0.00..1311.42 rows=451 width=16) (actual time=0.015..0.893 rows=375 loops=1) Index Cond: (idficha = 85220)
5.	10.991	12.000	↑ 3.7	123	375	→ Materialize (cost=4675.30..4679.81 rows=451 width=56) (actual time=0.000..0.032 rows=123 loops=375)
6.	0.090	1.009	↑ 3.7	123	1	→ Nested Loop (cost=0.00..4674.85 rows=451 width=56) (actual time=0.014..1.009 rows=123 loops=1)
7.	0.427	0.427	↑ 3.7	123	1	→ Index Scan using index_price_idred on price p (cost=0.00..1315.93 rows=451 width=60) (actual time=0.005..0.427 rows=123 loops=1) Index Cond: (idficha = 85220) Filter: ((pnight <> 0::numeric) OR (pweek <> 0::numeric) OR (pfortnight <> 0::numeric) OR (pmonth <> 0::numeric))
8.	0.492	0.492	↑ 1.0	1	123	→ Index Scan using idx_idcap_pricetable on cap_pricetable cp (cost=0.00..7.44 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=123) Index Cond: (cp.idcap_pricetable = p.idcap_pricetable)
9.	0.000	0.226	↓ 0.0	0	1	→ Hash (cost=57.81..57.81 rows=2 width=32) (actual time=0.226..0.226 rows=0 loops=1)
10.	0.000	0.226	↓ 0.0	0	1	→ Nested Loop (cost=0.00..57.81 rows=2 width=32) (actual time=0.226..0.226 rows=0 loops=1)
11.	0.226	0.226	↓ 0.0	0	1	→ Seq Scan on offer o (cost=0.00..41.23 rows=2 width=36) (actual time=0.226..0.226 rows=0 loops=1) Filter: (idficha = 85220)
12.	0.000	0.000	↓ 0.0		0	→ Index Scan using offerperiod_pkey on offerperiod op (cost=0.00..8.28 rows=1 width=4) (actual time=.. rows= loops=0) Index Cond: (op.idofferperiod = o.idofferperiod) Filter: ((op.active = 1) AND (now() >= op.startdate) AND (now() <= op.enddate))

Aggregate  (cost=384458.81..384458.84 rows=1 width=56) (actual time=3066.615..3066.615 rows=1 loops=1)
   ->  Hash Left Join  (cost=4733.14..383561.83 rows=179395 width=56) (actual time=0.394..2970.919 rows=41718 loops=1)
         Hash Cond: (p.idficha = o.idficha)
         ->  Nested Loop  (cost=4675.30..381261.57 rows=89697 width=32) (actual time=0.161..2947.708 rows=41718 loops=1)
               Join Filter: ((p.rest = 1) OR ((fn_getdaysinyearsfromdate(price.endday, price.endmonth, date_part('YEAR'::text, now())) - fn_getdaysinyearsfromdate(price.iniday, price.inimonth, ((date_part('YEAR'::text, now()))::intege
r)::double precision)) >= 7) OR (((fn_getdaysinyearsfromdate(price.endday, price.endmonth, date_part('YEAR'::text, now())) - fn_getdaysinyearsfromdate(price.iniday, price.inimonth, ((date_part('YEAR'::text, now()))::integer)::double p
recision)) >= (-359)) AND ((fn_getdaysinyearsfromdate(price.endday, price.endmonth, date_part('YEAR'::text, now())) - fn_getdaysinyearsfromdate(price.iniday, price.inimonth, ((date_part('YEAR'::text, now()))::integer)::double precisio
n)) <= 0)) OR ((p.endday = 1) AND (p.endmonth = 1) AND (p.iniday = 1) AND (p.inimonth = 1) AND (null2zero(p.easter) = 0)))
               ->  Index Scan using index_price_idred on price  (cost=0.00..1311.42 rows=451 width=16) (actual time=0.015..0.893 rows=375 loops=1)
                     Index Cond: (idficha = 85220)
               ->  Materialize  (cost=4675.30..4679.81 rows=451 width=56) (actual time=0.000..0.032 rows=123 loops=375)
                     ->  Nested Loop  (cost=0.00..4674.85 rows=451 width=56) (actual time=0.014..1.009 rows=123 loops=1)
                           ->  Index Scan using index_price_idred on price p  (cost=0.00..1315.93 rows=451 width=60) (actual time=0.005..0.427 rows=123 loops=1)
                                 Index Cond: (idficha = 85220)
                                 Filter: ((pnight <> 0::numeric) OR (pweek <> 0::numeric) OR (pfortnight <> 0::numeric) OR (pmonth <> 0::numeric))
                           ->  Index Scan using idx_idcap_pricetable on cap_pricetable cp  (cost=0.00..7.44 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=123)
                                 Index Cond: (cp.idcap_pricetable = p.idcap_pricetable)
         ->  Hash  (cost=57.81..57.81 rows=2 width=32) (actual time=0.226..0.226 rows=0 loops=1)
               ->  Nested Loop  (cost=0.00..57.81 rows=2 width=32) (actual time=0.226..0.226 rows=0 loops=1)
                     ->  Seq Scan on offer o  (cost=0.00..41.23 rows=2 width=36) (actual time=0.226..0.226 rows=0 loops=1)
                           Filter: (idficha = 85220)
                     ->  Index Scan using offerperiod_pkey on offerperiod op  (cost=0.00..8.28 rows=1 width=4) (never executed)
                           Index Cond: (op.idofferperiod = o.idofferperiod)
                           Filter: ((op.active = 1) AND (now() >= op.startdate) AND (now() <= op.enddate))
 Total runtime: 3066.721 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	95.696 ms	3.1 %
Hash	1	0.000 ms	0.0 %
Hash Left Join	1	22.985 ms	0.7 %
Index Scan	4	1.812 ms	0.1 %
Materialize	1	10.991 ms	0.4 %
Nested Loop	3	2934.905 ms	95.7 %
Seq Scan	1	0.226 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
cap_pricetable	1	0.492 ms	0.0 %
Index Scan	1	0.492 ms	100.0 %
offer	1	0.226 ms	0.0 %
Seq Scan	1	0.226 ms	100.0 %
offerperiod	1	0.000 ms	0.0 %
Index Scan	1	0.000 ms	0.0 %
price	2	1.320 ms	0.0 %
Index Scan	2	1.320 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: To9

Colorize:

Columns visibility:

Per node type stats

Per table stats