explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OEki

Settings

Optimization(s) for this plan:

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

Sort (cost=2,264,734.97..2,264,736.95 rows=790 width=525) (actual rows= loops=)

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

HashAggregate (cost=2,264,677.20..2,264,696.95 rows=790 width=525) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.00..2,264,427.05 rows=5,886 width=525) (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))))
4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.00..740,597.53 rows=810 width=492) (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)) = (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. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((gc_ordem_id)::text = 'A664F75994DB4992BC4DE86338C3DBA5'::text)
  • Filter: ((status_promocao IS NULL) OR ((status_promocao)::text <> 'PR'::text))
6. 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 = 'B203E145612C4A358B9DD90187283593'::text)
  • Filter: ((aluno.type)::text = 'AL'::text)
7. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: ((autorizacao_atual.gc_ordem_id)::text = 'B203E145612C4A358B9DD90187283593'::text)