Re: Transaction progress

On Sunday 19 January 2003 07:08 pm, Manfred Koizar wrote:
> On Sun, 19 Jan 2003 14:53:49 -0300, Pablo Yaggi
>
> <pyaggi(at)aulamagna(dot)com(dot)ar> wrote:
> >if not, can I estimate the time from the explain result ? how ?
> >this is the explain result about the query I'm running:
> >
> >Hash Join (cost=100347487.59..145240934.03 rows=26 width=60)
> > -> Index Scan using inars_rectificaciones_inx on inars_rectificaciones
> > b2 (cost=0.00..37839140.62 rows=9546466 width=34)
>
> ^^^^^^^ ^^
> (c) (d)
>
> > -> Hash (cost=100347379.07..100347379.07 rows=43407 width=26)
> > -> Seq Scan on inars_nocontrib_perm1
> > (cost=100000000.00..100347379.07 rows=43407 width=26)
>
> ^^^^^^^^^^^^ ^^^^^^
> (a) (b)
>
> The end result of explain, 145240934.03, basically tells us that you
> have SET enable_seqscan = OFF (a).
You're right, that's a configuration error, i fixed it, but the query is still working,
so it will be working after I restart the server. But anyway the planner says that
it will use Seq scan anyway, so is it not the same if enable_seqscan is on or not ?

>
> (b) looks a bit strange. Does inars_nocontrib_perm1 have 300000 pages
> but only 43407 tuples? If so, you should VACUUM more often. Anyway
> this seq scan contributes only a small fraction to the overall cost.
how did you realize that inars_nocontrib_perm1 is 300000 pages ?
I did a Vacuum before I execute the query that is still in progress, but the plan I tell last time
was made before it, so this is the output of the planner while the query is running:

Merge Join (cost=209527960.44..247478144.43 rows=612 width=60)
-> Sort (cost=209527960.44..209527960.44 rows=35037092 width=26)
-> Seq Scan on inars_nocontrib_perm1 (cost=100000000.00..100697315.92 rows=35037092 width=26)
-> Index Scan using inars_rectificaciones_inx on inars_rectificaciones b2 (cost=0.00..37838713.13 rows=9546358 width=34)

> (c) There are almost 10M rows in inars_rectificaciones, the width of
> which is at least 34 (d). Counting for overhead and columns not used
34 (d) ? Sorry, I didn't get it.

these are the tables and indx:

create table inars_rectificaciones (
cuil int8,cuit int8,ano int2,mes int2,
presentacion date,actividad int2,remuneracion float
);

create table inars_contrib_perm1 (
cuil int8,cuit int8,ano int2,mes int2,
presentacion date,rectificada bool,actividad int2,remuneracion float
);

create index inars_rectificaciones_inx on inars_rectificaciones (ano,mes,cuil,cuit);

> by your statement, let's guess 80 bytes per tuple (might be more).
> This gives at least 800MB for the whole relation. Assuming that your
> physical memory is much less than this and the index
> inars_rectificaciones_inx doesn't retrieve tuples in physical order,
> you need one random page read per tuple. So your question boils down
> to how long it takes to do 10M random reads on your hardware.

my memory size is 512Mb and the processor is a dual pentium 1.4G

> I have to guess again: 4ms seek, 4ms latency. Ignoring CPU and
> transfer times we get 8ms per page access or 80000 seconds (more than
> 22 hours) for the query on average PC hardware. This could be much
> more, if your disk is fragmented or slow or both ...

Thank's a lot,
Pablo