Slow query in 8.2.0

From: Manuel Sugawara <masm(at)fciencias(dot)unam(dot)mx>
To: pgsql general <pgsql-general(at)postgresql(dot)org>
Subject: Slow query in 8.2.0
Date: 2006-12-13 23:36:04
Message-ID: m31wn3id23.fsf@conexa.fciencias.unam.mx
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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=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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2006-12-13 23:52:33 Re: Slow query in 8.2.0
Previous Message Tom Lane 2006-12-13 23:07:15 Re: creating an index with tablespace on a dedicated filesystem (postgresql 8.1.5)