From: | torikoshia <torikoshia(at)oss(dot)nttdata(dot)com> |
---|---|
To: | Michael Paquier <michael(at)paquier(dot)xyz>, legrand_legrand(at)hotmail(dot)com, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> |
Cc: | pgsql-hackers(at)postgresql(dot)org, ikedamsh(at)oss(dot)nttdata(dot)com, atorik(at)gmail(dot)com, tatsuro(dot)yamada(dot)tf(at)nttcom(dot)co(dot)jp |
Subject: | Re: Is it useful to record whether plans are generic or custom? |
Date: | 2020-09-28 13:14:01 |
Message-ID: | 981ce240398b53ee5b72dd591e4fae8f@oss.nttdata.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2020-09-17 13:46, Michael Paquier wrote:
> On Fri, Jul 31, 2020 at 06:47:48PM +0900, torikoshia wrote:
>> Oops, sorry about that.
>> I just fixed it there for now.
>
> The regression tests of the patch look unstable, and the CF bot is
> reporting a failure here:
> https://travis-ci.org/github/postgresql-cfbot/postgresql/builds/727833416
> --
> Michael
Thank you for letting me know!
I'd like to reach a basic agreement on how we expose the
generic/custom plan information in pgss first.
Given the discussion so far, adding a new attribute to pgss key
is not appropriate since it can easily increase the number of
entries in pgss.
OTOH, just exposing the number of times generic/custom plan was
chosen seems not enough to know whether performance is degraded.
I'm now thinking about exposing not only the number of times
generic/custom plan was chosen but also some performance
metrics like 'total_time' for both generic and custom plans.
Attached a poc patch which exposes total, min, max, mean and
stddev time for both generic and custom plans.
=# SELECT * FROM =# SELECT * FROM pg_stat_statements;
-[ RECORD 1
]-------+---------------------------------------------------------
userid | 10
dbid | 12878
queryid | 4617094108938234366
query | PREPARE pr1 AS SELECT * FROM pg_class WHERE
relname = $1
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
calls | 6
total_exec_time | 0.46600699999999995
min_exec_time | 0.029376000000000003
max_exec_time | 0.237413
mean_exec_time | 0.07766783333333334
stddev_exec_time | 0.07254973134206326
generic_calls | 1
total_generic_time | 0.045334000000000006
min_generic_time | 0.045334000000000006
max_generic_time | 0.045334000000000006
mean_generic_time | 0.045334000000000006
stddev_generic_time | 0
custom_calls | 5
total_custom_time | 0.42067299999999996
min_custom_time | 0.029376000000000003
max_custom_time | 0.237413
mean_custom_time | 0.0841346
stddev_custom_time | 0.07787966226583164
...
In this patch, exposing new columns is mandatory, but I think
it's better to make it optional by adding a GUC something
like 'pgss.track_general_custom_plans.
I also feel it makes the number of columns too many.
Just adding the total time may be sufficient.
Any thoughts?
Regards,
--
Atsushi Torikoshi
Attachment | Content-Type | Size |
---|---|---|
0003-POC-add-plan-type-to-pgss.patch | text/x-diff | 7.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2020-09-28 13:17:37 | Re: Partition prune with stable Expr |
Previous Message | Esteban Zimanyi | 2020-09-28 13:10:36 | Adding constructors for path and polygon geometric types |