
Is it safe to drop and recreate an index used by a sequence?  Over
three databases I have these key indexes taking up about a gig of
disk space and I need to free it up (since the partition is getting
rather full).

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Fri, 25 Jan 2002 15:24:10 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
sequence indexes
Vince Vielhaber <vev@michvhf.com> writes:
> Is it safe to drop and recreate an index used by a sequence?

Uh, sequences haven't got indexes.  You mean an index on a "serial"
column, no?  Sure, there's no magic there.  Don't forget it's a
unique index, though, if you want to have the same error checking
as before.

			regards, tom lane

Fri, 25 Jan 2002 15:39:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: sequence indexes 
On Fri, 25 Jan 2002, Tom Lane wrote:

> Vince Vielhaber <vev@michvhf.com> writes:
> > Is it safe to drop and recreate an index used by a sequence?
>
> Uh, sequences haven't got indexes.  You mean an index on a "serial"
> column, no?  Sure, there's no magic there.  Don't forget it's a
> unique index, though, if you want to have the same error checking
> as before.

It's a serial column.

 | vev              | newclaim_newclaimid_key          | index    |
 | vev              | newclaim_newclaimid_seq          | sequence |

   577527808 Jan 25 14:50 newclaim_newclaimid_key

select count(*) from newclaim;
count
-----
53747
(1 row)


select max(newclaimid) from newclaim;
    max
-------
9907663
(1 row)

A bit much diskspace for that, isn't it?   The data turns over alot.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Fri, 25 Jan 2002 15:49:33 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: sequence indexes 
Vince Vielhaber <vev@michvhf.com> writes:
> A bit much diskspace for that, isn't it?   The data turns over alot.

Yeah, this is one of the scenarios where we desperately need index
compaction.  The index pages holding the lower serial numbers are
no doubt empty or nearly so, but there's no mechanism for reclaiming
that space short of rebuilding the index.  (BTW you might consider
REINDEX instead of a manual drop/recreate.)

I've looked at the problem a little bit --- there's literature more
recent than Lehmann-Yao that talks about how to do btree compaction
without losing concurrency.  But it didn't get done for 7.2.

			regards, tom lane

Fri, 25 Jan 2002 15:56:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: sequence indexes 
On Fri, 25 Jan 2002, Tom Lane wrote:

> Vince Vielhaber <vev@michvhf.com> writes:
> > A bit much diskspace for that, isn't it?   The data turns over alot.
>
> Yeah, this is one of the scenarios where we desperately need index
> compaction.  The index pages holding the lower serial numbers are
> no doubt empty or nearly so, but there's no mechanism for reclaiming
> that space short of rebuilding the index.  (BTW you might consider
> REINDEX instead of a manual drop/recreate.)

I'm guessing reindex wasn't in 6.5.3. :(


Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Fri, 25 Jan 2002 16:00:03 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: sequence indexes 
Vince Vielhaber <vev@michvhf.com> writes:
> I'm guessing reindex wasn't in 6.5.3. :(

Vince, surely you know better than to still be running 6.5.3 :-(

			regards, tom lane

Fri, 25 Jan 2002 16:01:11 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: sequence indexes 
On Fri, 25 Jan 2002, Tom Lane wrote:

> Vince Vielhaber <vev@michvhf.com> writes:
> > I'm guessing reindex wasn't in 6.5.3. :(
>
> Vince, surely you know better than to still be running 6.5.3 :-(

Actually until just a few minutes ago I thought it was 7.1.3, guess
this thing will be getting upgraded next month.


Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Fri, 25 Jan 2002 16:02:53 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: sequence indexes 