Search Postgresql Archives

How estimated rows is running ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

I have a database with one table with about 22 millions records.
I have a script every day wich insert about 200 000 records ...
Sometime my script takes 4 hours to insert the data, sometime 1 hour.

Then I have a question does for inserting data PostgreSQL use somewhere the 
number of estimated rows ... ? For me no ... but ...

For example ...

mybases=# ANALYZE VERBOSE my_table;
INFO:  analyzing "public.my_table"
INFO:  "my_table": 434342 pages, 30000 rows sampled, 22585030 estimated total 
rows
ANALYZE
Time: 173317.410 ms

I insert 200 000 data ... 4 hours ...

Then I get back to the same situation (other server same database in same 
situation before the insert ... (same analyze as before)) ... I do the same 
Analyze ... then I do a vacuum analyze like this after ... Why the estimated 
total rows of the first index is not in correlation with the analyze done 
before ? Why the total at the end of the vacuum is also different ? And why 
after the vacuum analyze the insert takes 1 hour ??

mybases=# VACUUM VERBOSE ANALYZE my_table;
INFO:  vacuuming "public.my_table"
INFO:  index "ix_my_table_compte" now contains 21897465 row versions in 113659 
pages
DETAIL:  270724 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 12.23s/20.53u sec elapsed 824.63 sec.
INFO:  index "ix_my_table_idcontract" now contains 21897465 row versions in 
65647 pages
DETAIL:  270724 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 4.32s/14.52u sec elapsed 337.45 sec.
INFO:  index "ix_my_table_arrete_week" now contains 21897465 row versions in 
87723 pages
DETAIL:  270724 index row versions were removed.
2780 index pages have been deleted, 1689 are currently reusable.
CPU 8.68s/11.40u sec elapsed 754.39 sec.
INFO:  "my_table": removed 270724 row versions in 5209 pages
DETAIL:  CPU 0.27s/0.74u sec elapsed 15.40 sec.
INFO:  "my_table": found 270724 removable, 21897465 nonremovable row versions 
in 434342 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 416707 unused item pointers.
0 pages are entirely empty.
CPU 44.14s/52.89u sec elapsed 2247.39 sec.
INFO:  "my_table": truncated 434342 to 431553 pages
DETAIL:  CPU 0.06s/0.03u sec elapsed 0.08 sec.
INFO:  vacuuming "pg_toast.pg_toast_917442"
INFO:  index "pg_toast_917442_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_917442": found 0 removable, 0 nonremovable row versions in 0 
pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO:  analyzing "public.my_table"
INFO:  "my_table": 431553 pages, 30000 rows sampled, 22440007 estimated total 
rows
VACUUM
Time: 2493147.338 ms

Thanks for you replies,
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux