Date: Tue, 17 Aug 2021 13:52:32 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Matt Dupree <matt.dupree@heap.io>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Postgres using the wrong index index
Message-ID: <20210817185232.GN10479@telsasoft.com>
References: 
 <CAMOk8kqSPr2sTihQ56Va07TeFRA8E1GsEUfwe4t4LSmj5pjd4A@mail.gmail.com>
 <20210811123856.GE10479@telsasoft.com>
 <CAMOk8kq5Hm80EHTtBR1xD0T0aCDmRoE3nxaWtkNSZqS04CLLfg@mail.gmail.com>
 <20210812034510.GG10479@telsasoft.com>
 <CAMOk8krqeZieBDgqRPV6HK8nXAra4D29Cc9wUpdyEa=OXH7oOw@mail.gmail.com>
 <20210812232006.GN10479@telsasoft.com>
 <CAMOk8kqB3X9qFatSJQOubFAW34n8ghR=EPxff20qBnb44n2fKQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAMOk8kqB3X9qFatSJQOubFAW34n8ghR=EPxff20qBnb44n2fKQ@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210817185232.GN10479%40telsasoft.com>
Precedence: bulk

On Mon, Aug 16, 2021 at 11:22:44AM -0400, Matt Dupree wrote:
> > Is either half of the AND estimated correctly?  If you do a query
> > with only ">=", and a query with only "<=", do either of them give an
> > accurate rowcount estimate ?
> 
> Dropping >= results in the correct index being used. Dropping <= doesn't
> have this effect.

This doesn't answer the question though: are the rowcount estimes accurate (say
within 10%).

It sounds like interpolating the histogram is giving a poor result, at least
over that range of values.  It'd be interesting to see the entire histogram.

You might try increasing (or decreasing) the stats target for that column, and
re-analyzing.

Your histogram bounds are for ~38 months of data, and your query is for the
previous month (July).

$ date -d @1530186399
Thu Jun 28 06:46:39 CDT 2018
$ date -d @1629125609
Mon Aug 16 09:53:29 CDT 2021

$ date -d @1627369200
Tue Jul 27 02:00:00 CDT 2021
$ date -d @1624777200
Sun Jun 27 02:00:00 CDT 2021

The timestamp column has ndistinct near -1, similar to a continuous
distribution, so I'm not sure why the estimate would be so bad.

-- 
Justin