Lentitud en consulta de Personas

SIU-Guaraní SIU-Guaraní - Técnicos
1

Buen dia:

Nos encontramos en versión 3.18.1, pasamos hace poco a la misma. Estamos teniendo demoras considerables en la operación “Administrar Personas” al realizar la búsqueda por Identificación, no así cuando se realiza por Nombre y Apellido.

Llegamos a identificar al SQL que produce la demora, esta se da solamente cuando debe buscar teniendo en cuenta el Perfil de Datos. Si ejecutamos la consulta por PgAdmin demora alrededor de 5 minutos en un entorno de Desarrollo, mientra que via Gestión en Producción demora alrededor de 10 minutos.

La demora se produce en el archivo co_personas en la función get_personas_perfil_de_datos, específicamente esta sería la consulta:

SELECT vw_perfil_de_datos_personas.persona, vw_perfil_de_datos_personas.apellido, vw_perfil_de_datos_personas.nombres, vw_perfil_de_datos_personas.tipo_nro_documento as documento
FROM vw_perfil_de_datos_personas
WHERE true AND f_limpiar_acentos(nro_documento::varchar) ILIKE '%25985556%'
UNION SELECT mdp_personas.persona, mdp_personas.apellido, mdp_personas.nombres, mdp_tipo_documento.desc_abreviada || ' ' || mdp_personas_documentos.nro_documento as documento
FROM mdp_personas
LEFT JOIN mdp_personas_perfil_datos ON mdp_personas_perfil_datos.persona = mdp_personas.persona
LEFT JOIN desarrollo.apex_usuario_perfil_datos_dims ON mdp_personas_perfil_datos.usuario_perfil_datos = desarrollo.apex_usuario_perfil_datos_dims.usuario_perfil_datos
LEFT JOIN mdp_personas_documentos ON mdp_personas_documentos.documento = mdp_personas.documento_principal
LEFT JOIN mdp_tipo_documento ON mdp_tipo_documento.tipo_documento = mdp_personas_documentos.tipo_documento
WHERE mdp_personas.persona NOT IN ( SELECT sga_alumnos.persona FROM sga_alumnos
UNION SELECT sga_docentes.persona FROM sga_docentes)
AND (mdp_personas_perfil_datos.usuario_perfil_datos IS NULL OR desarrollo.apex_usuario_perfil_datos_dims.clave IN ('2') )
AND f_limpiar_acentos(nro_documento::varchar) ILIKE '%25%'
GROUP BY mdp_personas.persona, mdp_tipo_documento.desc_abreviada, mdp_personas_documentos.nro_documento
ORDER BY apellido, nombres

Como podríamos reducir esas demoras?

Saludos

Ezequiel Molina
Fac. de Cs. Agrarias - UNJu

2

Buenas tardes.Vamos a ponernos a analizar este caso.Podrías indicarnos para tener una medida de la dimensión de la tabla cuantos registros tiene la tabla mdp_personas
Saludos.

3

Buenas Tardes:

Tenemos 87662 registros.

Esperamos novedades, nos avisan si hay algo que podamos ir haciendo o probando.

Gracias!

4

Hola Ezequiel, lo verificamos y te respondemos cuando tengamos alguna solución.
Por favor hace algunas pruebas:

  1. Corre por separado las dos querys que hay
  2. Luego reemplazá
f_limpiar_acentos(nro_documento::varchar) ILIKE '%25985556%'

por

nro_documento ILIKE '%25985556%'

y por:

nro_documento = '25985556'

Fijate si podes detectar cual de las dos querys es la que consume el mayor tiempo de procesamiento y si ademas sacando la funcion “f_limpiar_acentos” mejora la consulta.

Saludos.

5

Alejandro

No existe mucha diferencia entre uno y otro, la versión original demora 5.10 minutos y la modificada 5.09.

Saludos

6

Que raro, lo probé en una base con 700000 registros en la tabla mdp_personas, y tardó unos 4 o 5 segundos.
Por lo que veo al filtrar por nro de documento y que no sea el “=”, hace que realice una búsqueda secuencial al indice del nro de documento, si pongo = ‘25…’ ahi si hace un index scan y no un sequencial scan sobre el indice para buscar ese nro de documento.
¿El índice id_tipo_documento_nro_documento esta creado en la base?

