Query 10x mais lenta com limit

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?