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
Date: Wed, 22 Jul 2020 07:28:47 -0500
Message-ID: <20200722122846.GS5748@telsasoft.com> (raw)
In-Reply-To: <AM5PR10MB1617D1CEBE727976FC66F050F0790@AM5PR10MB1617.EURPRD10.PROD.OUTLOOK.COM>
References: <VI1PR10MB16325D8274F865AFF368FAA9F0780@VI1PR10MB1632.EURPRD10.PROD.OUTLOOK.COM>
<VI1PR10MB16320A5CA401319BD06522BAF0780@VI1PR10MB1632.EURPRD10.PROD.OUTLOOK.COM>
<20200721172713.GJ5748@telsasoft.com>
<AM5PR10MB1617D1CEBE727976FC66F050F0790@AM5PR10MB1617.EURPRD10.PROD.OUTLOOK.COM>
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
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, julian.wolf@invenium.io, pgsql-performance@lists.postgresql.org
Subject: Re: Too few rows expected by Planner on partitioned tables
In-Reply-To: <20200722122846.GS5748@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