Por favor enviá el resultado del explain:

EXPLAIN 
SELECT vw_perfil_de_datos_personas.persona, 
vw_perfil_de_datos_personas.apellido, 
vw_perfil_de_datos_personas.nombres, 
vw_perfil_de_datos_personas.tipo_nro_documento as documento
FROM vw_perfil_de_datos_personas
WHERE true 
AND f_limpiar_acentos(nro_documento::varchar) ILIKE '%25985556%'

UNION 

SELECT mdp_personas.persona, 
mdp_personas.apellido, 
mdp_personas.nombres, mdp_tipo_documento.desc_abreviada || ' ' || mdp_personas_documentos.nro_documento as documento
FROM mdp_personas
LEFT JOIN mdp_personas_perfil_datos ON mdp_personas_perfil_datos.persona = mdp_personas.persona
LEFT JOIN desarrollo.apex_usuario_perfil_datos_dims ON mdp_personas_perfil_datos.usuario_perfil_datos = desarrollo.apex_usuario_perfil_datos_dims.usuario_perfil_datos
JOIN mdp_personas_documentos ON mdp_personas_documentos.documento = mdp_personas.documento_principal
JOIN mdp_tipo_documento ON mdp_tipo_documento.tipo_documento = mdp_personas_documentos.tipo_documento
WHERE mdp_personas.persona NOT IN 
  (SELECT sga_alumnos.persona FROM sga_alumnos
   UNION 
   SELECT sga_docentes.persona FROM sga_docentes)
AND (mdp_personas_perfil_datos.usuario_perfil_datos IS NULL OR desarrollo.apex_usuario_perfil_datos_dims.clave IN ('2') )
AND f_limpiar_acentos(nro_documento::varchar) ILIKE '%25985556%'
GROUP BY mdp_personas.persona, mdp_tipo_documento.desc_abreviada, mdp_personas_documentos.nro_documento
ORDER BY apellido, nombres
7

El índice id_tipo_documento_nro_documento está presente en la tabla mdp_personas_documentos, lo que no tiene la misma, es la FK que lo relacione con mdp_personas, uno de nuestros compañeros revisó la versión 3.17 y si tenía esa FK, de todas formas, en un entorno de pruebas agregó la misma y no mejoró la situación.

Envío el resultado del Explain:


