unsubscribe - Mailing list pgsql-sql

-----Original Message-----
From: owner-pgsql-sql-digest@hub.org
[mailto:owner-pgsql-sql-digest@hub.org]
Sent: Sunday, November 21, 1999 9:01 PM
To: pgsql-sql-digest@hub.org
Subject: pgsql-sql-digest V1 #420



pgsql-sql-digest       Sunday, November 21 1999       Volume 01 : Number 420



Index:

RE: [SQL] Deleting indexes before vacuum? 
How to optimize this simple query :-(
Re: [SQL] Deleting indexes before vacuum? 

----------------------------------------------------------------------

Date: Mon, 22 Nov 1999 10:53:28 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
Subject: RE: [SQL] Deleting indexes before vacuum? 

> -----Original Message-----
> From: owner-pgsql-sql@postgreSQL.org [mailto:owner-pgsql-sql@postgreSQL.
> org]On Behalf Of Tom Lane
> Sent: Sunday, November 21, 1999 2:01 AM
> To: Oleg Bartunov
> Cc: Matthew Hagerty; pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] Deleting indexes before vacuum?
>
>
> Oleg Bartunov <oleg@sai.msu.su> writes:
> > The question is: what's the right way to do drop index/vacuum/create ?
> > Do I need transaction ?
>
> VACUUM should not be run inside a transaction block.  (I am planning to
> make the code enforce this for 7.0, but it doesn't yet.)  So you can't
> protect the whole sequence with a transaction.  AFAICS the only real
> problem is that if you are depending on UNIQUE indexes to catch
> attempts to insert duplicate data, an insertion that got in between
> the drop and recreate wouldn't get checked.
>
> I have been toying with the notion of ripping out the existing VACUUM
> index handling code and putting in fresh code that would simply do an
> index drop and rebuild ;-).  This could happen inside VACUUM's exclusive
> lock on the table, so it'd be perfectly safe whereas doing it the manual
> way is not.  But I do not want to do this unless Vadim approves it as a
> good idea --- perhaps the existing index-vacuuming code can be fixed to
> be an even better solution than this.  I haven't looked at the code to
> understand why it's so slow or whether there's a way to make it better.
>

Shouldn't vacuum preserve consistency even in case of abort ?
Currently PostgreSQL doesn't do little in case of abort(even commit also ?).
So I think it's a strong limitation.

It's well known that rebuilding indexes is faster than insert/deleting
many index tuples.
I have thought rebuilding indexes in vacuum for half a year.
But I don't have a reasonable solution yet.

Rebuiding indexes in vacuum has a big flaw that index may vanish in
case of abort and we may have to recreate index manually.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

------------------------------

Date: Sun, 21 Nov 1999 12:12:00 +0100 (CET)
From: marten@feki.toppoint.de
Subject: How to optimize this simple query :-(

I've the follwoing two tables:

Table P3AT

AO    CHAR(15)
AT    Integer
AV    VARCHAR(80)
AB    LargeInteger


Table P3RL

RELS  CHAR(15)
RELT  CHAR(15)
SRCT  Integer
and some other columns


Indices are on: P3AT.AO, P3AT.AT, P3RL.RELS, P3RL.RELT, P3RL.SRCT 


Now I do the query:

a) SELECT AO,AT,AV FROM P3AT WHERE AO IN     (SELECT RELT FROM P3RL WHERE RELS='9#####3#####RW#' AND (SRCT=1004025))

or via

b) SELECT AO,AT,AV FROM P3AT WHERE EXISTS    (SELECT RELT FROM P3RL WHERE (RELT=AO) AND (RELS= ..) AND ...)


Both statements have the same explain result:

Seq Scan on p3at (cost=14458.84 rows=327480 width=28)SubPlan   -> Index Scan using reltrgind om p3rl (cost=2.05 rows=1
width=12)

Both statements need about 3300 milliseconds to do the job ... and return
the
three result rows ...

I gave it another sql-query and this works much better:


c) SELECT AO,AT,AV FROM P3AT,P3RL WHERE      (P3AT.AO=P3RL.RELT) AND (RELS= ...) AND (SRCT= ...)

This gives the following explain result (and the one I would have expected):

NestedLoop (cost=4.1 rows=5 width=40) -> Index Scan using relsrcind on p3rl (cost=2.05 rows=1 width=12) -> Index Scan
usingatrownind on p3at (cost=2.05 rows=327480 width=28)
 

and needs only 5 ms !

Marten

------------------------------

Date: Sun, 21 Nov 1999 22:18:24 -0500
From: Tom Lane <tgl@sss.pgh.pa.us> 
Subject: Re: [SQL] Deleting indexes before vacuum? 

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> I have been toying with the notion of ripping out the existing VACUUM
>> index handling code and putting in fresh code that would simply do an
>> index drop and rebuild ;-).

> Shouldn't vacuum preserve consistency even in case of abort ?

That would be nice, certainly, but the existing code doesn't guarantee
it either, and I don't see how we could guarantee it by any method.
If you're moving tuples around then you are going to have some
inconsistent intermediate states :-(.  However, the big problem in
that respect is an abort while vacuum is reshuffling the data in the
table itself; changing our approach to index vacuuming isn't going
to make any difference there.  Once the table is finished and vacuum
moves on to fixing the indexes, an abort would leave the index(es)
corrupt but the table data should be OK.  So the issue is how to
recover the indexes after that happens.

> Rebuiding indexes in vacuum has a big flaw that index may vanish in
> case of abort and we may have to recreate index manually.

The index won't "vanish" --- I see no reason to touch the system-table
entries for it.  The data in the index might be corrupt, but that can
happen now.

ISTM that a big advantage of the rebuild approach is that if something
does go wrong during the index-fixing phase, you can try to recover just
by doing another vacuum.  That strikes me as less "manual" than dropping
and rebuilding the indexes, which is the only available recovery path
now.  It might even work for an index on a system table...
        regards, tom lane

------------------------------

End of pgsql-sql-digest V1 #420
*******************************


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