unbalanced indexes -> fixed via dump/restore?

unbalanced indexes -> fixed via dump/restore?

Post by will trilli » Fri, 09 Mar 2001 04:58:23



from reading various posts over the past month, i glean that
indexes can become lopsided and slow if already-sorted input data
is added to the already-indexed table.

if so, is it good practice to

A)
        pg_dump -c mydb > db.out.sql
and then
        psql mydb < db.out.sql
periodically?

or is it better to

B) merely 'reindex' on occasion?

what are the pro's and con's of each approach?

--

and, is there a mechanical method to determine IF reindexing is
a productive venture -- i.e. benchmarking routines or
lopsided-ness detector algorithms?

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
                -- Isaac Asimov, 'The Genetic Code'


http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

unbalanced indexes -> fixed via dump/restore?

Post by Tom La » Fri, 09 Mar 2001 07:23:29



> A)
>    pg_dump -c mydb > db.out.sql
> and then
>    psql mydb < db.out.sql
> periodically?
> or is it better to
> B) merely 'reindex' on occasion?

Plain old DROP INDEX / CREATE INDEX is probably the best-trodden path.
Your (A) seems like vastly more work than is needed.  (B) might be
marginally easier than DROP/CREATE, but I'm not sure how much I trust
REINDEX; it's not been around all that long.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

unbalanced indexes -> fixed via dump/restore?

Post by Alfred Perlste » Fri, 09 Mar 2001 23:13:54




> > A)
> >       pg_dump -c mydb > db.out.sql
> > and then
> >       psql mydb < db.out.sql
> > periodically?

> > or is it better to

> > B) merely 'reindex' on occasion?

> Plain old DROP INDEX / CREATE INDEX is probably the best-trodden path.
> Your (A) seems like vastly more work than is needed.  (B) might be
> marginally easier than DROP/CREATE, but I'm not sure how much I trust
> REINDEX; it's not been around all that long.

Is there a way to do this atomically, meaning so that no one can
get at the table after dropping, but before recreating the index?

lock the table during?

--

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

unbalanced indexes -> fixed via dump/restore?

Post by Tom La » Fri, 09 Mar 2001 23:49:21




>> Plain old DROP INDEX / CREATE INDEX is probably the best-trodden path.
>> Your (A) seems like vastly more work than is needed.  (B) might be
>> marginally easier than DROP/CREATE, but I'm not sure how much I trust
>> REINDEX; it's not been around all that long.
> Is there a way to do this atomically, meaning so that no one can
> get at the table after dropping, but before recreating the index?

In 7.1 it should work to do

        begin;
        drop index fooi;
        create index fooi on foo (...);
        end;

The DROP acquires an exclusive lock on foo, so there's no need for
an explicit "lock table foo", though you can add one if it seems
clearer that way.

Before 7.1 this is too risky, because if the create index fails for
some reason, you're hosed (the attempted rollback of DROP will*up).

btw, REINDEX essentially does the same thing as the above, but there's
a lot of strange additional locking code in it, which I don't trust
much... call it a design disagreement with Hiroshi ;-)

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

unbalanced indexes -> fixed via dump/restore?

Post by Alfred Perlste » Sat, 10 Mar 2001 10:12:39






> > >> Plain old DROP INDEX / CREATE INDEX is probably the best-trodden path.
> > >> Your (A) seems like vastly more work than is needed.  (B) might be
> > >> marginally easier than DROP/CREATE, but I'm not sure how much I trust
> > >> REINDEX; it's not been around all that long.

> > > Is there a way to do this atomically, meaning so that no one can
> > > get at the table after dropping, but before recreating the index?

> > In 7.1 it should work to do

> >         begin;
> >         drop index fooi;
> >         create index fooi on foo (...);
> >         end;

> > The DROP acquires an exclusive lock on foo, so there's no need for
> > an explicit "lock table foo", though you can add one if it seems
> > clearer that way.

> > Before 7.1 this is too risky, because if the create index fails for
> > some reason, you're hosed (the attempted rollback of DROP will*up).

> > btw, REINDEX essentially does the same thing as the above,

> Yes REINDEX is safe under postmaster in 7.1.
> In addtion REINDEX has some advantages.
> 1) no necessity to scatter the index definition.
> 2) it doesn't change any reference among system objects.

> > but there's
> > a lot of strange additional locking code in it,which I don't trust
> > much... call it a design disagreement with Hiroshi ;-)

> Is it LockClassForUpdate() ? If so it's never a special function.
> It's only implementing a 'FOR UPDATE' part of 'SELECT .. FROM PG_CLASS'
> and 'select .. for update' before 'update ..' is an oridinary
> sequence of update operations.

Is there a way to do this under 7.0.3?

--

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

unbalanced indexes -> fixed via dump/restore?

Post by Hiroshi Ino » Sat, 10 Mar 2001 10:11:18





> >> Plain old DROP INDEX / CREATE INDEX is probably the best-trodden path.
> >> Your (A) seems like vastly more work than is needed.  (B) might be
> >> marginally easier than DROP/CREATE, but I'm not sure how much I trust
> >> REINDEX; it's not been around all that long.

> > Is there a way to do this atomically, meaning so that no one can
> > get at the table after dropping, but before recreating the index?

> In 7.1 it should work to do

>         begin;
>         drop index fooi;
>         create index fooi on foo (...);
>         end;

> The DROP acquires an exclusive lock on foo, so there's no need for
> an explicit "lock table foo", though you can add one if it seems
> clearer that way.

> Before 7.1 this is too risky, because if the create index fails for
> some reason, you're hosed (the attempted rollback of DROP will*up).

> btw, REINDEX essentially does the same thing as the above,

Yes REINDEX is safe under postmaster in 7.1.
In addtion REINDEX has some advantages.
1) no necessity to scatter the index definition.
2) it doesn't change any reference among system objects.

Quote:> but there's
> a lot of strange additional locking code in it,which I don't trust
> much... call it a design disagreement with Hiroshi ;-)

Is it LockClassForUpdate() ? If so it's never a special function.
It's only implementing a 'FOR UPDATE' part of 'SELECT .. FROM PG_CLASS'
and 'select .. for update' before 'update ..' is an oridinary
sequence of update operations.

Regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

1. Remote Restore via dump file

Question: I have three sql 6.5 servers

let's say A, B and C

I can have a dump file on the A server and B and can create a backup device
that points to a dump device on A, but on Server C I can not make  
a backup device that points to the A server. I have thought I have checked
everything, but it won't work.

Please advise and thanks in advance.

2. Where do i start?????

3. 'unbalanced' indexes

4. RTE 429

5. Unbalanced indexes

6. View parameters?

7. unbalanced indexes -- common wisdom?

8. Max server memory

9. Restoring DBSpace via ONTAPE ->no database

10. HELP>>HELP>>Using an Index for an MSSQL database

11. Restore from Dump Allocation Error (Failed Restore)

12. attempt to restore, said db was dumped under diff sort order, how to restore it

13. Problem using LEFT via JDBC->ODBC->ACCESS2000