Thread: reindex vs. drop index , create index

reindex vs. drop index , create index

From
"Henrik Steffen"
Date:
hello all,

a few weeks ago I turned to REINDEXing my tables instead of DROPping and
reCREATEing them.

now, however, I again have problems when VACUUMing or REINDEXing these
tables. Postgres states that the connection has been lost etc.

the only thing helping is to DROP and CREATE the index again....

I will now again have a cronjob that at least once a week will
DROP and CREATE all indexes.

isn't this a bit poor of postgresql??

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------


Re: reindex vs. drop index , create index

From
Neil Conway
Date:
On Thu, 2003-02-20 at 12:03, Henrik Steffen wrote:
> a few weeks ago I turned to REINDEXing my tables instead of DROPping and
> reCREATEing them.
>
> now, however, I again have problems when VACUUMing or REINDEXing these
> tables. Postgres states that the connection has been lost etc.

Can you provide the exact error message, as well as the version of
PostgreSQL you're using and any relevant OS version details.

Also, it sounds like PostgreSQL might be dumping core. If that's the
case, providing a backtrace from the core file (using gdb) would be
helpful. It would be especially helpful if you could recompile
PostgreSQL with debugging symbols (./configure --enable-debug) and get a
backtrace after that.

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC




Re: reindex vs. drop index , create index

From
"Henrik Steffen"
Date:
hello,

I am currently using 7.3.1 -- planning to upgrade soon....

sorry, I didn't save nor log the exact error message.

using a SuSE Linux with kernel 2.4.20


--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Neil Conway" <neilc@samurai.com>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: Friday, February 21, 2003 7:40 AM
Subject: Re: [GENERAL] reindex vs. drop index , create index


> On Thu, 2003-02-20 at 12:03, Henrik Steffen wrote:
> > a few weeks ago I turned to REINDEXing my tables instead of DROPping and
> > reCREATEing them.
> >
> > now, however, I again have problems when VACUUMing or REINDEXing these
> > tables. Postgres states that the connection has been lost etc.
>
> Can you provide the exact error message, as well as the version of
> PostgreSQL you're using and any relevant OS version details.
>
> Also, it sounds like PostgreSQL might be dumping core. If that's the
> case, providing a backtrace from the core file (using gdb) would be
> helpful. It would be especially helpful if you could recompile
> PostgreSQL with debugging symbols (./configure --enable-debug) and get a
> backtrace after that.
>
> Cheers,
>
> Neil
> --
> Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
>
>
>


Re: reindex vs. drop index , create index

From
Neil Conway
Date:
On Fri, 2003-02-21 at 10:46, Henrik Steffen wrote:
> sorry, I didn't save nor log the exact error message.

I can't see a way to help you, in that case.

If you do find some means to reproduce the error or get a backtrace,
please let us know.

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC




Re: reindex vs. drop index , create index

From
Neil Conway
Date:
On Fri, 2003-02-28 at 17:23, Francisco J Reyes wrote:
> Coming late to the thread..
> Does compiling with --enable-debug causes any significant performance
> impact?

I don't think so -- there will be a significantly larger on-disk
footprint for the PostgreSQL executables, though.

Cheers,

Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC




Re: reindex vs. drop index , create index

From
Francisco J Reyes
Date:
On 21 Feb 2003, Neil Conway wrote:

> Also, it sounds like PostgreSQL might be dumping core. If that's the
> case, providing a backtrace from the core file (using gdb) would be
> helpful. It would be especially helpful if you could recompile
> PostgreSQL with debugging symbols (./configure --enable-debug) and get a
> backtrace after that.

Coming late to the thread..
Does compiling with --enable-debug causes any significant performance
impact?


Re: reindex vs. drop index , create index

From
Tom Lane
Date:
Francisco J Reyes <fran@natserv.net> writes:
> Does compiling with --enable-debug causes any significant performance
> impact?

If you're using gcc it's claimed to make no difference whatever.

On other compilers, --enable-debug often turns off optimization.

            regards, tom lane