pg_restore weirdness

Поиск
Список
Период
Сортировка
От Jie Liang
Тема pg_restore weirdness
Дата
Msg-id 7C760DAA511DC74B99E7D22189F786F101BF214B@mail01.stbernard.com
обсуждение исходный текст
Ответы Re: pg_restore weirdness  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список pgsql-admin
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!
following is my log:

urldb=# VACUUM FULL VERBOSE ANALYZE urlinfo;
NOTICE:  --Relation urlinfo--
NOTICE:  Pages 33840: Changed 0, reaped 0, Empty 0, New 0; Tup 2718061: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 76, MaxLen 1992; Re-using: Free/Avail.
Space 1365860/459512; EndEmpty/Avail. Pages 0/5329.
        CPU 0.66s/0.22u sec elapsed 0.89 sec.
NOTICE:  Index urlinfo_pkey: Pages 9063; Tuples 2718061.
        CPU 0.15s/0.17u sec elapsed 0.33 sec.
NOTICE:  Index urlinfo_ukey: Pages 16869; Tuples 2718061.
        CPU 0.38s/0.12u sec elapsed 0.50 sec.
NOTICE:  Index urlinfo_on: Pages 7456; Tuples 2718061.
        CPU 0.16s/0.11u sec elapsed 0.27 sec.
NOTICE:  Index urlinfo_rt: Pages 5954; Tuples 2718061.
        CPU 0.10s/0.14u sec elapsed 0.24 sec.
NOTICE:  Rel urlinfo: Pages: 33840 --> 33835; Tuple(s) moved: 425.
        CPU 0.05s/0.13u sec elapsed 0.20 sec.
NOTICE:  Index urlinfo_pkey: Pages 9064; Tuples 2718061: Deleted 425.
        CPU 0.19s/0.18u sec elapsed 0.38 sec.
NOTICE:  Index urlinfo_ukey: Pages 16869; Tuples 2718061: Deleted 425.
        CPU 0.41s/0.17u sec elapsed 0.59 sec.
NOTICE:  Index urlinfo_on: Pages 7457; Tuples 2718061: Deleted 425.
        CPU 0.17s/0.15u sec elapsed 0.34 sec.
NOTICE:  Index urlinfo_rt: Pages 5956; Tuples 2718061: Deleted 425.
        CPU 0.13s/0.15u sec elapsed 0.30 sec.
NOTICE:  --Relation pg_toast_57618011--
NOTICE:  Pages 1: Changed 1, reaped 0, Empty 0, New 0; Tup 4: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 827, MaxLen 2034; Re-using: Free/Avail. Space
1800/1800; EndEmpty/Avail. Pages 0/1.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Index pg_toast_57618011_idx: Pages 2; Tuples 4.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Rel pg_toast_57618011: Pages: 1 --> 1; Tuple(s) moved: 0.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Analyzing urlinfo
VACUUM
urldb=# VACUUM FULL VERBOSE ANALYZE ratings_by_serial;
NOTICE:  --Relation ratings_by_serial--
NOTICE:  Pages 20028: Changed 0, reaped 0, Empty 0, New 0; Tup 3705125: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 40, MaxLen 40; Re-using: Free/Avail. Space
643316/2452; EndEmpty/Avail. Pages 0/1.
        CPU 0.42s/0.16u sec elapsed 0.59 sec.
NOTICE:  Index rbs_unidx: Pages 10162; Tuples 3705125.
        CPU 0.24s/0.14u sec elapsed 0.38 sec.
NOTICE:  Index ratings_by_serial_pid: Pages 8115; Tuples 3705125.
        CPU 0.15s/0.17u sec elapsed 0.33 sec.
NOTICE:  Rel ratings_by_serial: Pages: 20028 --> 20028; Tuple(s) moved: 0.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Analyzing ratings_by_serial
VACUUM
urldb=# explain analyze select codestr(16482429);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0) (actual time=5602.67..5602.67
rows=1 loops=1)
Total runtime: 5602.72 msec

EXPLAIN
urldb=# explain analyze select codestr(16482429);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0) (actual time=4889.69..4889.69
rows=1 loops=1)
Total runtime: 4889.73 msec

....................
-----------------------------------------------------------------
After I dropped this function and reload it again via pg_restore:

EXPLAIN
urldb=# explain analyze select codestr(16482429);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0) (actual time=2.50..2.51 rows=1
loops=1)
Total runtime: 2.56 msec

EXPLAIN
urldb=# explain analyze select codestr(16482429);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.28..0.28 rows=1
loops=1)
Total runtime: 0.32 msec



Jie Liang

В списке pgsql-admin по дате отправления:

Предыдущее
От: "Chad R. Larson"
Дата:
Сообщение: Re: Ouverture d'une session base de
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: pg_restore weirdness