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.