Re: RV: bad result in a query!! hopeless

-----Mensaje original-----
De: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)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