Re: RV: bad result in a query!! :-(

On Tue, 15 Oct 2002, Jose Antonio Leo wrote:

> 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)
                         ^^^^^^^^^^^^^^^^^^^^^^^
What is this all about, the seqscan only returns 75918 rows?

>                           ->  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 ?

However, aside from that odd looking Sort line a fair portion of the time is
taken in the Merge Join. You could try the same query after doing a:

SET ENABLE_MERGEJOIN = OFF

which might force the planner to chose an alternative, possibly faster method.


--
Nigel J. Andrews