From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | judging acceptable discrepancy in row count v. estimate |
Date: | 2018-10-16 18:13:28 |
Message-ID: | 37AF5C49-8550-4738-912D-859F685DB3D4@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Should reality be half again as large as the estimated row count?
coon=# select count(*) from sui.segment;
count
----------
49,942,837 -- my commas
(1 row)
coon=# vacuum (analyse, verbose) sui.probandset;
INFO: vacuuming "sui.probandset"
INFO: scanned index "probandset_pkey" to remove 3122 row versions
DETAIL: CPU: user: 4.70 s, system: 1.45 s, elapsed: 26.97 s
INFO: scanned index "probandsetunique" to remove 3122 row versions
DETAIL: CPU: user: 5.99 s, system: 10.24 s, elapsed: 97.42 s
INFO: "probandset": removed 3122 row versions in 1951 pages
DETAIL: CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.73 s
INFO: index "probandset_pkey" now contains 33655227 row versions in 259175 pages
DETAIL: 3122 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.05 s.
INFO: index "probandsetunique" now contains 33655227 row versions in 1231894 pages
DETAIL: 3121 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.05 s.
INFO: "probandset": found 890 removable, 90624 nonremovable row versions in 4039 out of 2244646 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 288037
There were 5463 unused item pointers.
Skipped 0 pages due to buffer pins, 564917 frozen pages.
0 pages are entirely empty.
CPU: user: 10.91 s, system: 11.80 s, elapsed: 131.01 s.
INFO: vacuuming "pg_toast.pg_toast_18165"
INFO: index "pg_toast_18165_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s.
INFO: "pg_toast_18165": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 288037
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.03 s.
INFO: analyzing "sui.probandset"
INFO: "probandset": scanned 30000 of 2244646 pages, containing 448480 live rows and 0 dead rows; 30000 rows in sample, 33555961 estimated total rows
VACUUM
Time: 535436.137 ms (08:55.436)
coon=# reindex table sui.segment
coon-# ;
REINDEX
Time: 681530.451 ms (11:21.530)
coon=# select count(*) from sui.segment;
count
----------
49942837
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-10-16 19:01:12 | Re: judging acceptable discrepancy in row count v. estimate |
Previous Message | Shrikant Bhende | 2018-10-16 15:36:53 | Re: FATAL: terminating connection because protocol synchronization was lost |