pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Guerin <guerin@rentec.com>
To: pgsql-performance@postgresql.org
Subject: Estimated rows way off
Date: Wed, 31 Mar 2004 12:17:54 -0500
Message-ID: <406AFD42.7050208@rentec.com> (raw)
In-Reply-To: <4068A4E5.4000101@rentec.com>
References: <200403291955.i2TJtblp000821@eyas.rentec.com>
	<4068A4E5.4000101@rentec.com>

*statistics  target = 100
*INFO:  index "timeseries_tsid" now contains *16,677,521* row versions 
in 145605 pages
DETAIL:  76109 index pages have been deleted, 20000 are currently reusable.
CPU 12.00s/2.83u sec elapsed 171.26 sec.
INFO:  "timeseries": found 0 removable, 16677521 nonremovable row 
versions in 1876702 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were *18,894,051* unused item pointers.
0 pages are entirely empty.
CPU 138.74s/28.96u sec elapsed 1079.43 sec.
INFO:  vacuuming "pg_toast.pg_toast_1286079786"
INFO:  index "pg_toast_1286079786_index" now contains 4846282 row 
versions in 29319 pages
DETAIL:  10590 index pages have been deleted, 10590 are currently reusable.
CPU 2.23s/0.55u sec elapsed 28.34 sec.
INFO:  "pg_toast_1286079786": found 0 removable, 4846282 nonremovable 
row versions in 1379686 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 2824978 unused item pointers.
0 pages are entirely empty.
CPU 112.92s/19.53u sec elapsed 731.99 sec.
INFO:  analyzing "public.timeseries"
INFO:  "timeseries": 1876702 pages, *30,000* rows sampled, *41,762,188* 
estimated total rows

 
setting the default statistics target higher makes the estimate worse:
*statistics target = 500*
INFO:  index "timeseries_tsid" now contains *16,953,429 *row versions in 
145605 pages
INFO:  "timeseries": 1891940 pages, *150,000* rows sampled, *64,803,483* 
estimated total rows

*statistics target = 1000 *
INFO:  index "timeseries_tsid" now contains *17,216,139* row versions in 
145605 pages
INFO:  "timeseries": 1937484 pages, *300,000* rows sampled, *68,544,295* 
estimated total rows


I'm trying to understand why the estimated row count is so off.  I'm 
assuming this is b/c we do very large deletes and we're leaving around a 
large number of almost empty pages.  Is this the reason?

Let me know if you need more info.

Thanks
Michael





>
>
>> INFO:  index "timeseries_tsid" now contains *16677521* row versions 
>> in 145605 pages
>> DETAIL:  76109 index pages have been deleted, 20000 are currently 
>> reusable.
>> CPU 12.00s/2.83u sec elapsed 171.26 sec.
>> INFO:  "timeseries": found 0 removable, 16677521 nonremovable row 
>> versions in 1876702 pages
>> DETAIL:  0 dead row versions cannot be removed yet.
>> There were 18894051 unused item pointers.
>> 0 pages are entirely empty.
>> CPU 138.74s/28.96u sec elapsed 1079.43 sec.
>> INFO:  vacuuming "pg_toast.pg_toast_1286079786"
>> INFO:  index "pg_toast_1286079786_index" now contains 4846282 row 
>> versions in 29319 pages
>> DETAIL:  10590 index pages have been deleted, 10590 are currently 
>> reusable.
>> CPU 2.23s/0.55u sec elapsed 28.34 sec.
>> INFO:  "pg_toast_1286079786": found 0 removable, 4846282 nonremovable 
>> row versions in 1379686 pages
>> DETAIL:  0 dead row versions cannot be removed yet.
>> There were 2824978 unused item pointers.
>> 0 pages are entirely empty.
>> CPU 112.92s/19.53u sec elapsed 731.99 sec.
>> INFO:  analyzing "public.timeseries"
>> INFO:  "timeseries": 1876702 pages, *30,000* rows sampled, 
>> *41,762,188* estimated total rows
>>
>>  
>>
>
> setting the default statistics target higher made the estimate worse:  
> (changed from 100 to 500)
> *
> statistics target = 500*
> INFO:  index "timeseries_tsid" now contains *16,953,429 *row versions 
> in 145605 pages
> INFO:  "timeseries": 1891940 pages, *150,000* rows sampled, 
> *64,803,483* estimated total rows
>
> *statistics target = 1000
> *INFO:  index "timeseries_tsid" now contains *17,216,139* row versions 
> in 145605 pages
> INFO:  "timeseries": 1937484 pages,* 300,000* rows sampled, 
> *68,544,295* estimated total rows
>
>
>
>
>
> This probably has something to do with the large deletes we do.  I'm 
> looking around to get some more info on statistics collection.
>
> -mike
>
>
>



Message-ID: <406AFD42.7050208@rentec.com>
Permalink:  ../406AFD42.7050208@rentec.com/
Also on:    postgresql.org/message-id/406AFD42.7050208@rentec.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: guerin@rentec.com
  Subject: Re: Estimated rows way off
  In-Reply-To: <406AFD42.7050208@rentec.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox