Thread: 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

On Tuesday 15 Oct 2002 12:12 pm, Nigel J. Andrews wrote:
> 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

That's 9 minutes - not very good at all.

> > The query is:
[snip]
> > 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'))

This extract will force a seq-scan. You might find it better to check for
dates: 2002-01-01 to 2002-12-31 which could use an index on the field.

Failing that you could write a function year_part(timestamptz) which returned
the relevant date_part() and create a functional index.

> > And the Explain:

> >               ->  Merge Join  (cost=10821.77..12058.67 rows=1485
> > width=182) (actual time=16453.89..557749.04 rows=75918 loops=1)

Long start-up time on this (if I'm reading this right).

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

Yep - very strange. I'm not sure where the 4 million comes from - I can't see
any relationship with the 75918.

Nigel's advice about ENABLE_MERGEJOIN should help, but there's something odd
here. Try a VACUUM ANALYSE VERBOSE on the two tables and see if it says
anything odd perhaps.

--
  Richard Huxton

Richard Huxton <dev@archonet.com> writes:
> On Tuesday 15 Oct 2002 12:12 pm, Nigel J. Andrews wrote:
>> On Tue, 15 Oct 2002, Jose Antonio Leo wrote:
>>> ->  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?

> Yep - very strange. I'm not sure where the 4 million comes from - I can't see
> any relationship with the 75918.

I think what is happening is that there are many equal keys in the
relations being joined.  If you think about how a mergejoin works,
it has to back up and rescan a segment of the inner relation each
time it advances to a new outer tuple that has a key matching the
prior key.  I believe that the EXPLAIN ANALYZE machinery counts each
row fetched from the inner relation afresh, even if it's a re-fetch
of a row already fetched.

There is not currently any code in the planner to try to account
for this effect; if there were, it might choose a different plan.
(Not that I'm sure a hash join would be much better.)

Jose, how many distinct cod_ae1 values have you actually got in
each table?  Can you use additional join conditions (perhaps
cod_ae2, cod_ae3) to improve the specificity of the match between
the tables?

            regards, tom lane

hi again!
Firts thank for your responses.
I have done vacuum on both tables, I have changed the comparation of the
date for this (vtdiaaec.fecha>='2002/1/1' and vtdiaaec.fecha<='2002/12/31' )
and i try the select with the same results.
NOTICE:  QUERY PLAN:

Aggregate  (cost=1171405.05..1172997.68 rows=7963 width=145) (actual
time=206274.38..207963.43 rows=8 loops=1)
 ->  Group  (cost=1171405.05..1171803.20 rows=79631 width=145) (actual
time=206270.76..207284.63 rows=75918 loops=1)
  ->  Sort  (cost=1171405.05..1171405.05 rows=79631 width=145) (actual
time=206270.74..206392.71 rows=75918 loops=1)
    ->  Merge Join  (cost=24535.21..1152202.63 rows=79631 width=145) (actual
time=2422.72..203573.32 rows=75918 loops=1)
     ->  Index Scan using aecoc_key on aecoc  (cost=0.00..379.21rows=5037
width=64) (actual time=0.21..84.13 rows=5037 loops=1)
      ->  Sort  (cost=24535.21..24535.21 rows=79631 width=81) (actual
time=2422.45..70921.59 rows=49840029 loops=1)
        ->  Seq Scan on vtdiaaec  (cost=0.00..11552.80 rows=79631 width=81)
(actual time=0.08..910.94 rows=75918 loops=1)
Total runtime: 208014.31 msec

The table aecoc have 5 primary key cod_ae1, cod_ae2,cod_ae3, cod_ae4,
cod_ae5. and the 6th field is the descripton.
Is the codification of all the products for sale.
1,0,0,0,0, FOOD AND DRINKS
1,1,0,0,0, DRY FOOD
1,1,1,0,0, RICE
1,1,1,0,0, COOKIES
1,2,0,0,0, CONSERVES
... etc
2,0,0,0,0, FRESH FOOD
2,1,0,0,0, MEAT
2,1,1,0,0, BIRDS AND HUNT
etc..
There is 5689 tuples.

And the vtddiaaec is the table of sales of 5 shop. More o less it:
cod_ae1, cod_ae2,cod_ae3, sales_without_tax, date, cod_shop....
and the tuples
1,1,1,23000,10/10/2002,1234 etc...
every day there is sales of different codes aecoc and shops.
There is 256320 tuples in this table.

And the select it tries know the sales with aecoc codes in the level 1.,
with the cod_ae1 <>0 and the cod_ae2=0 and cod_ae3=0. The join is for
extract the description.

After this explication, i hope your help.  (I'm very busy because of them,
my boss is back always, :-p)

Sorry for my english and tk.



-----Mensaje original-----
De: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]En nombre de Tom Lane
Enviado el: martes, 15 de octubre de 2002 16:41
Para: Richard Huxton
CC: pgsql-general
Asunto: Re: [GENERAL] RV: bad result in a query!! :-(


Richard Huxton <dev@archonet.com> writes:
> On Tuesday 15 Oct 2002 12:12 pm, Nigel J. Andrews wrote:
>> On Tue, 15 Oct 2002, Jose Antonio Leo wrote:
>>> ->  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?

> Yep - very strange. I'm not sure where the 4 million comes from - I can't
see
> any relationship with the 75918.

I think what is happening is that there are many equal keys in the
relations being joined.  If you think about how a mergejoin works,
it has to back up and rescan a segment of the inner relation each
time it advances to a new outer tuple that has a key matching the
prior key.  I believe that the EXPLAIN ANALYZE machinery counts each
row fetched from the inner relation afresh, even if it's a re-fetch
of a row already fetched.

There is not currently any code in the planner to try to account
for this effect; if there were, it might choose a different plan.
(Not that I'm sure a hash join would be much better.)

Jose, how many distinct cod_ae1 values have you actually got in
each table?  Can you use additional join conditions (perhaps
cod_ae2, cod_ae3) to improve the specificity of the match between
the tables?

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Tuesday 15 Oct 2002 4:48 pm, Jose Antonio Leo wrote:
> hi again!
> Firts thank for your responses.
> I have done vacuum on both tables, I have changed the comparation of the
> date for this (vtdiaaec.fecha>='2002/1/1' and vtdiaaec.fecha<='2002/12/31'
> ) and i try the select with the same results.

Not hopeless yet!

> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=1171405.05..1172997.68 rows=7963 width=145) (actual
> time=206274.38..207963.43 rows=8 loops=1)
>  ->  Group  (cost=1171405.05..1171803.20 rows=79631 width=145) (actual
> time=206270.76..207284.63 rows=75918 loops=1)
>   ->  Sort  (cost=1171405.05..1171405.05 rows=79631 width=145) (actual
> time=206270.74..206392.71 rows=75918 loops=1)
>     ->  Merge Join  (cost=24535.21..1152202.63 rows=79631 width=145)
> (actual time=2422.72..203573.32 rows=75918 loops=1)
>      ->  Index Scan using aecoc_key on aecoc  (cost=0.00..379.21rows=5037
> width=64) (actual time=0.21..84.13 rows=5037 loops=1)
>       ->  Sort  (cost=24535.21..24535.21 rows=79631 width=81) (actual
> time=2422.45..70921.59 rows=49840029 loops=1)
>         ->  Seq Scan on vtdiaaec  (cost=0.00..11552.80 rows=79631 width=81)
> (actual time=0.08..910.94 rows=75918 loops=1)
> Total runtime: 208014.31 msec

OK, below you say you have 256,320 tuples in vtddiaaec so Seq Scan is correct
- if you're going to need 75918 tuples then an index won't help.

> The table aecoc have 5 primary key cod_ae1, cod_ae2,cod_ae3, cod_ae4,
> cod_ae5. and the 6th field is the descripton.
> Is the codification of all the products for sale.
> 1,0,0,0,0, FOOD AND DRINKS
> 1,1,0,0,0, DRY FOOD
> 1,1,1,0,0, RICE
> 1,1,1,0,0, COOKIES
> 1,2,0,0,0, CONSERVES
> ... etc
> 2,0,0,0,0, FRESH FOOD
> 2,1,0,0,0, MEAT
> 2,1,1,0,0, BIRDS AND HUNT
> etc..
> There is 5689 tuples.

So for the year 2002 you're trying to get:

1, FOOD AND DRINK, (totals...)
2, FRESH FOOD, (totals)

Without the description, is the totalling fast? Without the totals is
selecting the descriptions fast?

If so (and they should be), try creating two views - one with the totals, one
with descriptions and create a select to join them - does that do the trick?
From Tom's answer, your problem seems to be that the join is happening
earlier than you want. You should be able to rewrite the query in one go, but
views might make it easier.

HTH
--
  Richard Huxton

-----Mensaje original-----
De: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]En nombre de Richard Huxton
Enviado el: martes, 15 de octubre de 2002 18:21
Para: Jose Antonio Leo; pgsql-general
Asunto: Re: [GENERAL] RV: bad result in a query!! hopeless

On Tuesday 15 Oct 2002 4:48 pm, Jose Antonio Leo wrote:
> hi again!
> Firts thank for your responses.
> I have done vacuum on both tables, I have changed the comparation of the
> date for this (vtdiaaec.fecha>='2002/1/1' and vtdiaaec.fecha<='2002/12/31'
> ) and i try the select with the same results.

Not hopeless yet!

> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=1171405.05..1172997.68 rows=7963 width=145) (actual
> time=206274.38..207963.43 rows=8 loops=1)
>  ->  Group  (cost=1171405.05..1171803.20 rows=79631 width=145) (actual
> time=206270.76..207284.63 rows=75918 loops=1)
>   ->  Sort  (cost=1171405.05..1171405.05 rows=79631 width=145) (actual
> time=206270.74..206392.71 rows=75918 loops=1)
>     ->  Merge Join  (cost=24535.21..1152202.63 rows=79631 width=145)
> (actual time=2422.72..203573.32 rows=75918 loops=1)
>      ->  Index Scan using aecoc_key on aecoc  (cost=0.00..379.21rows=5037
> width=64) (actual time=0.21..84.13 rows=5037 loops=1)
>       ->  Sort  (cost=24535.21..24535.21 rows=79631 width=81) (actual
> time=2422.45..70921.59 rows=49840029 loops=1)
>         ->  Seq Scan on vtdiaaec  (cost=0.00..11552.80 rows=79631
width=81)
> (actual time=0.08..910.94 rows=75918 loops=1)
> Total runtime: 208014.31 msec

