pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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
Date: Tue, 17 Aug 2021 13:52:32 -0500
Message-ID: <20210817185232.GN10479@telsasoft.com> (raw)
In-Reply-To: <CAMOk8kqB3X9qFatSJQOubFAW34n8ghR=EPxff20qBnb44n2fKQ@mail.gmail.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>

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





view thread (11+ messages)  latest in thread

Message-ID: <20210817185232.GN10479@telsasoft.com>
Permalink:  ../20210817185232.GN10479@telsasoft.com/
Also on:    postgresql.org/message-id/20210817185232.GN10479@telsasoft.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pryzby@telsasoft.com, matt.dupree@heap.io, pgsql-performance@lists.postgresql.org
  Subject: Re: Postgres using the wrong index index
  In-Reply-To: <20210817185232.GN10479@telsasoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox