Slow query in 8.2.0

Поиск
Список
Период
Сортировка
От Manuel Sugawara
Тема Slow query in 8.2.0
Дата
Msg-id m31wn3id23.fsf@conexa.fciencias.unam.mx
обсуждение исходный текст
Ответы Re: Slow query in 8.2.0  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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=42236loops=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=48876loops=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)
(actualtime=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.033rows=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.252rows=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.227rows=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.223rows=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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: Marking indexes out of date (WAS: loading data,
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Slow query in 8.2.0