"Sort  (cost=126927926.10..126927926.13 rows=10 width=312)"
"  Sort Key: mdp_personas.apellido, mdp_personas.nombres"
"  ->  HashAggregate  (cost=126927925.84..126927925.94 rows=10 width=312)"
"        Group Key: mdp_personas.persona, mdp_personas.apellido, mdp_personas.nombres, ((((mdp_tipo_documento.desc_abreviada)::text || ' '::text) || (mdp_personas_documentos.nro_documento)::text))"
"        ->  Append  (cost=2950.75..126927925.74 rows=10 width=312)"
"              ->  Hash Left Join  (cost=2950.75..5984.60 rows=9 width=59)"
"                    Hash Cond: (mdp_personas_documentos.tipo_documento = mdp_tipo_documento.tipo_documento)"
"                    ->  Hash Join  (cost=2949.50..5983.27 rows=9 width=39)"
"                          Hash Cond: (mdp_personas.documento_principal = mdp_personas_documentos.documento)"
"                          ->  Seq Scan on mdp_personas  (cost=0.00..2798.03 rows=89803 width=31)"
"                          ->  Hash  (cost=2949.32..2949.32 rows=14 width=16)"
"                                ->  Seq Scan on mdp_personas_documentos  (cost=0.00..2949.32 rows=14 width=16)"
"                                      Filter: (translate((nro_documento)::text, 'ÀÁÂÃÄÅàáâãäåÒÓÔÕÖØòóôõöøÈÉÊËèéêëÇçÌÍÎÏìíîïÙÚÛÜùúûüÿ'::text, 'AAAAAAaaaaaaOOOOOOooooooEEEEeeeeCcIIIIiiiiUUUUuuuuy'::text) ~~* '%25985556%'::text)"
"                    ->  Hash  (cost=1.11..1.11 rows=11 width=5)"
"                          ->  Seq Scan on mdp_tipo_documento  (cost=0.00..1.11 rows=11 width=5)"
"              ->  Subquery Scan on "*SELECT* 2"  (cost=126921940.97..126921940.99 rows=1 width=59)"
"                    ->  Group  (cost=126921940.97..126921940.98 rows=1 width=72)"
"                          Group Key: mdp_personas_1.persona, mdp_tipo_documento_1.desc_abreviada, mdp_personas_documentos_1.nro_documento"
"                          ->  Sort  (cost=126921940.97..126921940.97 rows=1 width=40)"
"                                Sort Key: mdp_personas_1.persona, mdp_tipo_documento_1.desc_abreviada, mdp_personas_documentos_1.nro_documento"
"                                ->  Nested Loop  (cost=4883.63..126921940.96 rows=1 width=40)"
"                                      Join Filter: (mdp_personas_documentos_1.documento = mdp_personas_1.documento_principal)"
"                                      ->  Merge Left Join  (cost=4883.63..126918314.82 rows=3207 width=31)"
"                                            Merge Cond: (mdp_personas_1.persona = mdp_personas_perfil_datos.persona)"
"                                            Filter: ((mdp_personas_perfil_datos.usuario_perfil_datos IS NULL) OR (apex_usuario_perfil_datos_dims.clave = '2'::text))"
"                                            ->  Index Scan using pk_mdp_personas on mdp_personas mdp_personas_1  (cost=4851.84..126918166.34 rows=44902 width=31)"
"                                                  Filter: (NOT (SubPlan 1))"
"                                                  SubPlan 1"
"                                                    ->  Materialize  (cost=4851.55..7347.85 rows=132020 width=4)"
"                                                          ->  HashAggregate  (cost=4851.55..6171.75 rows=132020 width=4)"
"                                                                Group Key: sga_alumnos.persona"
"                                                                ->  Append  (cost=0.00..4521.50 rows=132020 width=4)"
"                                                                      ->  Seq Scan on sga_alumnos  (cost=0.00..2511.14 rows=130214 width=4)"
"                                                                      ->  Seq Scan on sga_docentes  (cost=0.00..30.06 rows=1806 width=4)"
"                                            ->  Sort  (cost=31.79..32.78 rows=394 width=14)"
"                                                  Sort Key: mdp_personas_perfil_datos.persona"
"                                                  ->  Hash Left Join  (cost=1.32..14.80 rows=394 width=14)"
"                                                        Hash Cond: (mdp_personas_perfil_datos.usuario_perfil_datos = apex_usuario_perfil_datos_dims.usuario_perfil_datos)"
"                                                        ->  Seq Scan on mdp_personas_perfil_datos  (cost=0.00..7.94 rows=394 width=12)"
"                                                        ->  Hash  (cost=1.14..1.14 rows=14 width=10)"
"                                                              ->  Seq Scan on apex_usuario_perfil_datos_dims  (cost=0.00..1.14 rows=14 width=10)"
"                                      ->  Materialize  (cost=0.00..2952.70 rows=14 width=17)"
"                                            ->  Nested Loop  (cost=0.00..2952.63 rows=14 width=17)"
"                                                  Join Filter: (mdp_personas_documentos_1.tipo_documento = mdp_tipo_documento_1.tipo_documento)"
"                                                  ->  Seq Scan on mdp_personas_documentos mdp_personas_documentos_1  (cost=0.00..2949.32 rows=14 width=16)"
"                                                        Filter: (translate((nro_documento)::text, 'ÀÁÂÃÄÅàáâãäåÒÓÔÕÖØòóôõöøÈÉÊËèéêëÇçÌÍÎÏìíîïÙÚÛÜùúûüÿ'::text, 'AAAAAAaaaaaaOOOOOOooooooEEEEeeeeCcIIIIiiiiUUUUuuuuy'::text) ~~* '%25985556%'::text)"
"                                                  ->  Materialize  (cost=0.00..1.17 rows=11 width=5)"
"                                                        ->  Seq Scan on mdp_tipo_documento mdp_tipo_documento_1  (cost=0.00..1.11 rows=11 width=5)"
8

Por favor verificá si tienen creados estos indices y fk:
Tabla: mdp_personas
iu_mdp_personas_apellido_nombres

Tabla: mdp_personas_documentos
id_tipo_documento_nro_documento
iu_pais_tipo_nro_documento
iu_persona_pais_tipo_documento

