vacuum 59 KB
Newer Older
Bruce Momjian's avatar
Bruce Momjian committed
1
From Inoue@tpf.co.jp Tue Jan 18 19:08:30 2000
2 3 4 5 6 7
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id UAA10148
	for <pgman@candle.pha.pa.us>; Tue, 18 Jan 2000 20:08:27 -0500 (EST)
Received: from cadzone ([126.0.1.40] (may be forged))
          by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
   id KAA02790; Wed, 19 Jan 2000 10:08:02 +0900
Bruce Momjian's avatar
Bruce Momjian committed
8 9 10 11 12 13
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "pgsql-hackers" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] Index recreation in vacuum
Date: Wed, 19 Jan 2000 10:13:40 +0900
Message-ID: <000201bf621a$6b9baf20$2801007e@tpf.co.jp>
14 15 16 17
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Bruce Momjian's avatar
Bruce Momjian committed
18 19 20 21 22 23
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
In-Reply-To: <200001181821.NAA02988@candle.pha.pa.us>
24
Status: ORr
Bruce Momjian's avatar
Bruce Momjian committed
25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> 
> [Charset iso-8859-1 unsupported, filtering to ASCII...]
> > Hi all,
> > 
> > I'm trying to implement REINDEX command.
> > 
> > REINDEX operation itself is available everywhere and
> > I've thought about applying it to VACUUM.
> 
> That is a good idea.  Vacuuming of indexes can be very slow.
> 
> > .
> > My plan is as follows.
> > 
> > Add a new option to force index recreation in vacuum
> > and if index recreation is specified.
> 
> Couldn't we auto-recreate indexes based on the number of tuples moved by
> vacuum,

Yes,we could probably do it. But I'm not sure the availability of new
vacuum.

New vacuum would give us a big advantage that
1) Much faster than current if vacuum remove/moves many tuples.
2) Does shrink index files

But in case of abort/crash
1) couldn't choose index scan for the table
2) unique constraints of the table would be lost

I don't know how people estimate this disadvantage.
  
> 
> > Now I'm inclined to use relhasindex of pg_class to
> > validate/invalidate indexes of a table at once.
> 
> There are a few calls to CatalogIndexInsert() that know the 
> system table they
> are using and know it has indexes, so it does not check that field.  You
> could add cases for that.
>

I think there aren't so many places to check.
I would examine it if my idea is OK.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From owner-pgsql-hackers@hub.org Tue Jan 18 19:15:27 2000
80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98
Received: from hub.org (hub.org [216.126.84.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id UAA10454
	for <pgman@candle.pha.pa.us>; Tue, 18 Jan 2000 20:15:26 -0500 (EST)
Received: from localhost (majordom@localhost)
	by hub.org (8.9.3/8.9.3) with SMTP id UAA42280;
	Tue, 18 Jan 2000 20:10:35 -0500 (EST)
	(envelope-from owner-pgsql-hackers)
Received: by hub.org (bulk_mailer v1.5); Tue, 18 Jan 2000 20:10:30 -0500
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id UAA42081
	for pgsql-hackers-outgoing; Tue, 18 Jan 2000 20:09:31 -0500 (EST)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by hub.org (8.9.3/8.9.3) with ESMTP id UAA41943
	for <pgsql-hackers@postgreSQL.org>; Tue, 18 Jan 2000 20:08:39 -0500 (EST)
	(envelope-from Inoue@tpf.co.jp)
Received: from cadzone ([126.0.1.40] (may be forged))
          by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
   id KAA02790; Wed, 19 Jan 2000 10:08:02 +0900
Bruce Momjian's avatar
Bruce Momjian committed
99 100 101 102 103 104
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "pgsql-hackers" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] Index recreation in vacuum
Date: Wed, 19 Jan 2000 10:13:40 +0900
Message-ID: <000201bf621a$6b9baf20$2801007e@tpf.co.jp>
105 106 107 108
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Bruce Momjian's avatar
Bruce Momjian committed
109 110 111 112 113 114 115
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
In-Reply-To: <200001181821.NAA02988@candle.pha.pa.us>
Sender: owner-pgsql-hackers@postgreSQL.org
116
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> 
> [Charset iso-8859-1 unsupported, filtering to ASCII...]
> > Hi all,
> > 
> > I'm trying to implement REINDEX command.
> > 
> > REINDEX operation itself is available everywhere and
> > I've thought about applying it to VACUUM.
> 
> That is a good idea.  Vacuuming of indexes can be very slow.
> 
> > .
> > My plan is as follows.
> > 
> > Add a new option to force index recreation in vacuum
> > and if index recreation is specified.
> 
> Couldn't we auto-recreate indexes based on the number of tuples moved by
> vacuum,

Yes,we could probably do it. But I'm not sure the availability of new
vacuum.

New vacuum would give us a big advantage that
1) Much faster than current if vacuum remove/moves many tuples.
2) Does shrink index files

But in case of abort/crash
1) couldn't choose index scan for the table
2) unique constraints of the table would be lost

I don't know how people estimate this disadvantage.
  
> 
> > Now I'm inclined to use relhasindex of pg_class to
> > validate/invalidate indexes of a table at once.
> 
> There are a few calls to CatalogIndexInsert() that know the 
> system table they
> are using and know it has indexes, so it does not check that field.  You
> could add cases for that.
>

I think there aren't so many places to check.
I would examine it if my idea is OK.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

************

From owner-pgsql-hackers@hub.org Tue Jan 18 19:57:21 2000
174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192
Received: from hub.org (hub.org [216.126.84.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id UAA11764
	for <pgman@candle.pha.pa.us>; Tue, 18 Jan 2000 20:57:19 -0500 (EST)
Received: from localhost (majordom@localhost)
	by hub.org (8.9.3/8.9.3) with SMTP id UAA50653;
	Tue, 18 Jan 2000 20:52:38 -0500 (EST)
	(envelope-from owner-pgsql-hackers)
Received: by hub.org (bulk_mailer v1.5); Tue, 18 Jan 2000 20:52:30 -0500
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id UAA50513
	for pgsql-hackers-outgoing; Tue, 18 Jan 2000 20:51:32 -0500 (EST)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
	by hub.org (8.9.3/8.9.3) with ESMTP id UAA50462
	for <pgsql-hackers@postgreSQL.org>; Tue, 18 Jan 2000 20:51:06 -0500 (EST)
	(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.9.0/8.9.0) id UAA11421;
	Tue, 18 Jan 2000 20:50:50 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
193 194 195 196 197 198 199 200 201
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001190150.UAA11421@candle.pha.pa.us>
Subject: Re: [HACKERS] Index recreation in vacuum
In-Reply-To: <000201bf621a$6b9baf20$2801007e@tpf.co.jp> from Hiroshi Inoue at
	"Jan 19, 2000 10:13:40 am"
To: Hiroshi Inoue <Inoue@tpf.co.jp>
Date: Tue, 18 Jan 2000 20:50:50 -0500 (EST)
CC: pgsql-hackers <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL66 (25)]
202 203 204
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Bruce Momjian's avatar
Bruce Momjian committed
205
Sender: owner-pgsql-hackers@postgreSQL.org
206
Status: ORr
Bruce Momjian's avatar
Bruce Momjian committed
207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263

> > > Add a new option to force index recreation in vacuum
> > > and if index recreation is specified.
> > 
> > Couldn't we auto-recreate indexes based on the number of tuples moved by
> > vacuum,
> 
> Yes,we could probably do it. But I'm not sure the availability of new
> vacuum.
> 
> New vacuum would give us a big advantage that
> 1) Much faster than current if vacuum remove/moves many tuples.
> 2) Does shrink index files
> 
> But in case of abort/crash
> 1) couldn't choose index scan for the table
> 2) unique constraints of the table would be lost
> 
> I don't know how people estimate this disadvantage.