>OK, below you say you have 256,320 tuples in vtddiaaec so Seq Scan is
correct
>- if you're going to need 75918 tuples then an index won't help.
why an index for the date won't help?


> The table aecoc have 5 primary key cod_ae1, cod_ae2,cod_ae3, cod_ae4,
> cod_ae5. and the 6th field is the descripton.
> Is the codification of all the products for sale.
> 1,0,0,0,0, FOOD AND DRINKS
> 1,1,0,0,0, DRY FOOD
> 1,1,1,0,0, RICE
> 1,1,1,0,0, COOKIES
> 1,2,0,0,0, CONSERVES
> ... etc
> 2,0,0,0,0, FRESH FOOD
> 2,1,0,0,0, MEAT
> 2,1,1,0,0, BIRDS AND HUNT
> etc..
> There is 5689 tuples.

>So for the year 2002 you're trying to get:

>1, FOOD AND DRINK, (totals...)
>2, FRESH FOOD, (totals)

>Without the description, is the totalling fast?

Is slow too, but they aren't 208014.31 msec

NOTICE:  QUERY PLAN:
Aggregate  (cost=24535.21..25928.76 rows=7963 width=81) (actual
time=2935.31..4338.35 rows=8 loops=1)
  ->  Group  (cost=24535.21..24734.29 rows=79631 width=81) (actual
time=2932.67..3491.43 rows=75918 loops=1)
        ->  Sort  (cost=24535.21..24535.21 rows=79631 width=81) (actual
time=2932.61..3066.87 rows=75918 loops=1)
              ->  Seq Scan on vtdiaaec  (cost=0.00..11552.80 rows=79631
width=81) (actual time=0.08..1092.91 rows=75918 loops=1)
Total runtime: 6744.68 msec

