explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pl4o

Settings
# exclusive inclusive rows x rows loops node
1. 5,847.601 108,751.529 ↓ 151.6 156,153 1

HashAggregate (cost=214,035.11..237,071.06 rows=1,030 width=714) (actual time=106,284.745..108,751.529 rows=156,153 loops=1)

  • Buffers: shared hit=2,611,647 read=27,082, temp read=6,997 written=6,991
2. 23,376.926 102,903.928 ↓ 151.6 156,153 1

Nested Loop (cost=180,091.33..213,908.94 rows=1,030 width=714) (actual time=45,721.813..102,903.928 rows=156,153 loops=1)

  • Buffers: shared hit=2,611,647 read=27,082, temp read=6,997 written=6,991
3. 6,284.797 54,854.828 ↓ 151.6 156,153 1

Hash Left Join (cost=180,090.91..190,214.85 rows=1,030 width=672) (actual time=45,720.486..54,854.828 rows=156,153 loops=1)

  • Hash Cond: (cli.codigo = quantidadeacessosporcliente.cliente)
  • Filter: ((CASE WHEN (quantidadeacessosporcliente.quantidadeacessos IS NULL) THEN 0::bigint ELSE quantidadeacessosporcliente.quantidadeacessos END >= 0) AND (CASE WHEN (quantidadeacessosporcliente.quantidadeacessos IS NULL) THEN 0::bigint ELSE (...)
  • Rows Removed by Filter: 49,792
  • Buffers: shared hit=2 read=26,871, temp read=6,997 written=6,991
4. 2,850.250 2,850.250 ↑ 1.0 205,945 1

Seq Scan on cliente cli (cost=0.00..4,457.45 rows=205,945 width=664) (actual time=0.291..2,850.250 rows=205,945 loops=1)

  • Buffers: shared hit=2 read=2,396
5. 778.976 45,719.781 ↓ 249.0 49,792 1

Hash (cost=180,088.41..180,088.41 rows=200 width=12) (actual time=45,719.781..45,719.781 rows=49,792 loops=1)

  • Buckets: 1,024 Batches: 4 (originally 1) Memory Usage: 1,025kB
  • Buffers: shared read=24,475, temp read=5,235 written=5,379
6. 1,347.996 44,940.805 ↓ 249.0 49,792 1

Subquery Scan on quantidadeacessosporcliente (cost=163,846.53..180,088.41 rows=200 width=12) (actual time=19,488.162..44,940.805 rows=49,792 loops=1)

  • Buffers: shared read=24,475, temp read=5,235 written=5,235
7. 5,991.451 43,592.809 ↓ 249.0 49,792 1

GroupAggregate (cost=163,846.53..180,086.41 rows=200 width=4) (actual time=19,488.141..43,592.809 rows=49,792 loops=1)

  • Buffers: shared read=24,475, temp read=5,235 written=5,235
8. 11,105.191 37,601.358 ↑ 1.6 367,477 1

Group (cost=163,846.53..171,393.67 rows=579,383 width=35) (actual time=19,488.003..37,601.358 rows=367,477 loops=1)

  • Buffers: shared read=24,475, temp read=5,235 written=5,235
9. 15,041.956 26,496.167 ↑ 1.5 402,382 1

Sort (cost=163,846.53..165,371.20 rows=609,868 width=35) (actual time=19,487.979..26,496.167 rows=402,382 loops=1)

  • Sort Key: acessocliente.cliente, (to_char(acessocliente.dthrentrada, 'YYYY-MM-DD'::text)), acessocliente.situacao
  • Sort Method: external merge Disk: 18,880kB
  • Buffers: shared read=24,475, temp read=5,235 written=5,235
10. 11,454.211 11,454.211 ↑ 1.5 402,382 1

Seq Scan on acessocliente (cost=0.00..71,888.90 rows=609,868 width=35) (actual time=0.597..11,454.211 rows=402,382 loops=1)

  • Filter: (((situacao)::text = ANY ('{RV_LIBACESSOAUTORIZADO,RV_LIBACESSOAUTORIZADOGYMPASS}'::text[])) AND (date_part('day'::text, (now() - (dthrentrada)::timestamp with time zone)) <= 28::double precision))
  • Rows Removed by Filter: 1,433,187
  • Buffers: shared read=24,475
11. 5,621.508 5,621.508 ↑ 1.0 1 156,153

Index Scan using ind_situacaoclientesinteticodw_codigocliente on situacaoclientesinteticodw sw (cost=0.42..0.64 rows=1 width=46) (actual time=0.023..0.036 rows=1 loops=156,153)

  • Index Cond: (codigocliente = cli.codigo)
  • Buffers: shared hit=625,253 read=211
12.          

SubPlan (for Nested Loop)

13. 8,432.262 8,432.262 ↑ 1.0 1 156,153

Seq Scan on empresa (cost=0.00..5.47 rows=1 width=118) (actual time=0.027..0.054 rows=1 loops=156,153)

  • Filter: (codigo = cli.empresa)
  • Rows Removed by Filter: 37
  • Buffers: shared hit=780,765
14. 5,153.049 5,153.049 ↑ 1.0 1 156,153

Index Scan using ch_telefone_pessoa on telefone (cost=0.42..8.44 rows=1 width=13) (actual time=0.022..0.033 rows=1 loops=156,153)

  • Index Cond: (pessoa = cli.pessoa)
  • Buffers: shared hit=580,330
15. 5,465.355 5,465.355 ↑ 1.0 1 156,153

Index Scan using ind_pessoa on email (cost=0.42..8.44 rows=1 width=25) (actual time=0.022..0.035 rows=1 loops=156,153)

  • Index Cond: (pessoa = cli.pessoa)
  • Buffers: shared hit=625,297
Total runtime : 110,900.107 ms