Query 10x mais lenta com limit

1. Query 10x mais lenta com limit

Carlos Eduardo Duarte Schwalm
krlsedu

(usa Ubuntu)

Enviado em 09/09/2019 - 09:48h

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?


  






Patrocínio

Site hospedado pelo provedor RedeHost.
Linux banner
Linux banner
Linux banner

Destaques

Artigos

Dicas

Tópicos

Top 10 do mês

Scripts