Manfred,
well thank's a lot you really help me to clearfy many concepts
about postgres explain and setup parameters.
About the uqery, it's finally done, it takes about 40 hours, and
it update about 9.1M rows, so the planner missed that.
Once again, thanks a lot,
Best Regards
Pablo
On Monday 20 January 2003 07:21 am, Manfred Koizar wrote:
> On Sun, 19 Jan 2003 22:23:49 -0300, Pablo Yaggi
>
> <pyaggi@aulamagna.com.ar> wrote:
> >I had also created and index inars_nocontrib_perm1_inx this way:
> >
> >create index inars_nocontrib_perm1_inx on inars_nocontrib_perm1
> > (ano,mes,cuil,cuit)
>
> How long did this take? Knowing this can help estimating sort cost.
> As I told you I have no gut feeling for large sorts; simply not
> enough experience ...
>
> Does this index still exist?
>
> >but the planner didn't use it, as you can see. That's the way I broke the
> > config file before (enable_seqscan=false).
>
> If your tuples are physically ordered by ano, mes, cuil, cuit, then an
> index scan is almost as fast as a seq scan and there is no need for a
> separate sort step. Unfortunately the planner has its problems with
> multi column indices. So if *you* know that tuples are stored in
> index order in both relations, this might indeed be a good case for
> setting enable_seqscan=off.
>
> >based on your experience/calculation, could you give some advice, do I
> > have to increase sort memory ?
>
> Definitely! I just don't know how much :-(
> A shoot into the dark: 60000 or even 120000, but don't leave it that
> high when you go multiuser.
>
> >do I have to change the query ?
>
> If the sort turns out to be the problem and it is unavoidable, I'd do
> several smaller updates:
>
> UPDATE ... WHERE ano=2000::int2 AND mes=1::int2;
> UPDATE ... WHERE ano=2000::int2 AND mes=2::int2;
> ...
> UPDATE ... WHERE ano=2003::int2 AND mes=1::int2;
>
> >... well something, the query is running from about 28 hours, do I stop it
> >and try something else ? is there anyway to check how long, even
> > estimated, it will take to finish ?
>
> Not that I know of, except watching your disk files grow and trying to
> estimate how many tuples have already been updated ...
>
> Servus
> Manfred
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)