Thread: Problem with a view (not lazy ;)
Hi all.
In my tests with views I have found a problem that can't explain.
Look at this view:
--------------------------------------
create view v_ingresos_técnico as
select e.nombre as empleado, p.importe_total, p.iva, p.num_factura,
p.fecha_factura, p.cobrado, a.urgente, ta.fecha
from técnico_aviso ta
join empleado e on ta.empleado = e.cod
join presupuesto p on ta.aviso = p.aviso
join aviso a on ta.aviso = a.número;
--------------------------------------
When I query:
SELECT empleado, sum(importe_total) from v_ingresos_técnico
where fecha between '1/1/2002' and '31/1/2002' group by
empleado;
It executes inmediately. Here is the explain:
--------------------------------------
Aggregate (cost=2930.48..2930.48 rows=1 width=40)
-> Group (cost=2930.48..2930.48 rows=1 width=40)
-> Sort (cost=2930.48..2930.48 rows=1 width=40)
-> Nested Loop (cost=3.71..2930.47 rows=1 width=40)
-> Nested Loop (cost=3.71..2506.35 rows=177 width=36)
-> Hash Join (cost=3.71..1449.89 rows=510 width=24)
-> Seq Scan on técnico_aviso ta (cost=0.00..1424.06 rows=510 width=8)
-> Hash (cost=3.37..3.37 rows=137 width=16)
-> Seq Scan on empleado e (cost=0.00..3.37 rows=137 width=16)
-> Index Scan using pre_aviso_ndx on presupuesto p (cost=0.00..2.06 rows=1 width=12)
-> Index Scan using aviso_pkey on aviso a (cost=0.00..2.39 rows=1 width=4)
--------------------------------------
Now look at this view. Differences with previous are marked with "->":
--------------------------------------
create view v_ingresos_técnico as
select e.nombre as empleado, p.importe_total, p.iva, p.num_factura,
p.fecha_factura, p.cobrado, a.urgente, ta.fecha,
-> em.descripcion as empresa
from técnico_aviso ta
join empleado e on ta.empleado = e.cod
join presupuesto p on ta.aviso = p.aviso
join aviso a
-> left join empresa em on a.empresa = em.cod
on ta.aviso = a.número;
--------------------------------------
Executing same query it lags for more than a minute. Here is explain:
--------------------------------------
Aggregate (cost=5679.37..5679.38 rows=1 width=46)
-> Group (cost=5679.37..5679.37 rows=1 width=46)
-> Sort (cost=5679.37..5679.37 rows=1 width=46)
-> Nested Loop (cost=1.34..5679.36 rows=1 width=46)
-> Nested Loop (cost=0.00..6.28 rows=1 width=36)
-> Nested Loop (cost=0.00..4.21 rows=1 width=24)
-> Index Scan using tec_avi_fecha_ndx on técnico_aviso ta (cost=0.00..2.18 rows=1
width=8)
-> Index Scan using empleado_pkey on empleado e (cost=0.00..2.01 rows=1 width=16)
-> Index Scan using pre_aviso_ndx on presupuesto p (cost=0.00..2.06 rows=1 width=12)
-> Materialize (cost=4974.88..4974.88 rows=46547 width=10)
-> Merge Join (cost=1.34..4974.88 rows=46547 width=10)
-> Index Scan using avi_empresa_ndx on aviso a (cost=0.00..4391.56 rows=46547
width=6)
-> Sort (cost=1.34..1.34 rows=12 width=4)
-> Seq Scan on empresa em (cost=0.00..1.12 rows=12 width=4)
---------------------------------------
I am curious about "Materialize" but don't know what it means.
Table "empresa" has 12 rows and there are indexes for all fields that
participate in a join.
Please, any help with this problem?.
Thanks in advance.
David
bombadil@wanadoo.es writes:
> In my tests with views I have found a problem that can't explain.
Are those EXPLAINs really for equivalent queries? I find it very
suspicious that the estimated number of rows retrieved from "ta" is
so different in the two cases. I wonder whether you used the same
range of "fecha" in both cases.
> from t�cnico_aviso ta
> join empleado e on ta.empleado = e.cod
> join presupuesto p on ta.aviso = p.aviso
> join aviso a
> -> left join empresa em on a.empresa = em.cod
> on ta.aviso = a.n�mero;
Did you really mean to do it that way, and not as
from t�cnico_aviso ta
join empleado e on ta.empleado = e.cod
join presupuesto p on ta.aviso = p.aviso
join aviso a on ta.aviso = a.n�mero
left join empresa em on a.empresa = em.cod;
The way you wrote it forces the a-to-em left join to be done first,
which means it will generate lots of join rows that probably won't
be of any use. I suspect you want to do the left join last not first.
See
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html
regards, tom lane
El miércoles 06 de febrero, Tom Lane escribió:
> bombadil@wanadoo.es writes:
> > In my tests with views I have found a problem that can't explain.
>
> Are those EXPLAINs really for equivalent queries? I find it very
> suspicious that the estimated number of rows retrieved from "ta" is
> so different in the two cases. I wonder whether you used the same
> range of "fecha" in both cases.
I have used same query. That is sure.
> > from técnico_aviso ta
> > join empleado e on ta.empleado = e.cod
> > join presupuesto p on ta.aviso = p.aviso
> > join aviso a
> > -> left join empresa em on a.empresa = em.cod
> > on ta.aviso = a.número;
>
> Did you really mean to do it that way, and not as
>
> from técnico_aviso ta
> join empleado e on ta.empleado = e.cod
> join presupuesto p on ta.aviso = p.aviso
> join aviso a on ta.aviso = a.número
> left join empresa em on a.empresa = em.cod;
>
> The way you wrote it forces the a-to-em left join to be done first,
> which means it will generate lots of join rows that probably won't
> be of any use. I suspect you want to do the left join last not first.
Wow!
Changing what you propose enhances execute time greatly. Thanks for
your idea. I need to experiment more with it.
Greets.
David