Re: Adding NOTICE for differences between estimated and actual rows

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/