Postgres Pro
Downloads
Home   >   mailing lists

Thread: Slow query in 8.2.0

Slow query in 8.2.0

From
Manuel Sugawara
Date:
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.

Re: Slow query in 8.2.0

From
Tom Lane
Date:
Manuel Sugawara <masm@fciencias.unam.mx> writes:
> 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:

You've omitted all the interesting details --- obviously
registro_trayectoria is a view, but of what?  Please show the table and
view definitions involved.

8.2 is a good bit more conservative than previous releases about
optimizing views involving volatile functions, so I'm wondering whether
that could have anything to do with it ...

            regards, tom lane

Re: Slow query in 8.2.0

From
Manuel Sugawara
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:

> 8.2 is a good bit more conservative than previous releases about
> optimizing views involving volatile functions, so I'm wondering whether
> that could have anything to do with it ...

Oh well, you are indeed right, I went through the whole view hierarchy
and replaced all volatile function calls (I used to use a trick
defining a function to avoid a join that used to perform better in
earlier versions) with explicit JOINs, also I relabeled some functions
that were indeed STABLE and now everything is working fine. I was
amazed that some of those views predates Postgres support of JOINs, I
guees we have been using Postgres for a very long time :-).

Just out of curiosity, why the change?

Regards,
Manuel.

Re: Slow query in 8.2.0

From
Tom Lane
Date:
Manuel Sugawara <masm@fciencias.unam.mx> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> 8.2 is a good bit more conservative than previous releases about
>> optimizing views involving volatile functions, so I'm wondering whether
>> that could have anything to do with it ...

> Just out of curiosity, why the change?

People complained about getting wrong/surprising answers due to
unexpected multiple evaluations of volatile functions.

            regards, tom lane