Bom dia,
Estou com um problema em uma query que tem nível de complexidade bem baixo, ela faz join de 3 tabelas, com um total de aproximadamente 170 mil registros, possui duas cláusulas where, order by e limit
Meu problema é o seguinte:
A performance está excelente quando o limit é usado trazendo em poucos ms os resultados, porém quando o resultado fica abaixo do tamanho do limit, o desempenho cai e muito, a mesma query, quando o resultado é de 6 registros, por exemplo, leva mais de 1 s, tirando o limit, o desempenho volta ao normal.
O script é utilizado no "auto-complete" de um cadastro, então o desempenho é crucial. A aplicação possui core em Java + hibernate, sendo assim na consulta não consigo usar CTE, que resolve a questão de desempenho.
select paciente0_.i_paciente as col_0_0_,
paciente0_.cns as col_1_0_,
pessoa1_.nome as col_2_0_
from saude.pacientes paciente0_
left outer join
global.pessoas pessoa1_ on paciente0_.i_pessoa = pessoa1_.i_pessoa
left outer join
global.entidades entidade2_ on pessoa1_.i_entidade = entidade2_.i_entidade
where (
retira_acentuacao(lower(pessoa1_.nome)) like '%jh%'
)
and retira_acentuacao(lower(entidade2_.chave_publica)) = 'fb5t'
order by paciente0_.i_paciente
limit 10
Verifiquei os plan e obtive o seguinte:
Plan com limit
[
{
"Plan": {
"Node Type": "Limit",
"Parallel Aware": false,
"Startup Cost": 0.84,
"Total Cost": 417.20,
"Plan Rows": 10,
"Plan Width": 46,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.84,
"Total Cost": 64870.16,
"Plan Rows": 1558,
"Plan Width": 46,
"Inner Unique": true,
"Join Filter": "(pessoa1_.i_entidade = entidade2_.i_entidade)",
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.84,
"Total Cost": 64471.47,
"Plan Rows": 26493,
"Plan Width": 54,
"Inner Unique": true,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pacientes_pkey",
"Relation Name": "pacientes",
"Alias": "paciente0_",
"Startup Cost": 0.42,
"Total Cost": 3526.54,
"Plan Rows": 132466,
"Plan Width": 32
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pessoas_pkey",
"Relation Name": "pessoas",
"Alias": "pessoa1_",
"Startup Cost": 0.42,
"Total Cost": 0.46,
"Plan Rows": 1,
"Plan Width": 38,
"Index Cond": "(i_pessoa = paciente0_.i_pessoa)",
"Filter": "(translate(lower((nome)::text), 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeee&EEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text) ~~ '%jh%'::text)"
}
]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 1.30,
"Plan Rows": 1,
"Plan Width": 8,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "entidades",
"Alias": "entidade2_",
"Startup Cost": 0.00,
"Total Cost": 1.30,
"Plan Rows": 1,
"Plan Width": 8,
"Filter": "(translate(lower((chave_publica)::text), 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeee&EEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text) = 'fb5t'::text)"
}
]
}
]
}
]
}
}
]
Plan sem limit:
[
{
"Plan": {
"Node Type": "Sort",
"Parallel Aware": false,
"Startup Cost": 4593.96,
"Total Cost": 4597.86,
"Plan Rows": 1558,
"Plan Width": 46,
"Sort Key": ["paciente0_.i_paciente"],
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2064.48,
"Total Cost": 4511.34,
"Plan Rows": 1558,
"Plan Width": 46,
"Inner Unique": true,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2064.06,
"Total Cost": 3683.70,
"Plan Rows": 1671,
"Plan Width": 30,
"Inner Unique": false,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "entidades",
"Alias": "entidade2_",
"Startup Cost": 0.00,
"Total Cost": 1.30,
"Plan Rows": 1,
"Plan Width": 8,
"Filter": "(translate(lower((chave_publica)::text), 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeee&EEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text) = 'fb5t'::text)"
},
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "pessoas",
"Alias": "pessoa1_",
"Startup Cost": 2064.06,
"Total Cost": 3664.64,
"Plan Rows": 1776,
"Plan Width": 38,
"Recheck Cond": "(i_entidade = entidade2_.i_entidade)",
"Filter": "(translate(lower((nome)::text), 'áàâãäåaaaÁÂÃÄÅAAAÀéèêëeeeee&EEEÉEEÈìíîïìiiiÌÍÎÏÌIIIóôõöoooòÒÓÔÕÖOOOùúûüuuuuÙÚÛÜUUUUçÇñÑýÝ'::text, 'aaaaaaaaaAAAAAAAAAeeeeeeeeeeEEEEEEEiiiiiiiiIIIIIIIIooooooooOOOOOOOOuuuuuuuuUUUUUUUUcCnNyY'::text) ~~ '%jh%'::text)",
"Plans": [
{
"Node Type": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Index Name": "idx_paciente_entidade_nome",
"Startup Cost": 0.00,
"Total Cost": 2063.61,
"Plan Rows": 8879,
"Plan Width": 0,
"Index Cond": "(i_entidade = entidade2_.i_entidade)"
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "uka5u1krnjq1ycjebf0j5vcac0b",
"Relation Name": "pacientes",
"Alias": "paciente0_",
"Startup Cost": 0.42,
"Total Cost": 0.50,
"Plan Rows": 1,
"Plan Width": 32,
"Index Cond": "(i_pessoa = pessoa1_.i_pessoa)"
}
]
}
]
}
}
]
Minha dúvida, há algo que possa fazer para que o postgres não "se perca" quando tenta limitar algo que não necessita limitar?
