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

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
==========================================================================

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

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
==========================================================================

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

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
==========================================================================

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.

Yes, there must be. Informix handles this case perfectly.
(It uses a background btree cleaner)

Andreas

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:

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.

Yes, there must be. Informix handles this case perfectly.
(It uses a background btree cleaner)

Right, I had hoped to fold it into lazy VACUUM, but ran out of time.
(Of course, had I known in August that we'd still not have released
7.2 by now, I might have kept after it :-()

regards, tom lane

Tom Lane wrote:

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:

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.

Yes, there must be. Informix handles this case perfectly.
(It uses a background btree cleaner)

As an idle thought, I wonder what other maintenance tasks we could have
a process in the background automatically doing when system activity is
low ?

Maintenance
***********
- Index compaction
- Vacuum of various flavours

Tuning
******
- cpu_tuple costings (and similar) recalculation(s)

Can't think of anything else off the top of my head though.

Regards and best wishes,

Justin Clift

Right, I had hoped to fold it into lazy VACUUM, but ran out of time.
(Of course, had I known in August that we'd still not have released
7.2 by now, I might have kept after it :-()

regards, tom lane

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

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

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Justin Clift wrote:

Tom Lane wrote:

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:

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.

Yes, there must be. Informix handles this case perfectly.
(It uses a background btree cleaner)

As an idle thought, I wonder what other maintenance tasks we could have
a process in the background automatically doing when system activity is
low ?

Maintenance
***********
- Index compaction
- Vacuum of various flavours

I had a couple thoughts about index compaction and vacuum in the
background:

Could one run a postgresql process in a lower priority process and
perform lazy vacuums without affecting performance all that much?

A live index compaction can be done by indexing the table with a
temporary name rename the old index, rename the new index to the old
name, and drop the old index.

mlw wrote:

Could one run a postgresql process in a lower priority process and
perform lazy vacuums without affecting performance all that much?

One must be very careful not to introduce reverse priority problems -
i.e. a
lower priority process locking some resource and then not letting go
while
higher priority processes are blocked from running due to needing that
lock.

In my tests 1 vacuum process slowed down 100 concurrent pgbench
processes
by ~2 times.

A live index compaction can be done by indexing the table with a
temporary name rename the old index, rename the new index to the old
name, and drop the old index.

Isn't this what REINDEX command does ?

---------------
Hannu

Hannu Krosing wrote:

mlw wrote:

Could one run a postgresql process in a lower priority process and
perform lazy vacuums without affecting performance all that much?

One must be very careful not to introduce reverse priority problems -
i.e. a
lower priority process locking some resource and then not letting go
while
higher priority processes are blocked from running due to needing that
lock.

I understand that, hmm. I wonder if the lock code could boost the priority of a
process which owns a lock.

In my tests 1 vacuum process slowed down 100 concurrent pgbench
processes
by ~2 times.

Is that good or bad?

A live index compaction can be done by indexing the table with a
temporary name rename the old index, rename the new index to the old
name, and drop the old index.

Isn't this what REINDEX command does ?

REINDEX can't be run on a live system, can it?

mlw wrote:

Hannu Krosing wrote:

mlw wrote:

Could one run a postgresql process in a lower priority process and
perform lazy vacuums without affecting performance all that much?

One must be very careful not to introduce reverse priority problems -
i.e. a
lower priority process locking some resource and then not letting go
while
higher priority processes are blocked from running due to needing that
lock.

I understand that, hmm. I wonder if the lock code could boost the priority of a
process which owns a lock.

In my tests 1 vacuum process slowed down 100 concurrent pgbench
processes
by ~2 times.

Is that good or bad?

I had hoped it to take somewhat proportional time, i.e. slow other
backends
down by 1/100.

A live index compaction can be done by indexing the table with a
temporary name rename the old index, rename the new index to the old
name, and drop the old index.

Isn't this what REINDEX command does ?

REINDEX can't be run on a live system, can it?

It will probably lock something, but otherways I don't say why it can't.

You may have to add FORCE to the end of command thus:

reindex table tablename force;

-------------
Hannu

On Tue, Jan 29, 2002 at 07:43:52AM -0500, mlw wrote:

Hannu Krosing wrote:

mlw wrote:

One must be very careful not to introduce reverse priority problems -
i.e. a
lower priority process locking some resource and then not letting go
while
higher priority processes are blocked from running due to needing that
lock.

I understand that, hmm. I wonder if the lock code could boost the priority of a
process which owns a lock.

The classic approach to solving priority inversion is to allow for
priority inheritance: that is, the low-priority process stays low
priority, even when it locks a resource, until there is contention for
that resource from a higher priority process: then it inherits the higher
priority of the waiting process.

Ross