Thread: Re: Almost infinite query -> Different Query Plan when changing where clause value

lionel duboeuf  wrote:

> I stopped postgresql
> I started postgresql (and as default autovacuum daemon)
> I restored the databases (need to restore 4 databases)
> It seems that after database 1 have been restored, autovacumm
> started on it and has been stopped while restoring database 2.

> Does this kind of error can lead to the query problem ?

I believe it can.  I assume you're talking about restoring something
which was dumped by pg_dump or pg_dumpall?  If so, you don't have
statistics right after the restore.  Autovacuum will try to build
them for you, but that will take a while.  There are also a couple
other hidden performance issues after a restore:

(1)  None of the hint bits are set, so the first read to each tuple
will cause it to be written again with hint bits, so you will have
mysterious bursts of write activity during read-only queries until
all tuples have been read.

(2)  All tuples in the database will have the same, or nearly the
same, xmin (creation transaction number), so at some indeterminate
time in the future, vacuums will trigger for all of your tables at
the same time, probably in the middle of heavy activity.

For all of the above reasons, I use a special postgresql.conf,
optimized for bulk loads, to restore from dumps (or to pipe from
pg_dump to psql).  I turn off autovacuum during the restore, and then
do an explicit VACUUM FREEZE ANALYZE before I consider the restore
complete.  Then I restart with a "normal" postgresql.conf file.  Some
might consider this extreme, but it works for me, an prevents the
kind of problems which generated your post.

-Kevin

Thank you Kevin and Scott for all the explanations. ;-)

regards
Lionel

lionel duboeuf a écrit :
> Thank you Kevin and Scott for all the explanations. ;-)
>
> regards
> Lionel
>
>
>
Sorry for forgetting Jorge also.

regards.
All this ,encourage me to know more about database management.