Re: 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.

> 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