That's why I was recommending rename().  The actual window of
vunerability goes from perhaps hours to fractions of a second.

In fact, if I understand this right, you could make the vulerability
zero by just performing the rename as one operation.

In fact, for REINDEX cases where you don't have a lock on the entire
table as you do in vacuum, you could reindex the table with a simple
read-lock on the base table and index, and move the new index into place
with the users seeing no change.  Only people traversing the index
during the change would have a problem.  You just need an exclusive
access on the index for the duration of the rename() so no one is
traversing the index during the rename().

Destroying the index and recreating opens a large time span that there
is no index, and you have to jury-rig something so people don't try to
use the index.  With rename() you just put the new index in place with
one operation.  Just don't let people traverse the index during the
change.  The pointers to the heap tuples is the same in both indexes.

In fact, with WAL, we will allow multiple physical files for the same
table by appending the table oid to the file name.  In this case, the
old index could be deleted by rename, and people would continue to use
the old index until they deleted the open file pointers.  Not sure how
this works in practice because new tuples would not be inserted into the
old copy of the index.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

************

From pgman Tue Jan 18 20:04:11 2000
264 265 266
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.9.0/8.9.0) id VAA11990;
	Tue, 18 Jan 2000 21:04:11 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
267 268 269 270 271 272 273 274 275 276
From: Bruce Momjian <pgman>
Message-Id: <200001190204.VAA11990@candle.pha.pa.us>
Subject: Re: [HACKERS] Index recreation in vacuum
In-Reply-To: <200001190150.UAA11421@candle.pha.pa.us> from Bruce Momjian at "Jan
	18, 2000 08:50:50 pm"
To: Bruce Momjian <pgman@candle.pha.pa.us>
Date: Tue, 18 Jan 2000 21:04:11 -0500 (EST)
CC: Hiroshi Inoue <Inoue@tpf.co.jp>,
        pgsql-hackers <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL66 (25)]
277 278 279 280
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331

> > I don't know how people estimate this disadvantage.
> 
> That's why I was recommending rename().  The actual window of
> vunerability goes from perhaps hours to fractions of a second.
> 
> In fact, if I understand this right, you could make the vulerability
> zero by just performing the rename as one operation.
> 
> In fact, for REINDEX cases where you don't have a lock on the entire
> table as you do in vacuum, you could reindex the table with a simple
> read-lock on the base table and index, and move the new index into place
> with the users seeing no change.  Only people traversing the index
> during the change would have a problem.  You just need an exclusive
> access on the index for the duration of the rename() so no one is
> traversing the index during the rename().
> 
> Destroying the index and recreating opens a large time span that there
> is no index, and you have to jury-rig something so people don't try to
> use the index.  With rename() you just put the new index in place with
> one operation.  Just don't let people traverse the index during the
> change.  The pointers to the heap tuples is the same in both indexes.
> 
> In fact, with WAL, we will allow multiple physical files for the same
> table by appending the table oid to the file name.  In this case, the
> old index could be deleted by rename, and people would continue to use
> the old index until they deleted the open file pointers.  Not sure how
> this works in practice because new tuples would not be inserted into the
> old copy of the index.

Maybe I am all wrong here.  Maybe most of the advantage of rename() are
meaningless with reindex using during vacuum, which is the most
important use of reindex.

Let's look at index using during vacuum.  Right now, how does vacuum
handle indexes when it moves a tuple?  Does it do each index update as
it moves a tuple?  Is that why it is so slow?

If we don't do that and vacuum fails, what state is the table left in? 
If we don't update the index for every tuple, the index is invalid in a
vacuum failure.  rename() is not going to help us here.  It keeps the
old index around, but the index is invalid anyway, right?


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From Inoue@tpf.co.jp Tue Jan 18 20:18:48 2000
332 333 334 335 336 337
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id VAA12437
	for <pgman@candle.pha.pa.us>; Tue, 18 Jan 2000 21:18:46 -0500 (EST)
Received: from cadzone ([126.0.1.40] (may be forged))
          by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
   id LAA02845; Wed, 19 Jan 2000 11:18:18 +0900
Bruce Momjian's avatar
Bruce Momjian committed
338 339 340 341 342 343
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "pgsql-hackers" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] Index recreation in vacuum
Date: Wed, 19 Jan 2000 11:23:55 +0900
Message-ID: <000801bf6224$3bfdd9a0$2801007e@tpf.co.jp>
344 345 346 347
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Bruce Momjian's avatar
Bruce Momjian committed
348 349 350 351 352 353
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
In-Reply-To: <200001190204.VAA11990@candle.pha.pa.us>
354
Status: ORr
Bruce Momjian's avatar
Bruce Momjian committed
355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
>
> > > I don't know how people estimate this disadvantage.
> >
> > That's why I was recommending rename().  The actual window of
> > vunerability goes from perhaps hours to fractions of a second.
> >
> > In fact, if I understand this right, you could make the vulerability
> > zero by just performing the rename as one operation.
> >
> > In fact, for REINDEX cases where you don't have a lock on the entire
> > table as you do in vacuum, you could reindex the table with a simple
> > read-lock on the base table and index, and move the new index into place
> > with the users seeing no change.  Only people traversing the index
> > during the change would have a problem.  You just need an exclusive
> > access on the index for the duration of the rename() so no one is
> > traversing the index during the rename().
> >
> > Destroying the index and recreating opens a large time span that there
> > is no index, and you have to jury-rig something so people don't try to
> > use the index.  With rename() you just put the new index in place with
> > one operation.  Just don't let people traverse the index during the
> > change.  The pointers to the heap tuples is the same in both indexes.
> >
> > In fact, with WAL, we will allow multiple physical files for the same
> > table by appending the table oid to the file name.  In this case, the
> > old index could be deleted by rename, and people would continue to use
> > the old index until they deleted the open file pointers.  Not sure how
> > this works in practice because new tuples would not be inserted into the
> > old copy of the index.
>
> Maybe I am all wrong here.  Maybe most of the advantage of rename() are
> meaningless with reindex using during vacuum, which is the most
> important use of reindex.
>
> Let's look at index using during vacuum.  Right now, how does vacuum
> handle indexes when it moves a tuple?  Does it do each index update as
> it moves a tuple?  Is that why it is so slow?
>

Yes,I believe so.  It's necessary to keep consistency between heap
table and indexes even in case of abort/crash.
As far as I see,it has been a big charge for vacuum.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp


