Hi all,
I have a query that use to work fine in 3 or 4 releases before 8.2 but
now it's very slow, same database same data set, explain analyze
follows:
8.2.0
ciencias=# explain analyze SELECT registro_id FROM vregacd.registro_trayectoria WHERE cuenta = '086521071' AND grupo_id = '160940';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=4033.17..9302.65 rows=1 width=4) (actual time=11293.047..24123.221 rows=1 loops=1)
-> Hash Join (cost=4033.17..9300.99 rows=1 width=4) (actual time=11293.002..24123.169 rows=1 loops=1)
Hash Cond: (t.alumno_id = a.id)
-> Seq Scan on trayectoria t (cost=0.00..4635.98 rows=42122 width=66) (actual time=21.003..14186.758 rows=42236 loops=1)
Filter: (causa_baja_id IS NULL)
-> Hash (cost=4033.16..4033.16 rows=1 width=4) (actual time=9874.651..9874.651 rows=1 loops=1)
-> Hash Join (cost=8.02..4033.16 rows=1 width=4) (actual time=5198.908..9874.643 rows=1 loops=1)
Hash Cond: (p.persona_id = a.persona_id)
-> Seq Scan on persona p (cost=0.00..3291.99 rows=48876 width=85) (actual time=34.254..9786.017 rows=48876 loops=1)
-> Hash (cost=8.02..8.02 rows=1 width=8) (actual time=0.054..0.054 rows=1 loops=1)
-> Index Scan using "AlumnoNúmeroCuentaÚnico" on alumno a (cost=0.00..8.02 rows=1 width=8) (actual time=0.039..0.044 rows=1 loops=1)
Index Cond: (cuenta = 86521071)
-> Index Scan using "RegistroAlumnoGrupoÚnico" on registro r (cost=0.00..1.65 rows=1 width=8) (actual time=0.031..0.033 rows=1 loops=1)
Index Cond: ((t.trayectoria_id = r.trayectoria_id) AND (r.grupo_id = 160940))
Total runtime: 24123.953 ms
(15 filas)
8.1.4
ciencias=# explain analyze SELECT registro_id FROM vregacd.registro_trayectoria WHERE cuenta = '086521071' AND grupo_id = '160940';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..21.25 rows=1 width=4) (actual time=0.747..0.747 rows=0 loops=1)
-> Nested Loop (cost=0.00..17.14 rows=1 width=8) (actual time=0.737..0.737 rows=0 loops=1)
-> Nested Loop (cost=0.00..11.20 rows=1 width=8) (actual time=0.487..0.502 rows=1 loops=1)
-> Index Scan using "AlumnoNúmeroCuentaÚnico" on alumno a (cost=0.00..5.47 rows=1 width=8) (actual time=0.248..0.252 rows=1 loops=1)
Index Cond: (cuenta = 86521071)
-> Index Scan using alumno_try_alumno on trayectoria t (cost=0.00..5.72 rows=1 width=8) (actual time=0.220..0.227 rows=1 loops=1)
Index Cond: ("outer".id = t.alumno_id)
Filter: (causa_baja_id IS NULL)
-> Index Scan using "RegistroAlumnoGrupoÚnico" on registro r (cost=0.00..5.93 rows=1 width=8) (actual time=0.223..0.223 rows=0 loops=1)
Index Cond: (("outer".id = r.trayectoria_id) AND (r.grupo_id = 160940))
-> Index Scan using persona_pkey on persona p (cost=0.00..4.10 rows=1 width=4) (never executed)
Index Cond: ("outer".persona_id = p.id)
Total runtime: 1.465 ms
(13 rows)
Any ideas?
Regards,
Manuel.