>Without the totals is selecting the descriptions fast?
NOTICE:  QUERY PLAN:
Group  (cost=1158662.26..1159060.41 rows=7963 width=74) (actual
time=195675.32..196350.27 rows=8 loops=1)
->  Sort  (cost=1158662.26..1158662.26 rows=79631 width=74) (actual
time=195674.05..195765.84 rows=75918 loops=1)
->  Merge Join  (cost=18778.09..1146445.51 rows=79631 width=74)
(actualtime=2024.63..192780.96 rows=75918 loops=1)
->  Index Scan using aecoc_key on aecoc  (cost=0.00..379.21 rows=5037
width=64) (actual time=2.49..87.02 rows=5037 loops=1)
 ->  Sort  (cost=18778.09..18778.09 rows=79631 width=10) (actual
time=2022.09..58846.08 rows=49840029 loops=1)
  ->  Seq Scan on vtdiaaec  (cost=0.00..11552.80 rows=79631 width=10)
(actual time=0.06..703.15 rows=75918 loops=1)
Total runtime: 196396.89 msec

This clear one that join between tables is the problem.


>If so (and they should be), try creating two views - one with the totals,
one
>with descriptions and create a select to join them - does that do the
trick?
From Tom's answer, your problem seems to be that the join is happening
>earlier than you want. You should be able to rewrite the query in one go,
but
>views might make it easier.