fk en la tabla mdp_personas_documentos:
fk_mdp_personas_documentos_mdp_personas

¿Tienen creada la PK en la tabla mdp_personas_documentos?

ALTER TABLE mdp_personas_documentos ADD CONSTRAINT pk_mdp_personas_documentos PRIMARY KEY (documento);

Envía el resultado de la siguiente consulta (cambie los ILIKE por =)

EXPLAIN
SELECT vw_perfil_de_datos_personas.persona, 
vw_perfil_de_datos_personas.apellido, 
vw_perfil_de_datos_personas.nombres, 
vw_perfil_de_datos_personas.tipo_nro_documento as documento
FROM vw_perfil_de_datos_personas
WHERE true 
AND nro_documento = '25985556'

UNION 

SELECT mdp_personas.persona, 
mdp_personas.apellido, 
mdp_personas.nombres, mdp_tipo_documento.desc_abreviada || ' ' || mdp_personas_documentos.nro_documento as documento
FROM mdp_personas
LEFT JOIN mdp_personas_perfil_datos ON mdp_personas_perfil_datos.persona = mdp_personas.persona
LEFT JOIN desarrollo.apex_usuario_perfil_datos_dims ON mdp_personas_perfil_datos.usuario_perfil_datos = desarrollo.apex_usuario_perfil_datos_dims.usuario_perfil_datos
LEFT JOIN mdp_personas_documentos ON mdp_personas_documentos.documento = mdp_personas.documento_principal
LEFT JOIN mdp_tipo_documento ON mdp_tipo_documento.tipo_documento = mdp_personas_documentos.tipo_documento
WHERE mdp_personas.persona NOT IN 
  (SELECT sga_alumnos.persona FROM sga_alumnos
   UNION 
   SELECT sga_docentes.persona FROM sga_docentes)
AND (mdp_personas_perfil_datos.usuario_perfil_datos IS NULL OR desarrollo.apex_usuario_perfil_datos_dims.clave IN ('2') )
AND nro_documento = '25985556'
GROUP BY mdp_personas.persona, mdp_tipo_documento.desc_abreviada, mdp_personas_documentos.nro_documento
ORDER BY apellido, nombres

Despues de esto, proba crear el siguiente indice sobre el campo “documento_principal” de la tabla “mdp_personas”, volve a correr la query anterior y enviá el resultado:

CREATE INDEX idx_mdp_personas_documento_principal ON mdp_personas (documento_principal);

3

9

Paso a responder por puntos

Tabla: mdp_personas
iu_mdp_personas_apellido_nombres

SI ESTABA EL INDICE

Tabla: mdp_personas_documentos
id_tipo_documento_nro_documento
iu_pais_tipo_nro_documento
iu_persona_pais_tipo_documento

SI ESTABAN LOS INDICES

fk en la tabla mdp_personas_documentos:
fk_mdp_personas_documentos_mdp_personas

NO ESTABA PRESENTE, AHORA FUÉ AGREGADO

¿Tienen creada la PK en la tabla mdp_personas_documentos?

SI ESTABA LA PK

Paso el resultado del Explain con las indicaciones previas


