Date: Wed, 22 Jul 2020 07:28:47 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Julian Wolf <julian.wolf@invenium.io>
Cc: pgsql-performance Postgres Mailing List
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Too few rows expected by Planner on partitioned tables
Message-ID: <20200722122846.GS5748@telsasoft.com>
References: 
 <VI1PR10MB16325D8274F865AFF368FAA9F0780@VI1PR10MB1632.EURPRD10.PROD.OUTLOOK.COM>
 <VI1PR10MB16320A5CA401319BD06522BAF0780@VI1PR10MB1632.EURPRD10.PROD.OUTLOOK.COM>
 <20200721172713.GJ5748@telsasoft.com>
 <AM5PR10MB1617D1CEBE727976FC66F050F0790@AM5PR10MB1617.EURPRD10.PROD.OUTLOOK.COM>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <AM5PR10MB1617D1CEBE727976FC66F050F0790@AM5PR10MB1617.EURPRD10.PROD.OUTLOOK.COM>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Wed, Jul 22, 2020 at 06:33:17AM +0000, Julian Wolf wrote:
> Hello Justin,
> 
> 
> thank you very much for your fast response.
> 
> > Is there a correlation between daterange and spacial_feature_id ?
> 
> I am not entirely sure, what you mean by that. Basically, no, they are not correlated - spatial features are places on a map, date ranges are time periods. But, as they are both part of a primary key in this particular table, they are correlated in some way as to be a part of uniquely identifying a row.
> 
> 
> > Are the estimates good if you query on *only* daterange?  spacial_feature_id ?
> Unfortunately no, they are not:

I checked and found that range types don't have "normal" statistics, and in
particular seem to use a poor ndistinct estimate..

                /* Estimate that non-null values are unique */
                stats->stadistinct = -1.0 * (1.0 - stats->stanullfrac);

You could try to cheat and hardcode a different ndistinct that's "less wrong"
by doing something like this:

ALTER TABLE t ALTER a SET (N_DISTINCT=-0.001);  ANALYZE t;

Maybe a better way is to create an index ON: lower(range),upper(range)
And then query: WHERE (lower(a),upper(a)) = (1,112);

Since you'd be storing the values separately in the index anyway, maybe this
means that range types won't work well for you for primary, searchable columns.

But if you're stuck with the schema, another kludge, if you want to do
something extra weird, is to remove statistics entirely by disabling
autoanalyze on the table and then manually run ANALYZE(columns) where columns
doesn't include the range column.  You'd have to remove the stats:

begin; DELETE FROM pg_statistic s USING pg_attribute a WHERE s.staattnum=a.attnum AND s.starelid=a.attrelid AND starelid='t'::regclass AND a.attname='a';

-- 
Justin