What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum

[Fd Habash]

I have been able to locate four google search results with the same inquiry. 
What’ve been able to understand is …

1. If auto-vaccum is working as expected, stats collector does not nullify 
these values as part of a startup sequence or regular Maitenance. If a relation 
gets auto[vacuumed|analyzed], the timestamps should remain. 
2. A database engine crash or restart with ‘immediate’ option will cause the 
timestamps to nullify. 
3. Table never qualified for vacuuming based on auto-vacuum settings. 

I can rule out all three scenarios above, but I still see null values. What 
else could be at play here? 




Thank you

Re: What is pg_stat_user_tables Showing NULL for last_autoanalyze & last_autovacuum

[Justin Pryzby]

On Wed, Feb 27, 2019 at 09:47:13AM -0500, Fd Habash wrote:
> I have been able to locate four google search results with the same inquiry. 
> What’ve been able to understand is …
> 
> 1. If auto-vaccum is working as expected, stats collector does not nullify 
> these values as part of a startup sequence or regular Maitenance. If a 
> relation gets auto[vacuumed|analyzed], the timestamps should remain.
> 2. A database engine crash or restart with ‘immediate’ option will cause the 
> timestamps to nullify. 
> 3. Table never qualified for vacuuming based on auto-vacuum settings. 

Can you give an example ?

If it's an empty inheritence parent (relkind=r), then it won't trigger
autovacuum/analyze thresholds (but you should analyze it manually).

Note that relkind=p "partitioned" tables don't have entries at all.
https://www.postgresql.org/message-id/flat/20180503141430.GA28019%40telsasoft.com

If it's never DELETEd from, then it won't trigger autovacuum (but may trigger
autoanalyze).

Justin

Re: Query slow for new participants

[supp...@mekong.be]

Hello All,



Thank you very much for your help. You have really helped me out!

The query is now as fast as the others.



The indexes ix_companyarticledb_article and ix_companyarticledb_company are
removed.

The parameter for default_statistics_target was set to 1000

ANALYZE was performed on the database



I am so happy this worked out.

The pg_buffercache extension is now installed, and I will be working with
it the coming days to improve my settings.

First time I ran the query (evening, not high peak usage)



SELECT c.relname, count(*) AS buffers

 FROM pg_buffercache b INNER JOIN pg_class c

 ON b.relfilenode = pg_relation_filenode(c.oid) AND

b.reldatabase IN (0, (SELECT oid FROM pg_database

  WHERE datname = current_database()))

 GROUP BY c.relname

 ORDER BY 2 DESC

 LIMIT 10;



"pk_pricedb""1479655"

"companyarticledb"  "1378549"

"articledb" "780821"

"pricedb"   "280771"

"descriptionindex""138514"

"ix_pricedb"  "122833"

"pk_articledb"  "47290"

"EnabledIndex" "29958"

"strippedmanufacturernumberindex"   "25604"

"strippedcataloguenumberindex"  "24360"





How can I see if the whole DB is kept in RAM?

How to define the best setting for work_mem ?



Thanks for your help!



Regards,

Kim















Op di 26 feb. 2019 om 20:08 schreef Michael Lewis :

>
> Indexes:
>> "pk_pricedb" PRIMARY KEY, btree (companyid, articleid)
>> "EnabledIndex" btree (enabled)
>> "ix_companyarticledb_article" btree (articleid)
>> "ix_companyarticledb_company" btree (companyid)
>>
>
> I'd say drop ix_companyarticledb_company since pk_pricedb can be used
> instead even if other queries are only on companyid field, and it will be
> faster for this case certainly since it targets the row you want directly
> from the index without the *"Rows Removed by Filter: 2674361"*
>
> I doubt the default_statistics_target = 100 default is doing you any
> favors. You may want to try increasing that to 500 or 1000 if you can
> afford a small increase in planning cost and more storage for the bigger
> sampling of stats.
>


-- 
Met vriendelijke groeten,