Re: How to deal with analyze gathering irrelevant stats

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Jan 11, 2021 at 04:50:12PM +0100, Rémi Chatenay wrote:
> We are experiencing some slow queries due to the query planner using an
> incorrect index. It is using an unoptimized index because the stats are
> computed during the night when the data is not the same as during the day.
> 
> CREATE INDEX index_conversations_on_user_id_and_status ON
> public.conversations USING btree (user_id, status);
> 
> CREATE INDEX index_conversations_on_status ON public.conversations USING
> btree (status)
> 
> The slow query is the following:
> 
> SELECT id FROM conversations WHERE status = 'in_progress' AND user_id = 123
> 
> There are hundreds of conversations with a status 'in_progress' at a given
> time during the day but virtually none during the night.
> 
> So when the analyze is run during the night, PG then thinks that using the
> index_conversations_on_status will return almost no rows and so it uses
> this index instead of the combined one.
> 
> When the analyze is run during the day, PG correctly uses the right index
> (index_conversations_on_user_id_and_status)

> We currently run a manual weekly vacuum analyze during the night. I'm
> wondering what are our possible solutions. One is to manually run the
> analyze during the day but is there a way to tell PG to run the auto
> analyze at a given time of the day for example ? I guess we are not the
> first ones to have data patterns that differ between when the analyze is
> run and the query is run.

I think you could run manual ANALYZE during the day just for this one column:
  ANALYZE conversations (status);

If it takes too long or causes a performance issue, you could do:
  SET default_statistics_target=10;
  ANALYZE conversations (status);

You could also change to make autovacuum do this on its own, by setting:
 ALTER TABLE conversations SET (autovacuum_analyze_scale_factor=0.005);

If that works but too slow, then maybe ALTER TABLE .. SET STATISTICS 10.

-- 
Justin





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux