explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pSTb

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=227,757.90..227,758.37 rows=188 width=861) (actual rows= loops=)

  • Sort Key: autorizacao_anterior.data_cadastro
2. 0.000 0.000 ↓ 0.0

HashAggregate (cost=227,746.09..227,750.79 rows=188 width=861) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.00..227,694.87 rows=683 width=861) (actual rows= loops=)

  • 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((aluno.nome)::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((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)) = btrim(replace(upper(regexp_replace(translate((aluno.nome)::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((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)) = btrim(replace(upper(regexp_replace(translate((aluno.nome)::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 = (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(btrim(replace(upper(regexp_replace(translate((aluno.nome)::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((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)) = btrim(replace(upper(regexp_replace(translate((aluno.nome)::text, 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeeeEEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text), '[^a-zA-Z ]'::text, ''::text, 'g'::text)), ' '::text, ' '::text))))
4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.00..47,600.46 rows=188 width=819) (actual rows= loops=)

  • 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))))
5. 0.000 0.000 ↓ 0.0

Index Scan using gsi_autorizacao_gc_ordem_idx on gsi_autorizacao autorizacao_anterior (cost=0.00..212.58 rows=188 width=786) (actual rows= loops=)

  • Index Cond: ((gc_ordem_id)::text = '0BA7CAF0FE2140BEB1F713BD9B1E07EF'::text)
  • Filter: (status_promocao IS NULL)
6. 0.000 0.000 ↓ 0.0

Index Scan using gsi_autorizacao_gc_ordem_idx on gsi_autorizacao autorizacao_atual (cost=0.00..212.58 rows=188 width=285) (actual rows= loops=)

  • Index Cond: ((autorizacao_atual.gc_ordem_id)::text = 'D23417E3BB0748FEA72BFFDDC07D1ACF'::text)
7. 0.000 0.000 ↓ 0.0

Index Scan using gd_pessoa_gc_ordem on gd_pessoa aluno (cost=0.00..807.59 rows=716 width=83) (actual rows= loops=)

  • Index Cond: ((aluno.gc_ordem_id)::text = 'D23417E3BB0748FEA72BFFDDC07D1ACF'::text)
  • Filter: ((aluno.type)::text = 'AL'::text)