I try creating a view for extract the description of the table aecoc (9
tuples level cod_ae1) and execute the query:
NOTICE:  QUERY PLAN:
Aggregate  (cost=42562.44..44155.07 rows=7963 width=130) (actual
time=5879.08..7567.22 rows=8 loops=1)
->  Group  (cost=42562.44..42960.59 rows=79631 width=130) (actual
time=5875.52..6887.99 rows=75918 loops=1)
 ->  Sort  (cost=42562.44..42562.44 rows=79631 width=130) (actual
time=5875.50..5997.02 rows=75918 loops=1)
  ->  Merge Join  (cost=24705.96..24910.02 rows=79631 width=130) (actual
time=2430.91..3187.71 rows=75918 loops=1)
  ->  Sort  (cost=24535.21..24535.21 rows=79631 width=81) (actual
time=2420.18..2549.51 rows=75918 loops=1)
   ->  Seq Scan on vtdiaaec  (cost=0.00..11552.80 rows=79631 width=81)
(actual time=0.08..905.16 rows=75918 loops=1)
    ->  Sort  (cost=170.75..170.75 rows=1 width=24) (actual
time=10.70..54.79 rows=74765 loops=1)
    ->  Subquery Scan v_aecoc_des_aec1  (cost=0.00..170.74 rows=1 width=24)
(actual time=0.10..10.47 rows=11 loops=1)
     ->  Seq Scan on aecoc  (cost=0.00..170.74 rows=1 width=24) (actual
time=0.09..10.42 rows=11 loops=1)
Total runtime: 7688.63 msec

Acceptable?
Elsewhere I think what should be to in one instruction.

tk very much
Jose Antonio Leo

On Wed, 2002-10-16 at 10:52, Jose Antonio Leo wrote:
> >OK, below you say you have 256,320 tuples in vtddiaaec so Seq Scan is
> correct
> >- if you're going to need 75918 tuples then an index won't help.
> why an index for the date won't help?

Because it will have to check the index 75,000 times and then fetch that
many records from the table. That means it's probably going to read all
the disk-blocks anyway, so the index is just complicating things.

> >Without the description, is the totalling fast?
>
> Is slow too, but they aren't 208014.31 msec
>
> NOTICE:  QUERY PLAN:
> Aggregate  (cost=24535.21..25928.76 rows=7963 width=81) (actual
> time=2935.31..4338.35 rows=8 loops=1)
>   ->  Group  (cost=24535.21..24734.29 rows=79631 width=81) (actual
> time=2932.67..3491.43 rows=75918 loops=1)
>         ->  Sort  (cost=24535.21..24535.21 rows=79631 width=81) (actual
> time=2932.61..3066.87 rows=75918 loops=1)
>               ->  Seq Scan on vtdiaaec  (cost=0.00..11552.80 rows=79631
> width=81) (actual time=0.08..1092.91 rows=75918 loops=1)
> Total runtime: 6744.68 msec

Increasing your sort memory might help here - check your postgresql.conf
file. Increase in small steps.

