Date: Mon, 11 Jan 2021 10:29:04 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: =?iso-8859-1?Q?R=E9mi?= Chatenay <remi.chatenay@doctolib.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: How to deal with analyze gathering irrelevant stats
Message-ID: <20210111162904.GS1849@telsasoft.com>
References: 
 <CAA==dd=gp5c4oQe8ffOu7iPUrgikA508robTnUV=AXx+o5EJfQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAA==dd=gp5c4oQe8ffOu7iPUrgikA508robTnUV=AXx+o5EJfQ@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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