From owner-pgsql-hackers@hub.org Tue Jan 18 20:53:49 2000
408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426
Received: from hub.org (hub.org [216.126.84.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id VAA13285
	for <pgman@candle.pha.pa.us>; Tue, 18 Jan 2000 21:53:47 -0500 (EST)
Received: from localhost (majordom@localhost)
	by hub.org (8.9.3/8.9.3) with SMTP id VAA65183;
	Tue, 18 Jan 2000 21:47:47 -0500 (EST)
	(envelope-from owner-pgsql-hackers)
Received: by hub.org (bulk_mailer v1.5); Tue, 18 Jan 2000 21:47:33 -0500
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id VAA65091
	for pgsql-hackers-outgoing; Tue, 18 Jan 2000 21:46:33 -0500 (EST)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
	by hub.org (8.9.3/8.9.3) with ESMTP id VAA65034
	for <pgsql-hackers@postgreSQL.org>; Tue, 18 Jan 2000 21:46:12 -0500 (EST)
	(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.9.0/8.9.0) id VAA13040;
	Tue, 18 Jan 2000 21:45:27 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
427 428 429 430 431 432 433 434 435
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001190245.VAA13040@candle.pha.pa.us>
Subject: Re: [HACKERS] Index recreation in vacuum
In-Reply-To: <000801bf6224$3bfdd9a0$2801007e@tpf.co.jp> from Hiroshi Inoue at
	"Jan 19, 2000 11:23:55 am"
To: Hiroshi Inoue <Inoue@tpf.co.jp>
Date: Tue, 18 Jan 2000 21:45:27 -0500 (EST)
CC: pgsql-hackers <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL66 (25)]
436 437 438
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Bruce Momjian's avatar
Bruce Momjian committed
439
Sender: owner-pgsql-hackers@postgreSQL.org
440
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490

> > > In fact, for REINDEX cases where you don't have a lock on the entire
> > > table as you do in vacuum, you could reindex the table with a simple
> > > read-lock on the base table and index, and move the new index into place
> > > with the users seeing no change.  Only people traversing the index
> > > during the change would have a problem.  You just need an exclusive
> > > access on the index for the duration of the rename() so no one is
> > > traversing the index during the rename().
> > >
> > > Destroying the index and recreating opens a large time span that there
> > > is no index, and you have to jury-rig something so people don't try to
> > > use the index.  With rename() you just put the new index in place with
> > > one operation.  Just don't let people traverse the index during the
> > > change.  The pointers to the heap tuples is the same in both indexes.
> > >
> > > In fact, with WAL, we will allow multiple physical files for the same
> > > table by appending the table oid to the file name.  In this case, the
> > > old index could be deleted by rename, and people would continue to use
> > > the old index until they deleted the open file pointers.  Not sure how
> > > this works in practice because new tuples would not be inserted into the
> > > old copy of the index.
> >
> > Maybe I am all wrong here.  Maybe most of the advantage of rename() are
> > meaningless with reindex using during vacuum, which is the most
> > important use of reindex.
> >
> > Let's look at index using during vacuum.  Right now, how does vacuum
> > handle indexes when it moves a tuple?  Does it do each index update as
> > it moves a tuple?  Is that why it is so slow?
> >
> 
> Yes,I believe so.  It's necessary to keep consistency between heap
> table and indexes even in case of abort/crash.
> As far as I see,it has been a big charge for vacuum.

OK, how about making a copy of the heap table before starting vacuum,
moving all the tuples in that copy, create new index, and then move the
new heap and indexes over the old version.  We already have an exclusive
lock on the table.  That would be 100% reliable, with the disadvantage
of using 2x the disk space.  Seems like a big win.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

************

From owner-pgsql-hackers@hub.org Tue Jan 18 21:15:24 2000
491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509
Received: from hub.org (hub.org [216.126.84.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id WAA14115
	for <pgman@candle.pha.pa.us>; Tue, 18 Jan 2000 22:15:23 -0500 (EST)
Received: from localhost (majordom@localhost)
	by hub.org (8.9.3/8.9.3) with SMTP id WAA72950;
	Tue, 18 Jan 2000 22:10:40 -0500 (EST)
	(envelope-from owner-pgsql-hackers)
Received: by hub.org (bulk_mailer v1.5); Tue, 18 Jan 2000 22:10:32 -0500
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id WAA72644
	for pgsql-hackers-outgoing; Tue, 18 Jan 2000 22:09:36 -0500 (EST)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
	by hub.org (8.9.3/8.9.3) with ESMTP id WAA72504
	for <pgsql-hackers@postgreSQL.org>; Tue, 18 Jan 2000 22:08:40 -0500 (EST)
	(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.9.0/8.9.0) id WAA13965;
	Tue, 18 Jan 2000 22:08:25 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
510 511 512 513 514 515 516 517 518
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001190308.WAA13965@candle.pha.pa.us>
Subject: Re: [HACKERS] Index recreation in vacuum
In-Reply-To: <000f01bf622a$bf423940$2801007e@tpf.co.jp> from Hiroshi Inoue at
	"Jan 19, 2000 12:10:32 pm"
To: Hiroshi Inoue <Inoue@tpf.co.jp>
Date: Tue, 18 Jan 2000 22:08:25 -0500 (EST)
CC: pgsql-hackers <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL66 (25)]
519 520 521
MIME-Version: 1.0
Content-Type: text/plain; charset=UNKNOWN-8BIT
Content-Transfer-Encoding: 8bit
Bruce Momjian's avatar
Bruce Momjian committed
522
Sender: owner-pgsql-hackers@postgreSQL.org
523
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
524

525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558
> I heard from someone that old vacuum had been like so.
> Probably 2x disk space for big tables was a big disadvantage.

That's interesting.

> 
> In addition,rename(),unlink(),mv aren't preferable for transaction
> control as far as I see. We couldn't avoid inconsistency using
> those OS functions.

I disagree.  Vacuum can't be rolled back anyway in the sense you can
bring back expire tuples, though I have no idea why you would want to.

You have an exclusive lock on the table.  Putting new heap/indexes in
place that match and have no expired tuples seems like it can not fail
in any situation.

Of course, the buffers of the old table have to be marked as invalid,
but with an exclusive lock, that is not a problem.  I am sure we do that
anyway in vacuum.

> We have to wait the change of relation file naming if copying
> vacuum is needed.
> Under the spec we need not rename(),mv etc.

Sorry, I don't agree, yet...

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

************
Bruce Momjian's avatar
Bruce Momjian committed
559 560

From Inoue@tpf.co.jp Tue Jan 18 21:05:23 2000
561 562 563 564 565 566
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id WAA13858
	for <pgman@candle.pha.pa.us>; Tue, 18 Jan 2000 22:05:21 -0500 (EST)
Received: from cadzone ([126.0.1.40] (may be forged))
          by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
   id MAA02870; Wed, 19 Jan 2000 12:04:55 +0900
Bruce Momjian's avatar
Bruce Momjian committed
567 568 569 570 571 572
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "pgsql-hackers" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] Index recreation in vacuum
Date: Wed, 19 Jan 2000 12:10:32 +0900
Message-ID: <000f01bf622a$bf423940$2801007e@tpf.co.jp>
573 574 575 576
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Bruce Momjian's avatar
Bruce Momjian committed
577 578 579 580 581 582
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal
In-Reply-To: <200001190245.VAA13040@candle.pha.pa.us>
583
Status: ORr
Bruce Momjian's avatar
Bruce Momjian committed
584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> > >
> > > Maybe I am all wrong here.  Maybe most of the advantage of
> rename() are
> > > meaningless with reindex using during vacuum, which is the most
> > > important use of reindex.
> > >
> > > Let's look at index using during vacuum.  Right now, how does vacuum
> > > handle indexes when it moves a tuple?  Does it do each index update as
> > > it moves a tuple?  Is that why it is so slow?
> > >
> >
> > Yes,I believe so.  It's necessary to keep consistency between heap
> > table and indexes even in case of abort/crash.
> > As far as I see,it has been a big charge for vacuum.
>
> OK, how about making a copy of the heap table before starting vacuum,
> moving all the tuples in that copy, create new index, and then move the
> new heap and indexes over the old version.  We already have an exclusive
> lock on the table.  That would be 100% reliable, with the disadvantage
> of using 2x the disk space.  Seems like a big win.
>

I heard from someone that old vacuum had been like so.
Probably 2x disk space for big tables was a big disadvantage.

In addition,rename(),unlink(),mv aren't preferable for transaction
control as far as I see. We couldn't avoid inconsistency using
those OS functions.
We have to wait the change of relation file naming if copying
vacuum is needed.
Under the spec we need not rename(),mv etc.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp




From dms@wplus.net Wed Jan 19 15:30:40 2000
628 629 630
Received: from relay.wplus.net (relay.wplus.net [195.131.52.179])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id QAA25919
	for <pgman@candle.pha.pa.us>; Wed, 19 Jan 2000 16:30:38 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
631
X-Real-To: pgman@candle.pha.pa.us
632 633 634
Received: from wplus.net (ppdms.dialup.wplus.net [195.131.52.71])
	by relay.wplus.net (8.9.1/8.9.1/wplus.2) with ESMTP id AAA64218;
	Thu, 20 Jan 2000 00:26:37 +0300 (MSK)
Bruce Momjian's avatar
Bruce Momjian committed
635 636 637 638 639
Message-ID: <38862C9D.C2151E4E@wplus.net>
Date: Thu, 20 Jan 2000 00:29:01 +0300
From: Dmitry Samersoff <dms@wplus.net>
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: ru,en
640
MIME-Version: 1.0
Bruce Momjian's avatar
Bruce Momjian committed
641 642 643 644 645
To: Hiroshi Inoue <Inoue@tpf.co.jp>
CC: Bruce Momjian <pgman@candle.pha.pa.us>,
        pgsql-hackers <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Index recreation in vacuum
References: <000f01bf622a$bf423940$2801007e@tpf.co.jp>
646 647 648
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit
Status: ORr
Bruce Momjian's avatar
Bruce Momjian committed
649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686

Hiroshi Inoue wrote:
> > > Yes,I believe so.  It's necessary to keep consistency between heap
> > > table and indexes even in case of abort/crash.
> > > As far as I see,it has been a big charge for vacuum.
> >
> > OK, how about making a copy of the heap table before starting vacuum,
> > moving all the tuples in that copy, create new index, and then move the
> > new heap and indexes over the old version.  We already have an exclusive
> > lock on the table.  That would be 100% reliable, with the disadvantage
> > of using 2x the disk space.  Seems like a big win.
> >
> 
> I heard from someone that old vacuum had been like so.
> Probably 2x disk space for big tables was a big disadvantage.

Yes, It is critical.

How about sequence like this:

* Drop indices (keeping somewhere index descriptions)
* vacuuming table
* recreate indices

If something crash, user have been noticed 
to re-run vacuum or recreate indices by hand 
when system restarts.

I use script like described above for vacuuming
 - it really increase vacuum performance for large table.


-- 
Dmitry Samersoff, DM\S
dms@wplus.net http://devnull.wplus.net
* there will come soft rains

From dms@wplus.net Wed Jan 19 15:42:49 2000
687 688 689
Received: from relay.wplus.net (relay.wplus.net [195.131.52.179])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id QAA26645
	for <pgman@candle.pha.pa.us>; Wed, 19 Jan 2000 16:42:47 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
690
X-Real-To: pgman@candle.pha.pa.us
691 692 693
Received: from wplus.net (ppdms.dialup.wplus.net [195.131.52.71])
	by relay.wplus.net (8.9.1/8.9.1/wplus.2) with ESMTP id AAA65264;
	Thu, 20 Jan 2000 00:39:02 +0300 (MSK)
Bruce Momjian's avatar
Bruce Momjian committed
694 695 696 697 698
Message-ID: <38862F86.20328BD3@wplus.net>
Date: Thu, 20 Jan 2000 00:41:26 +0300
From: Dmitry Samersoff <dms@wplus.net>
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: ru,en
699
MIME-Version: 1.0
Bruce Momjian's avatar
Bruce Momjian committed
700 701 702 703 704
To: Bruce Momjian <pgman@candle.pha.pa.us>
CC: Hiroshi Inoue <Inoue@tpf.co.jp>,
        pgsql-hackers <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Index recreation in vacuum
References: <200001192132.QAA26048@candle.pha.pa.us>
705 706 707
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit
Status: ORr
Bruce Momjian's avatar
Bruce Momjian committed
708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726

Bruce Momjian wrote:
> 
> We need two things:
> 

>         auto-create index on startup

IMHO, It have to be controlled by user, because creating large index 
can take a number of hours. Sometimes it's better to live without
indices
at all, and then build it by hand after workday end.


-- 
Dmitry Samersoff, DM\S
dms@wplus.net http://devnull.wplus.net
* there will come soft rains

727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782
From owner-pgsql-hackers@hub.org Thu Jan 20 23:51:34 2000
Received: from hub.org (hub.org [216.126.84.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id AAA13891
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 00:51:31 -0500 (EST)
Received: from localhost (majordom@localhost)
	by hub.org (8.9.3/8.9.3) with SMTP id AAA91784;
	Fri, 21 Jan 2000 00:47:07 -0500 (EST)
	(envelope-from owner-pgsql-hackers)
Received: by hub.org (bulk_mailer v1.5); Fri, 21 Jan 2000 00:45:38 -0500
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id AAA91495
	for pgsql-hackers-outgoing; Fri, 21 Jan 2000 00:44:40 -0500 (EST)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
	by hub.org (8.9.3/8.9.3) with ESMTP id AAA91378
	for <pgsql-hackers@postgreSQL.org>; Fri, 21 Jan 2000 00:44:04 -0500 (EST)
	(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.9.0/8.9.0) id AAA13592;
	Fri, 21 Jan 2000 00:43:49 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001210543.AAA13592@candle.pha.pa.us>
Subject: [HACKERS] vacuum timings
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 21 Jan 2000 00:43:49 -0500 (EST)
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL66 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Sender: owner-pgsql-hackers@postgreSQL.org
Status: OR

I loaded 10,000,000 rows into CREATE TABLE test (x INTEGER);  Table is
400MB and index is 160MB.

With index on the single in4 column, I got:
	 78 seconds for a vacuum
	121 seconds for vacuum after deleting a single row
	662 seconds for vacuum after deleting the entire table

With no index, I got:
	 43 seconds for a vacuum
	 43 seconds for vacuum after deleting a single row
	 43 seconds for vacuum after deleting the entire table

I find this quite interesting.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

************

Bruce Momjian's avatar
Bruce Momjian committed
783
From owner-pgsql-hackers@hub.org Fri Jan 21 00:34:56 2000
784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802
Received: from hub.org (hub.org [216.126.84.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id BAA15559
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 01:34:55 -0500 (EST)
Received: from localhost (majordom@localhost)
	by hub.org (8.9.3/8.9.3) with SMTP id BAA06108;
	Fri, 21 Jan 2000 01:32:23 -0500 (EST)
	(envelope-from owner-pgsql-hackers)
Received: by hub.org (bulk_mailer v1.5); Fri, 21 Jan 2000 01:30:38 -0500
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id BAA03704
	for pgsql-hackers-outgoing; Fri, 21 Jan 2000 01:27:53 -0500 (EST)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from sunpine.krs.ru (SunPine.krs.ru [195.161.16.37])
	by hub.org (8.9.3/8.9.3) with ESMTP id BAA01710
	for <pgsql-hackers@postgreSQL.org>; Fri, 21 Jan 2000 01:26:44 -0500 (EST)
	(envelope-from vadim@krs.ru)
Received: from krs.ru (dune.krs.ru [195.161.16.38])
	by sunpine.krs.ru (8.8.8/8.8.8) with ESMTP id NAA01685;
	Fri, 21 Jan 2000 13:26:33 +0700 (KRS)
Bruce Momjian's avatar
Bruce Momjian committed
803 804 805 806 807 808
Message-ID: <3887FC19.80305217@krs.ru>
Date: Fri, 21 Jan 2000 13:26:33 +0700
From: Vadim Mikheev <vadim@krs.ru>
Organization: OJSC Rostelecom (Krasnoyarsk)
X-Mailer: Mozilla 4.5 [en] (X11; I; FreeBSD 3.0-RELEASE i386)
X-Accept-Language: ru, en
809
MIME-Version: 1.0
Bruce Momjian's avatar
Bruce Momjian committed
810 811 812 813 814
To: Bruce Momjian <pgman@candle.pha.pa.us>
CC: Tom Lane <tgl@sss.pgh.pa.us>,
        PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] vacuum timings
References: <200001210543.AAA13592@candle.pha.pa.us>
815 816
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Bruce Momjian's avatar
Bruce Momjian committed
817
Sender: owner-pgsql-hackers@postgreSQL.org
818
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841

Bruce Momjian wrote:
> 
> I loaded 10,000,000 rows into CREATE TABLE test (x INTEGER);  Table is
> 400MB and index is 160MB.
> 
> With index on the single in4 column, I got:
>          78 seconds for a vacuum
>         121 seconds for vacuum after deleting a single row
>         662 seconds for vacuum after deleting the entire table
> 
> With no index, I got:
>          43 seconds for a vacuum
>          43 seconds for vacuum after deleting a single row
>          43 seconds for vacuum after deleting the entire table

Wi/wo -F ?

Vadim

************

From vadim@krs.ru Fri Jan 21 00:26:33 2000
842 843 844 845 846 847
Received: from sunpine.krs.ru (SunPine.krs.ru [195.161.16.37])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id BAA15239
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 01:26:31 -0500 (EST)
Received: from krs.ru (dune.krs.ru [195.161.16.38])
	by sunpine.krs.ru (8.8.8/8.8.8) with ESMTP id NAA01685;
	Fri, 21 Jan 2000 13:26:33 +0700 (KRS)
Bruce Momjian's avatar
Bruce Momjian committed
848 849 850 851 852 853 854
Sender: root@sunpine.krs.ru
Message-ID: <3887FC19.80305217@krs.ru>
Date: Fri, 21 Jan 2000 13:26:33 +0700
From: Vadim Mikheev <vadim@krs.ru>
Organization: OJSC Rostelecom (Krasnoyarsk)
X-Mailer: Mozilla 4.5 [en] (X11; I; FreeBSD 3.0-RELEASE i386)
X-Accept-Language: ru, en
855
MIME-Version: 1.0
Bruce Momjian's avatar
Bruce Momjian committed
856 857 858 859 860
To: Bruce Momjian <pgman@candle.pha.pa.us>
CC: Tom Lane <tgl@sss.pgh.pa.us>,
        PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] vacuum timings
References: <200001210543.AAA13592@candle.pha.pa.us>
861 862 863
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Status: ORr
Bruce Momjian's avatar
Bruce Momjian committed
864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884

Bruce Momjian wrote:
> 
> I loaded 10,000,000 rows into CREATE TABLE test (x INTEGER);  Table is
> 400MB and index is 160MB.
> 
> With index on the single in4 column, I got:
>          78 seconds for a vacuum
>         121 seconds for vacuum after deleting a single row
>         662 seconds for vacuum after deleting the entire table
> 
> With no index, I got:
>          43 seconds for a vacuum
>          43 seconds for vacuum after deleting a single row
>          43 seconds for vacuum after deleting the entire table

Wi/wo -F ?

Vadim

From Inoue@tpf.co.jp Fri Jan 21 00:40:35 2000
885 886 887 888 889 890
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id BAA15684
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 01:40:33 -0500 (EST)
Received: from cadzone ([126.0.1.40] (may be forged))
          by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
   id PAA04316; Fri, 21 Jan 2000 15:40:35 +0900
Bruce Momjian's avatar
Bruce Momjian committed
891 892 893 894 895 896 897
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "PostgreSQL-development" <pgsql-hackers@postgreSQL.org>,
        "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: RE: [HACKERS] vacuum timings
Date: Fri, 21 Jan 2000 15:46:15 +0900
Message-ID: <000201bf63db$36cdae20$2801007e@tpf.co.jp>
898 899 900 901
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Bruce Momjian's avatar
Bruce Momjian committed
902 903 904 905 906 907
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
In-Reply-To: <200001210543.AAA13592@candle.pha.pa.us>
908
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Bruce Momjian
> 
> I loaded 10,000,000 rows into CREATE TABLE test (x INTEGER);  Table is
> 400MB and index is 160MB.
> 
> With index on the single in4 column, I got:
> 	 78 seconds for a vacuum
		vc_vaconeind() is called once

> 	121 seconds for vacuum after deleting a single row
		vc_vaconeind() is called twice

Hmmm,vc_vaconeind() takes pretty long time even if it does little. 

> 	662 seconds for vacuum after deleting the entire table
>

How about half of the rows deleted case ?
It would take longer time.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From owner-pgsql-hackers@hub.org Fri Jan 21 12:00:49 2000
938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956
Received: from hub.org (hub.org [216.126.84.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id NAA13329
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 13:00:47 -0500 (EST)
Received: from localhost (majordom@localhost)
	by hub.org (8.9.3/8.9.3) with SMTP id MAA96106;
	Fri, 21 Jan 2000 12:55:34 -0500 (EST)
	(envelope-from owner-pgsql-hackers)
Received: by hub.org (bulk_mailer v1.5); Fri, 21 Jan 2000 12:53:53 -0500
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id MAA95775
	for pgsql-hackers-outgoing; Fri, 21 Jan 2000 12:52:54 -0500 (EST)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67])
	by hub.org (8.9.3/8.9.3) with ESMTP id MAA95720
	for <pgsql-hackers@postgreSQL.org>; Fri, 21 Jan 2000 12:52:39 -0500 (EST)
	(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.9.0/8.9.0) id MAA12106;
	Fri, 21 Jan 2000 12:51:53 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
957 958 959 960 961 962 963 964 965
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001211751.MAA12106@candle.pha.pa.us>
Subject: [HACKERS] Re: vacuum timings
In-Reply-To: <3641.948433911@sss.pgh.pa.us> from Tom Lane at "Jan 21, 2000 00:51:51
	am"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 21 Jan 2000 12:51:53 -0500 (EST)
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL66 (25)]
966 967 968
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Bruce Momjian's avatar
Bruce Momjian committed
969
Sender: owner-pgsql-hackers@postgreSQL.org
970
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I loaded 10,000,000 rows into CREATE TABLE test (x INTEGER);  Table is
> > 400MB and index is 160MB.
> 
> > With index on the single in4 column, I got:
> > 	 78 seconds for a vacuum
> > 	121 seconds for vacuum after deleting a single row
> > 	662 seconds for vacuum after deleting the entire table
> 
> > With no index, I got:
> > 	 43 seconds for a vacuum
> > 	 43 seconds for vacuum after deleting a single row
> > 	 43 seconds for vacuum after deleting the entire table
> 
> > I find this quite interesting.
> 
> How long does it take to create the index on your setup --- ie,
> if vacuum did a drop/create index, would it be competitive?

OK, new timings with -F enabled:

	index	no index
	519	same	load	
	247	"	first vacuum
	40	"	other vacuums
	
	1222	X	index creation
	90	X	first vacuum
	80	X	other vacuums
	
	<1	90	delete one row
	121	38	vacuum after delete 1 row
	
	346	344	delete all rows
	440	44	first vacuum
	20	<1	other vacuums(index is still same size)

Conclusions:

	o  indexes never get smaller
	o  drop/recreate index is slower than vacuum of indexes

What other conclusions can be made?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

************

From scrappy@hub.org Fri Jan 21 12:45:38 2000
1025 1026 1027 1028 1029 1030 1031
Received: from thelab.hub.org (nat200.60.mpoweredpc.net [142.177.200.60])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id NAA14380
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 13:45:29 -0500 (EST)
Received: from localhost (scrappy@localhost)
	by thelab.hub.org (8.9.3/8.9.1) with ESMTP id OAA68289;
	Fri, 21 Jan 2000 14:45:35 -0400 (AST)
	(envelope-from scrappy@hub.org)
Bruce Momjian's avatar
Bruce Momjian committed
1032 1033 1034 1035 1036 1037 1038 1039 1040
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Fri, 21 Jan 2000 14:45:34 -0400 (AST)
From: The Hermit Hacker <scrappy@hub.org>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Tom Lane <tgl@sss.pgh.pa.us>,
        PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: vacuum timings
In-Reply-To: <200001211751.MAA12106@candle.pha.pa.us>
Message-ID: <Pine.BSF.4.21.0001211443480.23487-100000@thelab.hub.org>
1041 1042 1043
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077

On Fri, 21 Jan 2000, Bruce Momjian wrote:

> OK, new timings with -F enabled:
> 
> 	index	no index
> 	519	same	load	
> 	247	"	first vacuum
> 	40	"	other vacuums
> 	
> 	1222	X	index creation
> 	90	X	first vacuum
> 	80	X	other vacuums
> 	
> 	<1	90	delete one row
> 	121	38	vacuum after delete 1 row
> 	
> 	346	344	delete all rows
> 	440	44	first vacuum
> 	20	<1	other vacuums(index is still same size)
> 
> Conclusions:
> 
> 	o  indexes never get smaller

this one, I thought, was a known?  if I remember right, Vadim changed it
so that space was reused, but index never shrunk in size ... no?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


From tgl@sss.pgh.pa.us Fri Jan 21 13:06:35 2000
1078 1079 1080 1081 1082 1083
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id OAA14618
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 14:06:33 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id OAA16501;
	Fri, 21 Jan 2000 14:06:31 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
1084 1085 1086 1087 1088 1089 1090 1091 1092 1093
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: vacuum timings 
In-reply-to: <200001211751.MAA12106@candle.pha.pa.us> 
References: <200001211751.MAA12106@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
	message dated "Fri, 21 Jan 2000 12:51:53 -0500"
Date: Fri, 21 Jan 2000 14:06:31 -0500
Message-ID: <16498.948481591@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
1094
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Conclusions:
> 	o  indexes never get smaller

Which we knew...

> 	o  drop/recreate index is slower than vacuum of indexes

Quite a few people have reported finding the opposite in practice.
You should probably try vacuuming after deleting or updating some
fraction of the rows, rather than just the all or none cases.

			regards, tom lane

From dms@wplus.net Fri Jan 21 13:51:27 2000
1111 1112 1113
Received: from relay.wplus.net (relay.wplus.net [195.131.52.179])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id OAA15623
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 14:51:24 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
1114
X-Real-To: pgman@candle.pha.pa.us
1115 1116 1117
Received: from wplus.net (ppdms.dialup.wplus.net [195.131.52.71])
	by relay.wplus.net (8.9.1/8.9.1/wplus.2) with ESMTP id WAA89451;
	Fri, 21 Jan 2000 22:46:19 +0300 (MSK)
Bruce Momjian's avatar
Bruce Momjian committed
1118 1119 1120 1121 1122
Message-ID: <3888B822.28F79A1F@wplus.net>
Date: Fri, 21 Jan 2000 22:48:50 +0300
From: Dmitry Samersoff <dms@wplus.net>
X-Mailer: Mozilla 4.7 [en] (WinNT; I)
X-Accept-Language: ru,en
1123
MIME-Version: 1.0
Bruce Momjian's avatar
Bruce Momjian committed
1124 1125 1126 1127 1128
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Bruce Momjian <pgman@candle.pha.pa.us>,
        PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: vacuum timings
References: <200001211751.MAA12106@candle.pha.pa.us> <16498.948481591@sss.pgh.pa.us>
1129 1130 1131
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit
Status: ORr
Bruce Momjian's avatar
Bruce Momjian committed
1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157

Tom Lane wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Conclusions:
> >       o  indexes never get smaller
> 
> Which we knew...
> 
> >       o  drop/recreate index is slower than vacuum of indexes
> 
> Quite a few people have reported finding the opposite in practice.

I'm one of them. On 1,5 GB table with three indices it about twice
slowly.
Probably becouse vacuuming indices brakes system cache policy.
(FreeBSD 3.3)



-- 
Dmitry Samersoff, DM\S
dms@wplus.net http://devnull.wplus.net
* there will come soft rains

From owner-pgsql-hackers@hub.org Fri Jan 21 14:04:08 2000
1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176
Received: from hub.org (hub.org [216.126.84.1])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id PAA16140
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 15:04:06 -0500 (EST)
Received: from localhost (majordom@localhost)
	by hub.org (8.9.3/8.9.3) with SMTP id OAA34808;
	Fri, 21 Jan 2000 14:59:30 -0500 (EST)
	(envelope-from owner-pgsql-hackers)
Received: by hub.org (bulk_mailer v1.5); Fri, 21 Jan 2000 14:57:48 -0500
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id OAA34320
	for pgsql-hackers-outgoing; Fri, 21 Jan 2000 14:56:50 -0500 (EST)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
	by hub.org (8.9.3/8.9.3) with ESMTP id OAA34255
	for <pgsql-hackers@postgresql.org>; Fri, 21 Jan 2000 14:56:18 -0500 (EST)
	(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.9.0/8.9.0) id OAA15772;
	Fri, 21 Jan 2000 14:54:22 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
1177 1178 1179 1180 1181 1182 1183 1184 1185 1186
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001211954.OAA15772@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: vacuum timings
In-Reply-To: <3888B822.28F79A1F@wplus.net> from Dmitry Samersoff at "Jan 21,
	2000 10:48:50 pm"
To: Dmitry Samersoff <dms@wplus.net>
Date: Fri, 21 Jan 2000 14:54:21 -0500 (EST)
CC: Tom Lane <tgl@sss.pgh.pa.us>,
        PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL66 (25)]
1187 1188 1189
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Bruce Momjian's avatar
Bruce Momjian committed
1190
Sender: owner-pgsql-hackers@postgreSQL.org
1191
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229

[Charset koi8-r unsupported, filtering to ASCII...]
> Tom Lane wrote:
> > 
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Conclusions:
> > >       o  indexes never get smaller
> > 
> > Which we knew...
> > 
> > >       o  drop/recreate index is slower than vacuum of indexes
> > 
> > Quite a few people have reported finding the opposite in practice.
> 
> I'm one of them. On 1,5 GB table with three indices it about twice
> slowly.
> Probably becouse vacuuming indices brakes system cache policy.
> (FreeBSD 3.3)

OK, we are researching what things can be done to improve this.  We are
toying with:

	lock table for less duration, or read lock
	creating another copy of heap/indexes, and rename() over old files
	improving heap vacuum speed
	improving index vacuum speed
	moving analyze out of vacuum
	

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

************

From scrappy@hub.org Fri Jan 21 14:12:16 2000
1230 1231 1232 1233 1234 1235 1236
Received: from thelab.hub.org (nat200.60.mpoweredpc.net [142.177.200.60])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id PAA16521
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 15:12:13 -0500 (EST)
Received: from localhost (scrappy@localhost)
	by thelab.hub.org (8.9.3/8.9.1) with ESMTP id QAA69039;
	Fri, 21 Jan 2000 16:12:25 -0400 (AST)
	(envelope-from scrappy@hub.org)
Bruce Momjian's avatar
Bruce Momjian committed
1237 1238 1239 1240 1241 1242 1243 1244 1245
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Fri, 21 Jan 2000 16:12:25 -0400 (AST)
From: The Hermit Hacker <scrappy@hub.org>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Dmitry Samersoff <dms@wplus.net>, Tom Lane <tgl@sss.pgh.pa.us>,
        PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: vacuum timings
In-Reply-To: <200001211954.OAA15772@candle.pha.pa.us>
Message-ID: <Pine.BSF.4.21.0001211607080.23487-100000@thelab.hub.org>
1246 1247 1248
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309

On Fri, 21 Jan 2000, Bruce Momjian wrote:

> [Charset koi8-r unsupported, filtering to ASCII...]
> > Tom Lane wrote:
> > > 
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > Conclusions:
> > > >       o  indexes never get smaller
> > > 
> > > Which we knew...
> > > 
> > > >       o  drop/recreate index is slower than vacuum of indexes
> > > 
> > > Quite a few people have reported finding the opposite in practice.
> > 
> > I'm one of them. On 1,5 GB table with three indices it about twice
> > slowly.
> > Probably becouse vacuuming indices brakes system cache policy.
> > (FreeBSD 3.3)
> 
> OK, we are researching what things can be done to improve this.  We are
> toying with:
> 
> 	lock table for less duration, or read lock

if there is some way that we can work around the bug that I believe Tom
found with removing the lock altogether (ie. makig use of MVCC), I think
that would be the best option ... if not possible, at least get things
down to a table lock vs the whole database?

a good example is the udmsearch that we are using on the site ... it uses
multiple tables to store the dictionary, each representing words of X size
... if I'm searching on a 4 letter word, and the whole database is locked
while it is working on the dictionary with 8 letter words, I'm sitting
there idle ... at least if we only locked the 8 letter table, everyone not
doing 8 letter searches can go on their merry way ...

Slightly longer vacuum's, IMHO, are acceptable if, to the end users, its
as transparent as possible ... locking per table would be slightly slower,
I think, because once a table is finished, the next table would need to
have an exclusive lock put on it before starting, so you'd have to
possibly wait for that...?

> 	creating another copy of heap/indexes, and rename() over old files

sounds to me like introducing a large potential for error here ...

> 	moving analyze out of vacuum

I think that should be done anyway ... if we ever get to the point that
we're able to re-use rows in tables, then that would eliminate the
immediate requirement for vacuum, but still retain a requirement for a
periodic analyze ... no?

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


From tgl@sss.pgh.pa.us Fri Jan 21 16:02:07 2000
1310 1311 1312 1313 1314 1315
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id RAA20290
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 17:02:06 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id RAA09697;
	Fri, 21 Jan 2000 17:02:06 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326
To: The Hermit Hacker <scrappy@hub.org>
cc: Bruce Momjian <pgman@candle.pha.pa.us>,
        PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Re: vacuum timings 
In-reply-to: <Pine.BSF.4.21.0001211607080.23487-100000@thelab.hub.org> 
References: <Pine.BSF.4.21.0001211607080.23487-100000@thelab.hub.org>
Comments: In-reply-to The Hermit Hacker <scrappy@hub.org>
	message dated "Fri, 21 Jan 2000 16:12:25 -0400"
Date: Fri, 21 Jan 2000 17:02:06 -0500
Message-ID: <9694.948492126@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
1327
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356

The Hermit Hacker <scrappy@hub.org> writes:
>> lock table for less duration, or read lock

> if there is some way that we can work around the bug that I believe Tom
> found with removing the lock altogether (ie. makig use of MVCC), I think
> that would be the best option ... if not possible, at least get things
> down to a table lock vs the whole database?

Huh?  VACUUM only requires an exclusive lock on the table it is
currently vacuuming; there's no database-wide lock.

Even a single-table exclusive lock is bad, of course, if it's a large
table that's critical to a 24x7 application.  Bruce was talking about
the possibility of having VACUUM get just a write lock on the table;
other backends could still read it, but not write it, during the vacuum
process.  That'd be a considerable step forward for 24x7 applications,
I think.

It looks like that could be done if we rewrote the table as a new file
(instead of compacting-in-place), but there's a problem when it comes
time to rename the new files into place.  At that point you'd need to
get an exclusive lock to ensure all the readers are out of the table too
--- and upgrading from a plain lock to an exclusive lock is a well-known
recipe for deadlocks.  Not sure if this can be solved.

			regards, tom lane

From tgl@sss.pgh.pa.us Fri Jan 21 22:50:34 2000
1357 1358 1359 1360 1361 1362
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id XAA01657
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 23:50:28 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id XAA19681;
	Fri, 21 Jan 2000 23:50:13 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
1363 1364 1365 1366 1367 1368 1369 1370 1371 1372
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: vacuum timings 
In-reply-to: <200001211751.MAA12106@candle.pha.pa.us> 
References: <200001211751.MAA12106@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
	message dated "Fri, 21 Jan 2000 12:51:53 -0500"
Date: Fri, 21 Jan 2000 23:50:13 -0500
Message-ID: <19678.948516613@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
1373
Status: ORr
Bruce Momjian's avatar
Bruce Momjian committed
1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Conclusions:
> 	o  drop/recreate index is slower than vacuum of indexes

BTW, I did some profiling of CREATE INDEX this evening (quite
unintentionally actually; I was interested in COPY IN, but the pg_dump
script I used as driver happened to create some indexes too).  I was
startled to discover that 60% of the runtime of CREATE INDEX is spent in
_bt_invokestrat (which is called from tuplesort.c's comparetup_index,
and exists only to figure out which specific comparison routine to call).
Of this, a whopping 4% was spent in the useful subroutine, int4gt.  All
the rest went into lookup and validation checks that by rights should be
done once per index creation, not once per comparison.

In short: a fairly straightforward bit of optimization will eliminate
circa 50% of the CPU time consumed by CREATE INDEX.  All we need is to
figure out where to cache the lookup results.  The optimization would
improve insertions and lookups in indexes, as well, if we can cache
the lookup results in those scenarios.

This was for a table small enough that tuplesort.c could do the sort
entirely in memory, so I'm sure the gains would be smaller for a large
table that requires a disk-based sort.  Still, it seems worth looking
into...

			regards, tom lane

From owner-pgsql-hackers@hub.org Sat Jan 22 02:31:03 2000
1403 1404 1405
Received: from renoir.op.net (root@renoir.op.net [207.29.195.4])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id DAA06743
	for <pgman@candle.pha.pa.us>; Sat, 22 Jan 2000 03:31:02 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
1406
Received: from hub.org (hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.3 $) with ESMTP id DAA07529 for <pgman@candle.pha.pa.us>; Sat, 22 Jan 2000 03:25:13 -0500 (EST)
1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422
Received: from localhost (majordom@localhost)
	by hub.org (8.9.3/8.9.3) with SMTP id DAA31900;
	Sat, 22 Jan 2000 03:19:53 -0500 (EST)
	(envelope-from owner-pgsql-hackers)
Received: by hub.org (bulk_mailer v1.5); Sat, 22 Jan 2000 03:17:56 -0500
Received: (from majordom@localhost)
	by hub.org (8.9.3/8.9.3) id DAA31715
	for pgsql-hackers-outgoing; Sat, 22 Jan 2000 03:16:58 -0500 (EST)
	(envelope-from owner-pgsql-hackers@postgreSQL.org)
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
	by hub.org (8.9.3/8.9.3) with ESMTP id DAA31647
	for <pgsql-hackers@postgresql.org>; Sat, 22 Jan 2000 03:16:26 -0500 (EST)
	(envelope-from Inoue@tpf.co.jp)
Received: from mcadnote1 (ppm114.noc.fukui.nsk.ne.jp [210.161.188.33])
          by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
   id RAA04754; Sat, 22 Jan 2000 17:14:43 +0900
Bruce Momjian's avatar
Bruce Momjian committed
1423 1424 1425 1426 1427 1428
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Tom Lane" <tgl@sss.pgh.pa.us>, "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "PostgreSQL-development" <pgsql-hackers@postgresql.org>
Subject: RE: [HACKERS] Re: vacuum timings 
Date: Sat, 22 Jan 2000 17:15:37 +0900
Message-ID: <NDBBIJLOILGIKBGDINDFIEEACCAA.Inoue@tpf.co.jp>
1429 1430 1431 1432
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-2022-jp"
Content-Transfer-Encoding: 7bit
Bruce Momjian's avatar
Bruce Momjian committed
1433 1434 1435 1436 1437 1438 1439
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
In-Reply-To: <16498.948481591@sss.pgh.pa.us>
Importance: Normal
Sender: owner-pgsql-hackers@postgresql.org
1440
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
1441

1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472
> -----Original Message-----
> From: owner-pgsql-hackers@postgresql.org
> [mailto:owner-pgsql-hackers@postgresql.org]On Behalf Of Tom Lane
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Conclusions:
> > 	o  indexes never get smaller
> 
> Which we knew...
> 
> > 	o  drop/recreate index is slower than vacuum of indexes
> 
> Quite a few people have reported finding the opposite in practice.
> You should probably try vacuuming after deleting or updating some
> fraction of the rows, rather than just the all or none cases.
>

Vacuum after delelting all rows isn't a worst case.
There's no moving in that case and vacuum doesn't need to call
index_insert() corresponding to the moving of heap tuples.

Vacuum after deleting half of rows may be one of the worst case.
In this case,index_delete() is called as many times as 'delete all'
case and expensive index_insert() is called for moved_in tuples.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp 

************
Bruce Momjian's avatar
Bruce Momjian committed
1473 1474

From tgl@sss.pgh.pa.us Sat Jan 22 10:31:02 2000
1475 1476 1477
Received: from renoir.op.net (root@renoir.op.net [207.29.195.4])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id LAA20882
	for <pgman@candle.pha.pa.us>; Sat, 22 Jan 2000 11:31:00 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
1478
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by renoir.op.net (o1/$Revision: 1.3 $) with ESMTP id LAA26612 for <pgman@candle.pha.pa.us>; Sat, 22 Jan 2000 11:12:44 -0500 (EST)
1479 1480 1481
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA20569;
	Sat, 22 Jan 2000 11:11:26 -0500 (EST)
Bruce Momjian's avatar
Bruce Momjian committed
1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492
To: "Hiroshi Inoue" <Inoue@tpf.co.jp>
cc: "Bruce Momjian" <pgman@candle.pha.pa.us>,
        "PostgreSQL-development" <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Re: vacuum timings 
In-reply-to: <NDBBIJLOILGIKBGDINDFIEEACCAA.Inoue@tpf.co.jp> 
References: <NDBBIJLOILGIKBGDINDFIEEACCAA.Inoue@tpf.co.jp>
Comments: In-reply-to "Hiroshi Inoue" <Inoue@tpf.co.jp>
	message dated "Sat, 22 Jan 2000 17:15:37 +0900"
Date: Sat, 22 Jan 2000 11:11:25 -0500
Message-ID: <20566.948557485@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
1493
Status: OR
Bruce Momjian's avatar
Bruce Momjian committed
1494 1495 1496 1497 1498 1499 1500 1501

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> Vacuum after deleting half of rows may be one of the worst case.

Or equivalently, vacuum after updating all the rows.

			regards, tom lane

Bruce Momjian's avatar
Bruce Momjian committed
1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541
From tgl@sss.pgh.pa.us Thu Jan 20 23:51:49 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
	by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id AAA13919
	for <pgman@candle.pha.pa.us>; Fri, 21 Jan 2000 00:51:47 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id AAA03644;
	Fri, 21 Jan 2000 00:51:51 -0500 (EST)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: vacuum timings 
In-reply-to: <200001210543.AAA13592@candle.pha.pa.us> 
References: <200001210543.AAA13592@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
	message dated "Fri, 21 Jan 2000 00:43:49 -0500"
Date: Fri, 21 Jan 2000 00:51:51 -0500
Message-ID: <3641.948433911@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
Status: ORr

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I loaded 10,000,000 rows into CREATE TABLE test (x INTEGER);  Table is
> 400MB and index is 160MB.

> With index on the single in4 column, I got:
> 	 78 seconds for a vacuum
> 	121 seconds for vacuum after deleting a single row
> 	662 seconds for vacuum after deleting the entire table

> With no index, I got:
> 	 43 seconds for a vacuum
> 	 43 seconds for vacuum after deleting a single row
> 	 43 seconds for vacuum after deleting the entire table

> I find this quite interesting.

How long does it take to create the index on your setup --- ie,
if vacuum did a drop/create index, would it be competitive?

			regards, tom lane