"Sort  (cost=122809517.12..122809517.13 rows=2 width=312)"
"  Sort Key: mdp_personas.apellido, mdp_personas.nombres"
"  ->  Unique  (cost=122809517.09..122809517.11 rows=2 width=312)"
"        ->  Sort  (cost=122809517.09..122809517.09 rows=2 width=312)"
"              Sort Key: mdp_personas.persona, mdp_personas.apellido, mdp_personas.nombres, ((((mdp_tipo_documento.desc_abreviada)::text || ' '::text) || (mdp_personas_documentos.nro_documento)::text))"
"              ->  Append  (cost=8.45..122809517.08 rows=2 width=312)"
"                    ->  Nested Loop Left Join  (cost=8.45..2951.17 rows=1 width=59)"
"                          Join Filter: (mdp_tipo_documento.tipo_documento = mdp_personas_documentos.tipo_documento)"
"                          ->  Hash Join  (cost=8.45..2949.91 rows=1 width=39)"
"                                Hash Cond: (mdp_personas.documento_principal = mdp_personas_documentos.documento)"
"                                ->  Seq Scan on mdp_personas  (cost=0.00..2712.23 rows=87323 width=31)"
"                                ->  Hash  (cost=8.44..8.44 rows=1 width=16)"
"                                      ->  Index Scan using id_tipo_documento_nro_documento on mdp_personas_documentos  (cost=0.42..8.44 rows=1 width=16)"
"                                            Index Cond: ((nro_documento)::text = '25985556'::text)"
"                          ->  Seq Scan on mdp_tipo_documento  (cost=0.00..1.11 rows=11 width=22)"
"                    ->  Subquery Scan on ""*SELECT* 2""  (cost=122806565.86..122806565.89 rows=1 width=59)"
"                          ->  Group  (cost=122806565.86..122806565.88 rows=1 width=89)"
"                                Group Key: mdp_personas_1.persona, mdp_tipo_documento_1.desc_abreviada, mdp_personas_documentos_1.nro_documento"
"                                ->  Sort  (cost=122806565.86..122806565.87 rows=1 width=57)"
"                                      Sort Key: mdp_personas_1.persona, mdp_tipo_documento_1.desc_abreviada"
"                                      ->  Hash Join  (cost=4868.28..122806565.85 rows=1 width=57)"
"                                            Hash Cond: (mdp_personas_1.documento_principal = mdp_personas_documentos_1.documento)"
"                                            ->  Merge Left Join  (cost=4858.58..122806544.45 rows=3119 width=31)"
"                                                  Merge Cond: (mdp_personas_1.persona = mdp_personas_perfil_datos.persona)"
"                                                  Filter: ((mdp_personas_perfil_datos.usuario_perfil_datos IS NULL) OR (apex_usuario_perfil_datos_dims.clave = '2'::text))"
"                                                  ->  Index Scan using pk_mdp_personas on mdp_personas mdp_personas_1  (cost=4827.14..122806399.46 rows=43662 width=31)"
"                                                        Filter: (NOT (SubPlan 1))"
"                                                        SubPlan 1"
"                                                          ->  Materialize  (cost=4826.85..7310.95 rows=131340 width=4)"
"                                                                ->  HashAggregate  (cost=4826.85..6140.25 rows=131340 width=4)"
"                                                                      Group Key: sga_alumnos.persona"
"                                                                      ->  Append  (cost=0.00..4498.50 rows=131340 width=4)"
"                                                                            ->  Seq Scan on sga_alumnos  (cost=0.00..2498.36 rows=129536 width=4)"
"                                                                            ->  Seq Scan on sga_docentes  (cost=0.00..30.04 rows=1804 width=4)"
"                                                  ->  Sort  (cost=31.44..32.41 rows=391 width=44)"
"                                                        Sort Key: mdp_personas_perfil_datos.persona"
"                                                        ->  Hash Left Join  (cost=1.32..14.60 rows=391 width=44)"
"                                                              Hash Cond: (mdp_personas_perfil_datos.usuario_perfil_datos = apex_usuario_perfil_datos_dims.usuario_perfil_datos)"
"                                                              ->  Seq Scan on mdp_personas_perfil_datos  (cost=0.00..7.91 rows=391 width=12)"
"                                                              ->  Hash  (cost=1.14..1.14 rows=14 width=40)"
"                                                                    ->  Seq Scan on apex_usuario_perfil_datos_dims  (cost=0.00..1.14 rows=14 width=40)"
"                                            ->  Hash  (cost=9.68..9.68 rows=1 width=34)"
"                                                  ->  Nested Loop Left Join  (cost=0.42..9.68 rows=1 width=34)"
"                                                        Join Filter: (mdp_tipo_documento_1.tipo_documento = mdp_personas_documentos_1.tipo_documento)"
"                                                        ->  Index Scan using id_tipo_documento_nro_documento on mdp_personas_documentos mdp_personas_documentos_1  (cost=0.42..8.44 rows=1 width=16)"
"                                                              Index Cond: ((nro_documento)::text = '25985556'::text)"
"                                                        ->  Seq Scan on mdp_tipo_documento mdp_tipo_documento_1  (cost=0.00..1.11 rows=11 width=22)"

y a continuación agrego el índice:

CREATE INDEX idx_mdp_personas_documento_principal ON mdp_personas (documento_principal);

y paso de nuevo el resultado del Explain


