| Lists: | pgsql-novice |
|---|
| From: | Michael Guerin <guerin(at)rentec(dot)com> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | estimated row count way off. |
| Date: | 2004-04-07 02:11:51 |
| Message-ID: | 40736367.3080107@rentec.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-novice |
I'm trying to understand why the row count is way off, setting the statistics_target higher seems to make it worse.
summary:
INFO: "timeseries": found 0 removable, 29,394,070 nonremovable row versions in 7480085 pages
stats_target set to 100 ->
INFO: "timeseries": 7480085 pages, 30000 rows sampled, ***154,958,639*** estimated total rows
stats_target set to 500 ->
INFO: "timeseries": 7485447 pages, 150000 rows sampled, ***253,679,871*** estimated total rows
The table is very active, every day about 20 M rows of the data is deleted and ~ 20M rows are inserted.
--details of one of the vacuum's
statistics_target = 100
INFO: vacuuming "public.timeseries"
INFO: index "timeseries_tsid" now contains ***29,394,070*** row versions in 480566 pages
DETAIL: 359684 index pages have been deleted, 20000 are currently reusable.
CPU 55.79s/8.15u sec elapsed 427.51 sec.
INFO: "timeseries": found 0 removable, 29394070 nonremovable row versions in 7480085 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 93065230 unused item pointers.
0 pages are entirely empty.
CPU 550.67s/91.75u sec elapsed 3256.11 sec.
INFO: vacuuming "pg_toast.pg_toast_1286079786"
INFO: index "pg_toast_1286079786_index" now contains 8472523 row versions in 88887 pages
DETAIL: 55839 index pages have been deleted, 20000 are currently reusable.
CPU 14.40s/1.99u sec elapsed 72.61 sec.
INFO: "pg_toast_1286079786": found 0 removable, 8472523 nonremovable row versions in 4924161 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 19416055 unused item pointers.
0 pages are entirely empty.
CPU 358.70s/54.13u sec elapsed 2058.52 sec.
INFO: analyzing "public.timeseries"
INFO: "timeseries": 7480085 pages, 30000 rows sampled, ***154,958,639*** estimated total rows
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Michael Guerin <guerin(at)rentec(dot)com> |
| Cc: | pgsql-novice(at)postgresql(dot)org |
| Subject: | Re: estimated row count way off. |
| Date: | 2004-04-07 05:48:03 |
| Message-ID: | 5939.1081316883@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-novice |
Michael Guerin <guerin(at)rentec(dot)com> writes:
> I'm trying to understand why the row count is way off, setting the statistics_target higher seems to make it worse.
This is not a row count estimate, it's just ANALYZE telling you how many
rows it sampled to prepare the pg_stats statistics.
regards, tom lane