Re: performance of analytical query

On Fri, 12 Nov 2021 at 03:41, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> On Thu, Nov 11, 2021 at 08:20:57PM +0100, Jiří Fejfar wrote:
> > Hi folks,
> >
> > we have found that (probably after VACUUM ANALYZE) one analytical query
> > starts to be slow on our production DB. Moreover, more or less the same
> > plan is used on our testing data (how to restore our testing data is
> > described at the end of this email), or better to say the same problem
> > exists in both (production vs testing data) scenarios: nested loop
> scanning
> > CTE several thousand times is used due to the bad estimates:
> > https://explain.dalibo.com/plan/sER#plan/node/87 (query is included on
> > dalibo).
>
> > Do you have any idea how to get HASH JOINS in the CTE w_1p_data instead
> of
> > NESTED LOOPs?
> > * Add some statistics to not get bad estimates on "lower-level" CTEs?
>
> Do you know why the estimates are bad ?
>
> I have no clear insight at the moment... problem is probably with bad
estimates which chain along the whole tree of nodes... one bad estimate was
after aggregation for example... probably, I would need to explore
carefully whole execution plan and identify sources of unprecise estimates
and correct it with additional, more precise statistics when possible,
right?

> Index Scan using t_map_plot_cell__cell_gid__idx on cm_plot2cell_mapping
> cm_plot2cell_mapping (cost=0.29..18.59 rows=381 width=12) (actual
> time=0.015..2.373 rows=3,898 loops=1)
> Index Cond: (cm_plot2cell_mapping.estimation_cell =
> f_a_cell.estimation_cell)
> Buffers: shared hit=110
>
> I don't know, but is the estimate for this portion of the plan improved by
> doing:
> | ALTER TABLE f_a_cell ALTER estimation_cell SET STATISTICS 500; ANALYZE
> f_a_cell;
>
> this does not help to the plan as a whole... but I am thinking about
increasing this parameter (size of sample) at the DB level

> > * In a slightly more complicated function I used temporary tables to be
> > able to narrow statistics [2] but I am afraid of system table bloating
> > because of the huge amount of usage of this function on the production
> > (hundred thousand of calls by day when data are to be analyzed).
>
> I would try this for sure - I think hundreds of calls per day would be no
> problem. If you're concerned, you could add manual calls to do (for
> example)
> VACUUM pg_attribute; after dropping the temp tables.
>
> it is hundreds of thousands of calls (10^5) ... but yes I got some hints
how to avoid bloating (basically use temp tables longer and truncate them
instead of deleting when possible)

> BTW, we disable nested loops for the our analytic report queries. I have
> never
> been able to avoid pathological plans any other way.
>

I will think about that.

AND

we further simplified the query and get again one good execution plan
https://explain.dalibo.com/plan/tCk :-)

I have some thoughts now:

* I know that PG is focused on OLTP rather then analytics, but we are happy
with it at all and do not wish to use another engine for analytical
queries... isn't somewhere some "PG analytical best practice" available?
* It seems that the the form / style of query has great impact on execution
plans... I was very happy with writing queries as CTEs on top of other CTEs
or layering VIEWS because you can really focus on the semantics of the
problem and I hoped that planner will somehow magically "compile" my code
and get something good enough with respect to performance. Of course, I
have to not use materialized CTEs, but it was not possible with NOT
MATERIALIZED version as performance was bad and I was not able even to get
oriented in exec. plan...

Thank you for your ideas! J.