"Sort  (cost=6270.77..6270.77 rows=2 width=312)"
"  Sort Key: mdp_personas.apellido, mdp_personas.nombres"
"  ->  Unique  (cost=6270.73..6270.76 rows=2 width=312)"
"        ->  Sort  (cost=6270.73..6270.74 rows=2 width=312)"
"              Sort Key: mdp_personas.persona, mdp_personas.apellido, mdp_personas.nombres, ((((mdp_tipo_documento.desc_abreviada)::text || ' '::text) || (mdp_personas_documentos.nro_documento)::text))"
"              ->  Append  (cost=0.71..6270.72 rows=2 width=312)"
"                    ->  Nested Loop Left Join  (cost=0.71..18.01 rows=1 width=59)"
"                          Join Filter: (mdp_tipo_documento.tipo_documento = mdp_personas_documentos.tipo_documento)"
"                          ->  Nested Loop  (cost=0.71..16.76 rows=1 width=39)"
"                                ->  Index Scan using id_tipo_documento_nro_documento on mdp_personas_documentos  (cost=0.42..8.44 rows=1 width=16)"
"                                      Index Cond: ((nro_documento)::text = '25985556'::text)"
"                                ->  Index Scan using idx_mdp_personas_documento_principal on mdp_personas  (cost=0.29..8.31 rows=1 width=31)"
"                                      Index Cond: (documento_principal = mdp_personas_documentos.documento)"
"                          ->  Seq Scan on mdp_tipo_documento  (cost=0.00..1.11 rows=11 width=22)"
"                    ->  Subquery Scan on ""*SELECT* 2""  (cost=6252.66..6252.69 rows=1 width=59)"
"                          ->  Group  (cost=6252.66..6252.68 rows=1 width=89)"
"                                Group Key: mdp_personas_1.persona, mdp_tipo_documento_1.desc_abreviada, mdp_personas_documentos_1.nro_documento"
"                                ->  Sort  (cost=6252.66..6252.67 rows=1 width=57)"
"                                      Sort Key: mdp_personas_1.persona, mdp_tipo_documento_1.desc_abreviada"
"                                      ->  Nested Loop Left Join  (cost=4827.90..6252.65 rows=1 width=57)"
"                                            Join Filter: (mdp_tipo_documento_1.tipo_documento = mdp_personas_documentos_1.tipo_documento)"
"                                            ->  Nested Loop Left Join  (cost=4827.90..6251.41 rows=1 width=39)"
"                                                  Filter: ((mdp_personas_perfil_datos.usuario_perfil_datos IS NULL) OR (apex_usuario_perfil_datos_dims.clave = '2'::text))"
"                                                  ->  Nested Loop  (cost=4827.56..6249.84 rows=1 width=39)"
"                                                        ->  Index Scan using id_tipo_documento_nro_documento on mdp_personas_documentos mdp_personas_documentos_1  (cost=0.42..8.44 rows=1 width=16)"
"                                                              Index Cond: ((nro_documento)::text = '25985556'::text)"
"                                                        ->  Index Scan using idx_mdp_personas_documento_principal on mdp_personas mdp_personas_1  (cost=4827.14..6241.39 rows=1 width=31)"
"                                                              Index Cond: (documento_principal = mdp_personas_documentos_1.documento)"
"                                                              Filter: (NOT (SubPlan 1))"
"                                                              SubPlan 1"
"                                                                ->  Materialize  (cost=4826.85..7310.95 rows=131340 width=4)"
"                                                                      ->  HashAggregate  (cost=4826.85..6140.25 rows=131340 width=4)"
"                                                                            Group Key: sga_alumnos.persona"
"                                                                            ->  Append  (cost=0.00..4498.50 rows=131340 width=4)"
"                                                                                  ->  Seq Scan on sga_alumnos  (cost=0.00..2498.36 rows=129536 width=4)"
"                                                                                  ->  Seq Scan on sga_docentes  (cost=0.00..30.04 rows=1804 width=4)"
"                                                  ->  Hash Right Join  (cost=0.33..1.55 rows=2 width=44)"
"                                                        Hash Cond: (apex_usuario_perfil_datos_dims.usuario_perfil_datos = mdp_personas_perfil_datos.usuario_perfil_datos)"
"                                                        ->  Seq Scan on apex_usuario_perfil_datos_dims  (cost=0.00..1.14 rows=14 width=40)"
"                                                        ->  Hash  (cost=0.31..0.31 rows=2 width=12)"
"                                                              ->  Index Scan using ifk_mdp_personas_perfil_datos_mdp_personas_grupo_acc on mdp_personas_perfil_datos  (cost=0.27..0.31 rows=2 width=12)"
"                                                                    Index Cond: (persona = mdp_personas_1.persona)"
"                                            ->  Seq Scan on mdp_tipo_documento mdp_tipo_documento_1  (cost=0.00..1.11 rows=11 width=22)"

