Postgres Pro
Downloads
Home   >   mailing lists

Thread: Count not updated with VACUUM ANALYZE

Count not updated with VACUUM ANALYZE

From
Melvin Davidson
Date:
<div class="Section1"><p class="MsoNormal"><b><span style="font-family:"Arial","sans-serif";color:#1F497D">PgAdmin III
1.8.2</span></b><b><spanstyle="font-family:"Arial","sans-serif""></span></b><p class="MsoNormal"><span
style="color:#1F497D"> </span><pclass="MsoNormal"><span style="font-family:"Arial","sans-serif"">I have statistics
enabledon my server. However, when I do a VACUUM ANALYZE on my database, the count [ Rows (counted) ] is set to “not
counted”.</span><pclass="MsoNormal"><span style="font-family:"Arial","sans-serif""> </span><p class="MsoNormal"><span
style="font-family:"Arial","sans-serif"">NowI can right click on the individual tables and select “Count” to manually
updateit, but shouldn’t the VACUUM have done that?</span><p class="MsoNormal"><span
style="font-family:"Arial","sans-serif""> </span><pclass="MsoNormal"><span
style="font-family:"Arial","sans-serif"">Also,is there an documented (or undocumented) function I can run to manually
updatethe counts.</span><p class="MsoNormal"><span style="font-family:"Arial","sans-serif""> </span><p
class="MsoNormal"><spanstyle="font-family:"Arial","sans-serif"">Thanks in advance,</span><p class="MsoNormal"><span
style="font-family:"Arial","sans-serif"">MelvinDavidson</span></div>

Re: Count not updated with VACUUM ANALYZE

From
Guillaume Lelarge
Date:
Melvin Davidson a écrit :
> *PgAdmin III 1.8.2***
> 
>  
> 
> I have statistics enabled on my server. However, when I do a VACUUM 
> ANALYZE on my database, the count [ Rows (counted) ] is set to “not 
> counted”.
> 
>  
> 
> Now I can right click on the individual tables and select “Count” to 
> manually update it, but shouldn’t the VACUUM have done that?
> 

Nope. ANALYZE collects some statistics (for example an estimated number 
of rows on each table... this number is available in pgAdmin under the 
exact number of rows).

The Count item of the contextual menu does a count(*) on the table. So, 
it gives you the exact number of rows.

> Also, is there an documented (or undocumented) function I can run to 
> manually update the counts.
> 

No. You really need to select the menu item "Count" to have the exact 
number of rows.


-- 
Guillaume. http://www.postgresqlfr.org http://dalibo.com

Re: Count not updated with VACUUM ANALYZE

From
"Stephen Denne"
Date:
> Melvin Davidson a écrit :
> > *PgAdmin III 1.8.2***
> >
> >
> >
> > I have statistics enabled on my server. However, when I do a VACUUM
> > ANALYZE on my database, the count [ Rows (counted) ] is set to “not
> > counted”.
> >
> >
> >
> > Now I can right click on the individual tables and select
> “Count” to
> > manually update it, but shouldn’t the VACUUM have done that?
> >
>
> Nope. ANALYZE collects some statistics (for example an
> estimated number
> of rows on each table... this number is available in pgAdmin
> under the
> exact number of rows).

I think Guillaume meant "estimated" rather than "exact" here.

> The Count item of the contextual menu does a count(*) on the
> table. So,
> it gives you the exact number of rows.
>
> > Also, is there an documented (or undocumented) function I
> can run to
> > manually update the counts.
> >
>
> No. You really need to select the menu item "Count" to have the exact
> number of rows.

There is an option under the Query tab named
"Count rows if estimated less than"
which you could change to a larger number.

I have mine set to 0 as I don't like the delay, and the estimates fill my needs.

Regards,
Stephen Denne.

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any
attachmentsis confidential and may be subject to legal privilege.  If it is not intended for you please advise by reply
immediately,destroy it and do not copy, disclose or use it in any way. 
__________________________________________________________________ This email has been scanned by the DMZGlobal
BusinessQuality             Electronic Messaging Suite. 
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__________________________________________________________________

Re: Count not updated with VACUUM ANALYZE

From
Guillaume Lelarge
Date:
Stephen Denne a écrit :
>> Melvin Davidson a écrit :
>>> *PgAdmin III 1.8.2***
>>>
>>> I have statistics enabled on my server. However, when I do a VACUUM 
>>> ANALYZE on my database, the count [ Rows (counted) ] is set to “not 
>>> counted”.
>>>
>>> Now I can right click on the individual tables and select 
>> “Count” to 
>>> manually update it, but shouldn’t the VACUUM have done that?
>>>
>> Nope. ANALYZE collects some statistics (for example an 
>> estimated number 
>> of rows on each table... this number is available in pgAdmin 
>> under the 
>> exact number of rows).
> 
> I think Guillaume meant "estimated" rather than "exact" here.
> 

Well, I really meant "exact". There's one line for the exact number of 
rows (which needs some time to get), and there's one line for the 
estimated number (the one that ANALYZE gets).

>> The Count item of the contextual menu does a count(*) on the 
>> table. So, 
>> it gives you the exact number of rows.
>>
>>> Also, is there an documented (or undocumented) function I 
>> can run to 
>>> manually update the counts.
>>>
>> No. You really need to select the menu item "Count" to have the exact 
>> number of rows.
> 
> There is an option under the Query tab named
> "Count rows if estimated less than" 
> which you could change to a larger number.
> 

Oh yes, you're right about this option. I completely forgot it, thanks.

> I have mine set to 0 as I don't like the delay, and the estimates fill my needs.
> 


-- 
Guillaume. http://www.postgresqlfr.org http://dalibo.com