Re: pg_restore weirdness

Stephan,

Woo, you are right!!
select codestr() is using its cache before my vacuum in a same session!

Thanks!


Jie Liang

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Tuesday, August 06, 2002 10:30 AM
To: Jie Liang
Cc: 'pgsql-admin@postgresql.org'; 'pgsql-hacker@postgresql.org'
Subject: Re: [ADMIN] pg_restore weirdness



On Tue, 6 Aug 2002, Jie Liang wrote:

> I got a very weird restore problem:
> 1. I use
>    pg_dump -Fc urldb > urldb
>    on one server(FreeBSD4.3 Pg7.2.0)
> 2. I use
>    pg_restore -l urldb > archive.list
>    on another server(FreeBSD4.6 Pg7.2.1)
>    then I use
>    pg_restore -v -L archive.lst -d urldb urldb
>    on this server(Pg7.2.1)
> 3. I vacuum tables, then run my sql function which was written in plpgsql
>    very simple, I got very surprised is that it run much slower on Pg7.2.1
>    machine then Pg7.2.0 machine!
> 4. When I dropped this function and reload it again(still in binary), it
>    much much faster then before.
>
> I tested serveral times, same conclusion, I have no other backend running
> there.
> I cannot explain this, and it doesn't make any sence to me also!

You haven't shown us codestr(). Is it possible that it's a plpgsql
function that accesses a table and that it was called in your session
prior to the analyze?  If so, it may have cached a less optimal query
plan.  If that were true then quiting your session and starting a new one
would have also made it faster.