explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gxFFY

Settings
# exclusive inclusive rows x rows loops node
1. 4.642 572,959.652 ↓ 1.1 833 1

Sort (cost=2,264,734.97..2,264,736.95 rows=790 width=525) (actual time=572,958.197..572,959.652 rows=833 loops=1)

  • Sort Key: autorizacao_anterior.data_cadastro
  • Sort Method: quicksort Memory: 246kB
2. 13.926 572,955.010 ↓ 1.1 833 1

HashAggregate (cost=2,264,677.20..2,264,696.95 rows=790 width=525) (actual time=572,952.647..572,955.010 rows=833 loops=1)

3. 299,959.061 572,941.084 ↑ 6.1 960 1

Nested Loop Left Join (cost=0.00..2,264,427.05 rows=5,886 width=525) (actual time=570.957..572,941.084 rows=960 loops=1)

  • Join Filter: (((btrim(replace(upper(regexp_replace(translate((autorizacao_anterior.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text)) = btrim(replace(upper(regexp_replace(translate((autorizacao_atual.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text))) AND (regexp_replace(btrim((autorizacao_anterior.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text) = regexp_replace(btrim((autorizacao_atual.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text)) AND ((autorizacao_anterior.data_nascimento)::date = (autorizacao_atual.data_nascimento)::date)) OR ((btrim(replace(upper(regexp_replace(translate((autorizacao_anterior.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text)) = btrim(replace(upper(regexp_replace(translate((autorizacao_atual.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text))) AND (regexp_replace(btrim((autorizacao_anterior.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text) = regexp_replace(btrim((autorizacao_atual.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text))) OR ((btrim(replace(upper(regexp_replace(translate((autorizacao_anterior.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text)) = btrim(replace(upper(regexp_replace(translate((autorizacao_atual.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text))) AND ((autorizacao_anterior.data_nascimento)::date = (autorizacao_atual.data_nascimento)::date)) OR (("substring"(replace(btrim(replace(upper(regexp_replace(translate((autorizacao_anterior.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text)), ' '::text, ''::text), 1, 5) = "substring"(replace(btrim(replace(upper(regexp_replace(translate((autorizacao_atual.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text)), ' '::text, ''::text), 1, 5)) AND (regexp_replace(btrim((autorizacao_anterior.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text) = regexp_replace(btrim((autorizacao_atual.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text))) OR (btrim(replace(upper(regexp_replace(translate((autorizacao_anterior.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text)) = btrim(replace(upper(regexp_replace(translate((autorizacao_atual.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text))))
4. 264,077.371 269,668.241 ↓ 1.0 846 1

Nested Loop Left Join (cost=0.00..740,597.53 rows=810 width=492) (actual time=332.957..269,668.241 rows=846 loops=1)

  • Join Filter: (((btrim(replace(upper(regexp_replace(translate((autorizacao_anterior.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text)) = (aluno.nome)::text) AND (regexp_replace(btrim((autorizacao_anterior.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text) = regexp_replace(btrim((aluno.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text)) AND ((autorizacao_anterior.data_nascimento)::date = (aluno.date_nasc)::date)) OR ((btrim(replace(upper(regexp_replace(translate((autorizacao_anterior.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text)) = (aluno.nome)::text) AND (regexp_replace(btrim((autorizacao_anterior.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text) = regexp_replace(btrim((aluno.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text))) OR ((btrim(replace(upper(regexp_replace(translate((autorizacao_anterior.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text)) = (aluno.nome)::text) AND ((autorizacao_anterior.data_nascimento)::date = (aluno.date_nasc)::date)) OR (("substring"(replace(btrim(replace(upper(regexp_replace(translate((autorizacao_anterior.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text)), ' '::text, ''::text), 1, 5) = "substring"(replace((aluno.nome)::text, ' '::text, ''::text), 1, 5)) AND (regexp_replace(btrim((autorizacao_anterior.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text) = regexp_replace(btrim((aluno.num_matricula)::text, '0'::text), '[^a-zA-Z0-9]'::text, ''::text, 'g'::text))) OR (btrim(replace(upper(regexp_replace(translate((autorizacao_anterior.nome_aluno)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text)) = (aluno.nome)::text))
5. 14.884 14.884 ↓ 1.0 846 1

Index Scan using gsi_autorizacao_gc_ordem_idx on gsi_autorizacao autorizacao_anterior (cost=0.00..906.28 rows=810 width=450) (actual time=0.321..14.884 rows=846 loops=1)

  • Index Cond: ((gc_ordem_id)::text = 'A664F75994DB4992BC4DE86338C3DBA5'::text)
  • Filter: ((status_promocao IS NULL) OR ((status_promocao)::text <> 'PR'::text))
6. 5,575.986 5,575.986 ↓ 3.2 2,274 846

Index Scan using gd_pessoa_gc_ordem on gd_pessoa aluno (cost=0.00..807.59 rows=716 width=83) (actual time=0.042..6.591 rows=2,274 loops=846)

  • Index Cond: ((aluno.gc_ordem_id)::text = 'B203E145612C4A358B9DD90187283593'::text)
  • Filter: ((aluno.type)::text = 'AL'::text)
7. 3,313.782 3,313.782 ↑ 1.1 1,294 846

Index Scan using gsi_autorizacao_gc_ordem_idx on gsi_autorizacao autorizacao_atual (cost=0.00..1,580.53 rows=1,432 width=285) (actual time=0.031..3.917 rows=1,294 loops=846)

  • Index Cond: ((autorizacao_atual.gc_ordem_id)::text = 'B203E145612C4A358B9DD90187283593'::text)
Total runtime : 572,961.380 ms