Hi, I execute a complex query I get very slow response: Total runtime: 565528.70 msec
The query is:
explain analyze SELECT vtdiaaec.cod_ae1, aecoc.des_ae, Sum(vtdiaaec.ven_uni) AS
Sum(vtdiaaec.ven_pco) AS SumaDeven_pco, Sum(vtdiaaec.ven_siv) AS SumaDeven_siv,
Sum(vtdiaaec.ven_civ) AS SumaDeven_civ, Sum(vtdiaaec.ven_ofe) AS SumaDeven_ofe,
Sum(vtdiaaec.cos_ofe) AS SumaDecos_ofe
FROM vtdiaaec LEFT JOIN aecoc ON vtdiaaec.cod_ae1 = aecoc.cod_ae1
WHERE (((aecoc.cod_ae2)=0) AND ((aecoc.cod_ae3)=0) AND
((aecoc.cod_ae4)=0) AND ((aecoc.cod_ae5)=0) AND
((extract (year from vtdiaaec.fecha))='2002'))
GROUP BY vtdiaaec.cod_ae1, aecoc.des_ae
ORDER BY vtdiaaec.cod_ae1;
The table aecoc has a primary key (cod_ae1,cod_ae2,cod_ae3) and the table vtdiaaec has a key with the fields cod_ae1,cod_ae2,cod_ae3.
And the Explain:
EXPLAIN
NOTICE: QUERY PLAN:
Aggregate (cost=12136.91..12166.61 rows=149 width=182) (actual time=563794.40..565484.82 rows=8 loops=1)
-> Group (cost=12136.91..12144.33 rows=1485 width=182) (actual time=563790.78..564804.35 rows=75918 loops=1)
-> Sort (cost=12136.91..12136.91 rows=1485 width=182) (actual time=563790.76..563912.66 rows=75918 loops=1)
-> Merge Join (cost=10821.77..12058.67 rows=1485 width=182) (actual time=16453.89..557749.04 rows=75918 loops=1)
-> Index Scan using aecoc_key on aecoc (cost=0.00..379.17rows=5036 width=64) (actual time=0.18..83.90 rows=5036 loops=1)
-> Sort (cost=10821.77..10821.77 rows=1485 width=118) (actual time=16453.64..199329.55 rows=49801240 loops=1)
-> Seq Scan on vtdiaaec (cost=0.00..10743.52 rows=1485 width=118) (actual time=213.71..11992.74 rows=75918 loops=1)
Total runtime: 565528.70 msec
How i can interpret this bad results ?
tk
