Re: Too few rows expected by Planner on partitioned tables

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:

------------------------------------------------------------------------------------------------------------------------------------------------

EXPLAIN (ANALYZE , BUFFERS)
SELECT sum(visitors * n)
FROM location_statistics st
WHERE st.daterange = '[2019-03-04,2019-03-11)'::DATERANGE

QUERY PLAN
Aggregate (cost=2.79..2.80 rows=1 width=8) (actual time=1143.393..1143.393 rows=1 loops=1)
Buffers: shared hit=304958
-> Index Scan using location_statistics_y2019m03w_pkey on location_statistics_y2019m03w st (cost=0.56..2.78 rows=1 width=8) (actual time=0.024..931.645 rows=4296639 loops=1)
Index Cond: (daterange = '[2019-03-04,2019-03-11)'::daterange)
Buffers: shared hit=304958
Planning Time: 0.080 ms
Execution Time: 1143.421 ms

------------------------------------------------------------------------------------------------------------------------------------------------

EXPLAIN (ANALYZE , BUFFERS)
SELECT sum(visitors * n)
FROM location_statistics_y2019m03w st
WHERE st.daterange = '[2019-03-04,2019-03-11)'::DATERANGE

QUERY PLAN
Aggregate (cost=2.79..2.80 rows=1 width=8) (actual time=1126.819..1126.820 rows=1 loops=1)
Buffers: shared hit=304958
-> Index Scan using location_statistics_y2019m03w_pkey on location_statistics_y2019m03w st (cost=0.56..2.78 rows=1 width=8) (actual time=0.023..763.852 rows=4296639 loops=1)
Index Cond: (daterange = '[2019-03-04,2019-03-11)'::daterange)
Buffers: shared hit=304958
Planning Time: 0.046 ms
Execution Time: 1126.845 ms

------------------------------------------------------------------------------------------------------------------------------------------------
Checking only on the spatial_feature is not the same query, as the table contains 4 different date ranges. Furthermore, there is no index for this operation. Because of that, I can only invoke this query on one partition, otherwise the query would take days.

EXPLAIN (ANALYZE , BUFFERS)
SELECT sum(visitors * n)
FROM location_statistics_y2019m03w st
WHERE spatial_feature_id = 12675

QUERY PLAN
Finalize Aggregate (cost=288490.25..288490.26 rows=1 width=8) (actual time=1131.593..1131.593 rows=1 loops=1)
Buffers: shared hit=40156 read=139887
-> Gather (cost=288490.03..288490.24 rows=2 width=8) (actual time=1131.499..1148.872 rows=2 loops=1)
Workers Planned: 2
Workers Launched: 1
Buffers: shared hit=40156 read=139887
-> Partial Aggregate (cost=287490.03..287490.04 rows=1 width=8) (actual time=1118.578..1118.579 rows=1 loops=2)
Buffers: shared hit=40156 read=139887
-> Parallel Seq Scan on location_statistics_y2019m03w st (cost=0.00..280378.27 rows=948235 width=8) (actual time=3.544..1032.899 rows=1134146 loops=2)
Filter: (spatial_feature_id = 12675)
Rows Removed by Filter: 8498136
Buffers: shared hit=40156 read=139887
Planning Time: 0.218 ms
JIT:
Functions: 12
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.929 ms, Inlining 0.000 ms, Optimization 0.426 ms, Emission 6.300 ms, Total 7.655 ms
Execution Time: 1191.741 ms

The estimates seem to be good though.

Thanks in Advance

Julian

[http://www.invenium.io/images/invenium_triangle_64.png]
Julian P. Wolf | Invenium Data Insights GmbH
julian(dot)wolf(at)invenium(dot)io | +43 664 88 199 013
Herrengasse 28 | 8010 Graz | www.invenium.io

________________________________
From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Sent: Tuesday, July 21, 2020 7:27 PM
To: Julian Wolf <julian(dot)wolf(at)invenium(dot)io>
Cc: pgsql-performance Postgres Mailing List <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Re: Too few rows expected by Planner on partitioned tables

On Tue, Jul 21, 2020 at 01:09:22PM +0000, Julian Wolf wrote:
> 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.

> daterange daterange NOT NULL,
> spatial_feature_id INTEGER,

> 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
>
> As can be seen, the planner predicts one row to be returned, although it should be around 3% (11% of the entries are of the given ID, which are distributed over 4 weeks = date ranges) of the table. Using the partition table directly, does not change this fact.

Is there a correlation between daterange and spacial_feature_id ?

Are the estimates good if you query on *only* daterange? spacial_feature_id ?

Maybe what you need is:
https://www.postgresql.org/docs/devel/sql-createstatistics.html
CREATE STATISTICS stats (dependencies) ON daterange, spacial_feature_id FROM location_statistics;
ANALYZE location_statistics;

--
Justin