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

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