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 09:40:16 -0500
Message-ID: <20200722144016.GV5748@telsasoft.com> (raw)
In-Reply-To: <VI1PR10MB16320A5CA401319BD06522BAF0780@VI1PR10MB1632.EURPRD10.PROD.OUTLOOK.COM>
References: <VI1PR10MB16325D8274F865AFF368FAA9F0780@VI1PR10MB1632.EURPRD10.PROD.OUTLOOK.COM>
<VI1PR10MB16320A5CA401319BD06522BAF0780@VI1PR10MB1632.EURPRD10.PROD.OUTLOOK.COM>
On Tue, Jul 21, 2020 at 01:09:22PM +0000, Julian Wolf wrote:
> Hello,
>
> A description of what you are trying to achieve and what results you expect:
> Our database is growing on a daily basis by about 2.5million rows per table (2 at the moment). Because of that, we decided to partition the data, especially, as we are pre-aggregating the data for weeks, months, quarters and years. Every aggregation is stored in a separate partition:
>
...
> Our problem is, that the planner always predicts one row to be returned, although only a part of the primary key is queried. This problem exceeds feasibility of performance rapidly - a query only involving a few days already takes dozens of seconds. All tables are analyzed and pg_stats looks reasonable IMHO.
...
> PRIMARY KEY ( daterange, spatial_feature_id, visitor_profile_id, activity_type_combination_id,
> activity_chain_id),
...
> ) PARTITION BY LIST (daterange);
> schemaname relname n_live_tup
> mobility_insights location_statistics_y2019m03d 23569853
> mobility_insights location_statistics_y2019m03w 19264373
> mobility_insights location_statistics_y2019m03 18105295
> Aggregate (cost=2.79..2.80 rows=1 width=8) (actual time=143.073..143.073 rows=1 loops=1)
> Buffers: shared hit=67334
> -> Index Scan using location_statistics_y2019m03w_pkey on location_statistics_y2019m03w st (cost=0.56..2.78 rows=1 width=8) (actual time=0.026..117.284 rows=516277 loops=1)
> Index Cond: ((daterange = '[2019-03-04,2019-03-11)'::daterange) AND (spatial_feature_id = 12675))
> Buffers: shared hit=67334
I guess this isn't actually the problem query, since it takes 143ms and not
dozens of seconds. I don't know what is the problem query, but maybe it might
help to create an new index on spatial_feature_id, which could be scanned
rather than scanning the unique index.
Also, if daterange *and* spatial_feature_id are always *both* included, then
this might work:
postgres=# CREATE STATISTICS t_stats (mcv) ON daterange,spatial_feature_id FROM t ; ANALYZE t;
--
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: <20200722144016.GV5748@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