Estas modificaciones mejoran considerablemente la consulta! lo probé en Gestión y ahora responde en segundos. Todo esto lo probé en un entorno de Desarrollo, lo paso sin problemas a Producción?

Consulta: este último índice que se agrega: idx_mdp_personas_documento_principa es propio de la versión 3.18? Cuál puede ser el motivo por el cuál no lo teníamos en la BD?

Saludos!

10

Crealo en producción.
Ese índice no lo tenemos en la base, lo agregaremos en la version 3.19. No tendrán problemas cuando migren a 3.19 porque esta contemplado que pueda existir ese índice.
Lo raro es que en una base con 700000 alumnos y sin ese índice la respuesta era de uno 3 segundos buscando por un nro de documento (aunque el tiempo máximo era de acceder al servidor y devolver el dato). Luego creé ese índice y el tiempo bajó a 1 segundo.
En la mayoría de las consultas que están las tablas mdp_personas y mdp_personas_documentos se hace el join por persona y documento principal, por eso el acceso es rapido porque accede por el indice sobre el campo “persona”, pero en este caso el join solo esta por ese campo “documento_principal” y de ahi es que hacia un scan secuencial en la tabla mdp_personas. Ahora con este indice hace una búsqueda en este nuevo índice:
“Index Scan using idx_mdp_personas_documento_principal on mdp_personas mdp_personas_1”

11

Con ese índice creado, proba esta query, cambie la subquery que tiene un IN por un NOT EXISTS, porque creo esa subquery es la que tarda mucho tiempo por hacer un scan secuencial en la tabla de docentes y alumnos:

SELECT vw_perfil_de_datos_personas.persona,
vw_perfil_de_datos_personas.apellido,
vw_perfil_de_datos_personas.nombres,
vw_perfil_de_datos_personas.tipo_nro_documento as documento
FROM vw_perfil_de_datos_personas
WHERE true
AND f_limpiar_acentos(nro_documento::varchar) ILIKE '%25985556%'
UNION
SELECT mdp_personas.persona,
mdp_personas.apellido,
mdp_personas.nombres, mdp_tipo_documento.desc_abreviada || ' ' || mdp_personas_documentos.nro_documento as documento
FROM mdp_personas
LEFT JOIN mdp_personas_perfil_datos ON mdp_personas_perfil_datos.persona = mdp_personas.persona
LEFT JOIN desarrollo.apex_usuario_perfil_datos_dims ON mdp_personas_perfil_datos.usuario_perfil_datos = desarrollo.apex_usuario_perfil_datos_dims.usuario_perfil_datos
LEFT JOIN mdp_personas_documentos ON mdp_personas_documentos.documento = mdp_personas.documento_principal
LEFT JOIN mdp_tipo_documento ON mdp_tipo_documento.tipo_documento = mdp_personas_documentos.tipo_documento
WHERE NOT EXISTS   (SELECT 1 FROM sga_alumnos WHERE persona = mdp_personas.persona     UNION  SELECT 1 FROM sga_docentes WHERE persona = mdp_personas.persona)
AND (mdp_personas_perfil_datos.usuario_perfil_datos IS NULL OR desarrollo.apex_usuario_perfil_datos_dims.clave IN ('2') )
AND f_limpiar_acentos(nro_documento::varchar) ILIKE '%25985556%'
GROUP BY mdp_personas.persona, mdp_tipo_documento.desc_abreviada, mdp_personas_documentos.nro_documento
ORDER BY apellido, nombres
12

Listo! hice el cambio en producción y funciona OK.

Probé esta última consulta con el NOT EXISTS y la resuelve en 1 segundo.

Muchas Gracias Ale!