Re: Adding NOTICE for differences between estimated and actual rows

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.