On 05.09.2024 23:32, Euler Taveira wrote:
On Thu, Sep 5, 2024, at 1:05 PM, Ilia Evdokimov wrote:
It's quite common that poor query performance can be attributed to
inaccurate row estimations by the planner. To make it easier to
detect these discrepancies, rather than scrutinizing the estimates
manually, it would be helpful to output a dedicated |NOTICE| message.
I don't know if NOTICE is a good UI for an inaccurate estimation. The
main issue
with your proposal is that it does not indicate where it is. It is
easier to
inspect small query plans but what if you have a plan with hundreds of
lines?
IMO the client should provide this feature. The shell provides a way
to change
the color and/or style from the output. I have a perl script that reads an
EXPLAIN output and mark with different colors (red, yellow) if the
estimations
are off. psql could do the same.
In your case if the output was changed to something like:
\033[0;1;31mGather (cost=1000.00..107848.00 rows=1000 width=8)
(actual time=0.446..122.476 rows=91 loops=1)\033[0m
Workers Planned: 2
Workers Launched: 2
-> \033[0;1;31mParallel Seq Scan on t (cost=0.00..106748.00
rows=417 width=8) (actual time=77.657..118.000 rows=30 loops=3)\033[0m
Filter: ((a > 10) AND (b <= 200))
Rows Removed by Filter: 3333303
Planning Time: 0.097 ms
Execution Time: 122.502 ms
(8 rows)
Note "\033[0;1;31m" and "\033[0m" that means foreground bold red and
default,
respectively.
Another alternative if you don't want to modify psql is to use the
pager. Create
a script that contains your logic to apply color and/or style to the
desired
(sub)string(s). The following example that I extracted from [1] can
apply colors
to psql output.
$ cat /tmp/pcc.pl
#!/usr/bin/perl -n
print "\033[1m\033[35m$1\033[36m$2\033[32m$3\033[33m$4\033[m" while
/([|+-]+)|([0-9]+)|([a-zA-Z_]+)|([^\w])/g;
and then you can start psql as:
$ PAGER="/c/mypager.pl" psql
[1]
https://stackoverflow.com/questions/5947742/how-to-change-the-output-color-of-echo-in-linux/28938235#28938235
--
Euler Taveira
EDB https://www.enterprisedb.com/
Yes, you are right. It probably doesn't make sense to implement such a
notification on the server side. It makes more sense to handle this on
the client side, where there are many different tools, including your
suggestion, to highlight inaccurate estimates.
Thank you very much for the review!
--
Regards,
Ilia Evdokimov,
Tantor Labs LCC.
