#	exclusive	inclusive	rows x	rows	loops	node
1.	1736.003	10516.887	↓ 143.8	267868	1	→ Nested Loop Left Join (cost=130.63..186614.60 rows=1863 width=351) (actual time=41.510..10516.887 rows=267868 loops=1) Buffers: shared hit=3438375 read=12484 written=2186
2.	444.890	5730.544	↓ 143.8	267868	1	→ Hash Join (cost=130.63..32384.14 rows=1863 width=347) (actual time=41.448..5730.544 rows=267868 loops=1) Hash Cond: (produto.cdgrupoproduto = grupoproduto.cdgrupoproduto) Buffers: shared hit=1746741 read=11747 written=2066
3.	495.829	5285.583	↓ 143.8	267868	1	→ Hash Join (cost=114.56..32342.45 rows=1863 width=129) (actual time=41.366..5285.583 rows=267868 loops=1) Hash Cond: (lancamento.cdproduto = produto.cdproduto) Buffers: shared hit=1746741 read=11746 written=2066
4.	1338.995	4785.454	↓ 143.8	267868	1	→ Nested Loop (cost=0.00..32197.62 rows=1863 width=96) (actual time=37.050..4785.454 rows=267868 loops=1) Buffers: shared hit=1746686 read=11746 written=2066
5.	1102.144	2344.747	↓ 141.7	275428	1	→ Nested Loop (cost=0.00..25987.02 rows=1944 width=68) (actual time=37.035..2344.747 rows=275428 loops=1) Buffers: shared hit=674075 read=11435 written=1969
6.	407.373	407.373	↓ 141.4	167046	1	→ Seq Scan on lanccaixa (cost=0.00..17796.89 rows=1181 width=16) (actual time=37.004..407.373 rows=167046 loops=1) Filter: ((cdempresa = 1) AND (cdoperacaocaixa = 1) AND (flcancelado = 0) AND (date(dhlanccaixa) >= '2011-05-01'::date) AND (date(dhlanccaixa) < '2012-03-21'::date)) Buffers: shared hit=2 read=10631 written=1856
7.	835.230	835.230	↑ 1.5	2	167046	→ Index Scan using ixlctocdlanccaixa on lancamento (cost=0.00..6.90 rows=3 width=64) (actual time=0.003..0.005 rows=2 loops=167046) Index Cond: (cdlanccaixa = lanccaixa.cdlanccaixa) Filter: (cdempresa = 1) Buffers: shared hit=674073 read=804 written=113
8.	1101.712	1101.712	↑ 1.0	1	275428	→ Index Scan using pkdocfiscalitem on docfiscalitem (cost=0.00..3.18 rows=1 width=40) (actual time=0.003..0.004 rows=1 loops=275428) Index Cond: (cddocfiscalitem = lancamento.cddocfiscalitem) Filter: (cdempresa = 1) Buffers: shared hit=1072611 read=311 written=97
9.	2.160	4.300	↑ 1.0	2647	1	→ Hash (cost=81.47..81.47 rows=2647 width=33) (actual time=4.300..4.300 rows=2647 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 175kB Buffers: shared hit=55
10.	2.140	2.140	↑ 1.0	2647	1	→ Seq Scan on produto (cost=0.00..81.47 rows=2647 width=33) (actual time=0.004..2.140 rows=2647 loops=1) Buffers: shared hit=55
11.	0.032	0.071	↑ 8.2	33	1	→ Hash (cost=12.70..12.70 rows=270 width=222) (actual time=0.071..0.071 rows=33 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB Buffers: shared read=1
12.	0.039	0.039	↑ 8.2	33	1	→ Seq Scan on grupoproduto (cost=0.00..12.70 rows=270 width=222) (actual time=0.014..0.039 rows=33 loops=1) Buffers: shared read=1
13.	1339.340	1339.340	↑ 1.0	1	267868	→ Index Scan using ixcustocdempdtcdp on custo (cost=0.00..6.99 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=267868) Index Cond: ((date(lanccaixa.dhlanccaixa) = dtcusto) AND (lancamento.cdproduto = cdproduto) AND (cdempresa = 1) AND (cdempresa = lancamento.cdempresa)) Buffers: shared hit=1068077 read=351 written=80
14.						SubPlan (forNested Loop Left Join)
15.	232.000	1711.000	↑ 1.0	1	29000	→ Aggregate (cost=75.75..75.76 rows=1 width=16) (actual time=0.059..0.059 rows=1 loops=29000) Buffers: shared hit=623557 read=386 written=40
16.	733.660	1479.000	↑ 2.3	3	29000	→ Nested Loop (cost=0.00..75.72 rows=7 width=16) (actual time=0.014..0.051 rows=3 loops=29000) Buffers: shared hit=623557 read=386 written=40
17.	203.000	203.000	↑ 1.6	5	29000	→ Index Scan using ixlanccomposicaocdl on lanccomposicao (cost=0.00..8.41 rows=8 width=12) (actual time=0.003..0.007 rows=5 loops=29000) Index Cond: (cdlancamento = lancamento.cdlancamento) Buffers: shared hit=114730 read=386 written=40
18.	542.340	542.340	↑ 1.0	1	135585	→ Index Scan using ixcustocdempdtcdp on custo (cost=0.00..8.40 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=135585) Index Cond: ((dtcusto = date(lanccaixa.dhlanccaixa)) AND (cdproduto = lanccomposicao.cdproduto) AND (cdempresa = lancamento.cdempresa)) Buffers: shared hit=508827

"Nested Loop Left Join  (cost=130.63..186614.60 rows=1863 width=351) (actual time=41.510..10516.887 rows=267868 loops=1)"
"  Buffers: shared hit=3438375 read=12484 written=2186"
"  ->  Hash Join  (cost=130.63..32384.14 rows=1863 width=347) (actual time=41.448..5730.544 rows=267868 loops=1)"
"        Hash Cond: (produto.cdgrupoproduto = grupoproduto.cdgrupoproduto)"
"        Buffers: shared hit=1746741 read=11747 written=2066"
"        ->  Hash Join  (cost=114.56..32342.45 rows=1863 width=129) (actual time=41.366..5285.583 rows=267868 loops=1)"
"              Hash Cond: (lancamento.cdproduto = produto.cdproduto)"
"              Buffers: shared hit=1746741 read=11746 written=2066"
"              ->  Nested Loop  (cost=0.00..32197.62 rows=1863 width=96) (actual time=37.050..4785.454 rows=267868 loops=1)"
"                    Buffers: shared hit=1746686 read=11746 written=2066"
"                    ->  Nested Loop  (cost=0.00..25987.02 rows=1944 width=68) (actual time=37.035..2344.747 rows=275428 loops=1)"
"                          Buffers: shared hit=674075 read=11435 written=1969"
"                          ->  Seq Scan on lanccaixa  (cost=0.00..17796.89 rows=1181 width=16) (actual time=37.004..407.373 rows=167046 loops=1)"
"                                Filter: ((cdempresa = 1) AND (cdoperacaocaixa = 1) AND (flcancelado = 0) AND (date(dhlanccaixa) >= '2011-05-01'::date) AND (date(dhlanccaixa) < '2012-03-21'::date))"
"                                Buffers: shared hit=2 read=10631 written=1856"
"                          ->  Index Scan using ixlctocdlanccaixa on lancamento  (cost=0.00..6.90 rows=3 width=64) (actual time=0.003..0.005 rows=2 loops=167046)"
"                                Index Cond: (cdlanccaixa = lanccaixa.cdlanccaixa)"
"                                Filter: (cdempresa = 1)"
"                                Buffers: shared hit=674073 read=804 written=113"
"                    ->  Index Scan using pkdocfiscalitem on docfiscalitem  (cost=0.00..3.18 rows=1 width=40) (actual time=0.003..0.004 rows=1 loops=275428)"
"                          Index Cond: (cddocfiscalitem = lancamento.cddocfiscalitem)"
"                          Filter: (cdempresa = 1)"
"                          Buffers: shared hit=1072611 read=311 written=97"
"              ->  Hash  (cost=81.47..81.47 rows=2647 width=33) (actual time=4.300..4.300 rows=2647 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 175kB"
"                    Buffers: shared hit=55"
"                    ->  Seq Scan on produto  (cost=0.00..81.47 rows=2647 width=33) (actual time=0.004..2.140 rows=2647 loops=1)"
"                          Buffers: shared hit=55"
"        ->  Hash  (cost=12.70..12.70 rows=270 width=222) (actual time=0.071..0.071 rows=33 loops=1)"
"              Buckets: 1024  Batches: 1  Memory Usage: 2kB"
"              Buffers: shared read=1"
"              ->  Seq Scan on grupoproduto  (cost=0.00..12.70 rows=270 width=222) (actual time=0.014..0.039 rows=33 loops=1)"
"                    Buffers: shared read=1"
"  ->  Index Scan using ixcustocdempdtcdp on custo  (cost=0.00..6.99 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=267868)"
"        Index Cond: ((date(lanccaixa.dhlanccaixa) = dtcusto) AND (lancamento.cdproduto = cdproduto) AND (cdempresa = 1) AND (cdempresa = lancamento.cdempresa))"
"        Buffers: shared hit=1068077 read=351 written=80"
"  SubPlan 1"
"    ->  Aggregate  (cost=75.75..75.76 rows=1 width=16) (actual time=0.059..0.059 rows=1 loops=29000)"
"          Buffers: shared hit=623557 read=386 written=40"
"          ->  Nested Loop  (cost=0.00..75.72 rows=7 width=16) (actual time=0.014..0.051 rows=3 loops=29000)"
"                Buffers: shared hit=623557 read=386 written=40"
"                ->  Index Scan using ixlanccomposicaocdl on lanccomposicao  (cost=0.00..8.41 rows=8 width=12) (actual time=0.003..0.007 rows=5 loops=29000)"
"                      Index Cond: (cdlancamento = lancamento.cdlancamento)"
"                      Buffers: shared hit=114730 read=386 written=40"
"                ->  Index Scan using ixcustocdempdtcdp on custo  (cost=0.00..8.40 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=135585)"
"                      Index Cond: ((dtcusto = date(lanccaixa.dhlanccaixa)) AND (cdproduto = lanccomposicao.cdproduto) AND (cdempresa = lancamento.cdempresa))"
"                      Buffers: shared hit=508827"
"Total runtime: 10707.476 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	232.000 ms	2.2 %
Hash	2	2.192 ms	0.0 %
Hash Join	2	940.719 ms	8.9 %
Index Scan	5	4021.622 ms	38.2 %
Nested Loop	3	3174.799 ms	30.2 %
Nested Loop Left Join	1	1736.003 ms	16.5 %
Seq Scan	3	409.552 ms	3.9 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
custo	2	1881.680 ms	17.9 %
Index Scan	2	1881.680 ms	100.0 %
docfiscalitem	1	1101.712 ms	10.5 %
Index Scan	1	1101.712 ms	100.0 %
grupoproduto	1	0.039 ms	0.0 %
Seq Scan	1	0.039 ms	100.0 %
lancamento	1	835.230 ms	7.9 %
Index Scan	1	835.230 ms	100.0 %
lanccaixa	1	407.373 ms	3.9 %
Seq Scan	1	407.373 ms	100.0 %
lanccomposicao	1	203.000 ms	1.9 %
Index Scan	1	203.000 ms	100.0 %
produto	1	2.140 ms	0.0 %
Seq Scan	1	2.140 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: fWr

Colorize:

Columns visibility:

Per node type stats

Per table stats