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