explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6f4h : meu explain

Settings
# exclusive inclusive rows x rows loops node
1. 0.050 248,672.590 ↓ 3.0 3 1

GroupAggregate (cost=10,786.45..10,786.59 rows=1 width=32) (actual time=248,672.551..248,672.590 rows=3 loops=1)

  • Group Key: (CASE WHEN (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) < 15::double precision) THEN 'entre 0 e 14 anos'::text WHEN ((date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) >= 15::double precision) AND (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) <= 29::double precision)) THEN 'entre 15 e 29 anos'::text WHEN ((date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) >= 30::double precision) AND (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) <= 59::double precision)) THEN 'entre 30 e 59 anos'::text WHEN (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) >= 60::double precision) THEN 'maior que 60 anos'::text ELSE NULL::text END)
2. 1.635 248,672.540 ↓ 344.0 344 1

Sort (cost=10,786.45..10,786.46 rows=1 width=32) (actual time=248,672.523..248,672.540 rows=344 loops=1)

  • Sort Key: (CASE WHEN (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) < 15::double precision) THEN 'entre 0 e 14 anos'::text WHEN ((date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) >= 15::double precision) AND (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) <= 29::double precision)) THEN 'entre 15 e 29 anos'::text WHEN ((date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) >= 30::double precision) AND (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) <= 59::double precision)) THEN 'entre 30 e 59 anos'::text WHEN (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (to_date(("substring"(c.retornomainframe, 125, 8)), 'ddMMyyyy'::text))::timestamp with time zone)) >= 60::double precision) THEN 'maior que 60 anos'::text ELSE NULL::text END)
  • Sort Method: quicksort Memory: 51kB
3. 512.301 248,670.905 ↓ 344.0 344 1

Nested Loop (cost=8,236.46..10,786.44 rows=1 width=32) (actual time=1,084.108..248,670.905 rows=344 loops=1)

  • Join Filter: ((inf.docidentidade)::text = (inf_1.docidentidade)::text)
  • Rows Removed by Join Filter: 2338252
4. 11.844 205.184 ↓ 4,446.0 4,446 1

HashAggregate (cost=8,225.79..8,225.80 rows=1 width=467) (actual time=197.073..205.184 rows=4,446 loops=1)

  • Group Key: inf_1.docidentidade, "substring"(c.retornomainframe, 125, 8)
5. 35.063 193.340 ↓ 10,756.0 10,756 1

Hash Join (cost=7,849.69..8,225.78 rows=1 width=467) (actual time=156.694..193.340 rows=10,756 loops=1)

  • Hash Cond: ((inf_1.docidentidade)::text = (c.query)::text)
6. 1.611 1.611 ↓ 1.0 12,646 1

Seq Scan on infrator inf_1 (cost=0.00..329.42 rows=12,442 width=11) (actual time=0.006..1.611 rows=12,646 loops=1)

7. 13.777 156.666 ↓ 20,718.0 20,718 1

Hash (cost=7,849.68..7,849.68 rows=1 width=465) (actual time=156.666..156.666 rows=20,718 loops=1)

  • Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 4097kB
8. 142.889 142.889 ↓ 20,718.0 20,718 1

Seq Scan on consulta c (cost=0.00..7,849.68 rows=1 width=465) (actual time=0.049..142.889 rows=20,718 loops=1)

  • Filter: ((query IS NOT NULL) AND ((query)::text <> ''::text) AND ((operacao)::text = 'P53'::text) AND ("substring"(retornomainframe, 125, 8) !~~ '% %'::text) AND ("substring"(retornomainframe, 125, 8) !~~ '% %'::text) AND ("substring"(retornomainframe, 1, 3) = '000'::text))
  • Rows Removed by Filter: 46295
9. 1,636.128 247,953.420 ↓ 263.0 526 4,446

Nested Loop (cost=10.67..2,560.49 rows=2 width=11) (actual time=0.735..55.770 rows=526 loops=4,446)

10. 115,693.812 241,640.100 ↓ 263.0 526 4,446

Nested Loop (cost=10.39..2,548.50 rows=2 width=8) (actual time=0.730..54.350 rows=526 loops=4,446)

  • Join Filter: (u.id = i.usuario_id)
  • Rows Removed by Join Filter: 314594
11. 63,524.448 63,524.448 ↓ 25.2 1,560 4,446

Seq Scan on infracao i (cost=0.00..2,484.08 rows=62 width=16) (actual time=0.715..14.288 rows=1,560 loops=4,446)

  • Filter: (((status = 0) OR (status = 3)) AND (to_char(datacometimento, 'MM/yyyy'::text) = '09/2019'::text))
  • Rows Removed by Filter: 11652
12. 62,421.535 62,421.840 ↓ 11.9 202 6,935,760

Materialize (cost=10.39..48.66 rows=17 width=8) (actual time=0.000..0.009 rows=202 loops=6,935,760)

13. 0.207 0.305 ↓ 11.9 202 1

Hash Join (cost=10.39..48.57 rows=17 width=8) (actual time=0.064..0.305 rows=202 loops=1)

  • Hash Cond: (u.orgaouserselected_id = o.id)
14. 0.086 0.086 ↑ 1.0 510 1

Seq Scan on usuario u (cost=0.00..36.10 rows=510 width=16) (actual time=0.007..0.086 rows=510 loops=1)

15. 0.002 0.012 ↑ 1.0 1 1

Hash (cost=10.38..10.38 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
16. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on orgao o (cost=0.00..10.38 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)

  • Filter: ((codigo)::text = '102100'::text)
  • Rows Removed by Filter: 2
17. 4,677.192 4,677.192 ↑ 1.0 1 2,338,596

Index Scan using infrator_pkey on infrator inf (cost=0.29..5.98 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=2,338,596)

  • Index Cond: (id = i.infrator_id)
Planning time : 2.298 ms
Execution time : 248,672.844 ms