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
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.