> I try creating a view for extract the description of the table aecoc (9
> tuples level cod_ae1) and execute the query:
> NOTICE:  QUERY PLAN:
> Aggregate  (cost=42562.44..44155.07 rows=7963 width=130) (actual
> time=5879.08..7567.22 rows=8 loops=1)
> ->  Group  (cost=42562.44..42960.59 rows=79631 width=130) (actual
> time=5875.52..6887.99 rows=75918 loops=1)
>  ->  Sort  (cost=42562.44..42562.44 rows=79631 width=130) (actual
> time=5875.50..5997.02 rows=75918 loops=1)
>   ->  Merge Join  (cost=24705.96..24910.02 rows=79631 width=130) (actual
> time=2430.91..3187.71 rows=75918 loops=1)
>   ->  Sort  (cost=24535.21..24535.21 rows=79631 width=81) (actual
> time=2420.18..2549.51 rows=75918 loops=1)
>    ->  Seq Scan on vtdiaaec  (cost=0.00..11552.80 rows=79631 width=81)
> (actual time=0.08..905.16 rows=75918 loops=1)
>     ->  Sort  (cost=170.75..170.75 rows=1 width=24) (actual
> time=10.70..54.79 rows=74765 loops=1)
>     ->  Subquery Scan v_aecoc_des_aec1  (cost=0.00..170.74 rows=1 width=24)
> (actual time=0.10..10.47 rows=11 loops=1)
>      ->  Seq Scan on aecoc  (cost=0.00..170.74 rows=1 width=24) (actual
> time=0.09..10.42 rows=11 loops=1)
> Total runtime: 7688.63 msec

You might find a partial index helps a little on the descriptions, but
it won,t do much. See the docs on CREATE INDEX .... WHERE

HTH

- Richard Huxton

-----Mensaje original-----
De: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]En nombre de Richard Huxton
Enviado el: miércoles, 16 de octubre de 2002 15:14
Para: Jose Antonio Leo
CC: pgsql-general
Asunto: Re: [GENERAL] RV: bad result in a query!! hopeless

>Because it will have to check the index 75,000 times and then fetch that
>many records from the table. That means it's probably going to read all
>the disk-blocks anyway, so the index is just complicating things.
ok


>You might find a partial index helps a little on the descriptions, but
>it won,t do much. See the docs on CREATE INDEX .... WHERE

You thinks is better a partial index or a view or both?


Jose antonio Leo

On Wed, 2002-10-16 at 13:36, Jose Antonio Leo wrote:
>
> >You might find a partial index helps a little on the descriptions, but
> >it won,t do much. See the docs on CREATE INDEX .... WHERE
>
> You thinks is better a partial index or a view or both?

The view is just to simplify the query.
Try the partial index on the query table and see if that helps any.

- Richard Huxton

Query rewritten with some indentation:

SELECT
    vtdiaaec.cod_ae1,
    aecoc.des_ae,

    Sum(vtdiaaec.ven_uni) AS
-- You are missing something here, copy-paste error I presume

    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.

Something I don't understand about this query: why the LEFT JOIN and
not an INNER JOIN? Isn't it true that each row of the vtdiaaec table
that does not have a matching row in aecoc table gets included in the
join result with each field that originates from the aecoc table set to
NULL?
But then, you later remove the rows anyway by adding predicates that
exclude all rows where these fields do not have a 0 value. Wouldn't you
get the same result by using an INNER JOIN instead of a LEFT JOIN? Or
am I missing something?

Jochem

Hi!

-----Mensaje original-----
De: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]En nombre de Jochem van
Dieten
Enviado el: jueves, 17 de octubre de 2002 16:14
Para: Jose Antonio Leo
CC: pgsql-general
Asunto: Re: [GENERAL] RV: bad result in a query!! :-(


Query rewritten with some indentation:

SELECT
    vtdiaaec.cod_ae1,
    aecoc.des_ae,

    Sum(vtdiaaec.ven_uni) AS
-- You are missing something here, copy-paste error I presume
Sorry, that's.

    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.

>Something I don't understand about this query: why the LEFT JOIN and
>not an INNER JOIN? Isn't it true that each row of the vtdiaaec table
>that does not have a matching row in aecoc table gets included in the
>join result with each field that originates from the aecoc table set to
>NULL?
>But then, you later remove the rows anyway by adding predicates that
>exclude all rows where these fields do not have a 0 value. Wouldn't you
>get the same result by using an INNER JOIN instead of a LEFT JOIN? Or
>am I missing something?

How the data base is old, exists sadly articles that they are not codified
with the codes aecoc (they do not have description) and however have sales
reflected to the table vtdiaart (table of daily sales ) whit a code
nonexistent in the table aecoc.
The workers of the department of purchases you are updating it, but at the
moment it exists.

I believe that he was this what you asked.

Jose Antonio


Jochem

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org