Re: RV: bad result in a query!! hopeless - Mailing list pgsql-general

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