References: <878r2zeqsp.fsf@163.com>
 <CAApHDvq9QWL6az9veW1-HaW4LwGenW0V8YyKXFLZYoaF_4AQ+Q@mail.gmail.com>
 <87r0gqcjno.fsf@163.com>
 <CAApHDvr6Dv-oHN3qn3t9Z3QHLT0qi80fa_tRNmmjG8r9BhzoHA@mail.gmail.com>
 <87le6xb5j9.fsf@163.com> <87bk7sbzaj.fsf@163.com>
 <CAApHDvo5S5xqWQR1HKzx=5UpBr1ZKhwEtxEkt=3q0z4oR+TnPA@mail.gmail.com>
User-agent: mu4e 1.10.7; emacs 29.1
From: Andy Fan <zhihuifan1213@163.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 pgsql-hackers@lists.postgresql.org
Subject: Re: a wrong index choose when statistics is out of date
Date: Thu, 07 Mar 2024 18:16:52 +0800
In-reply-to: 
 <CAApHDvo5S5xqWQR1HKzx=5UpBr1ZKhwEtxEkt=3q0z4oR+TnPA@mail.gmail.com>
Message-ID: <87msra9vgo.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/87msra9vgo.fsf%40163.com>
Precedence: bulk


David Rowley <dgrowleyml@gmail.com> writes:

> On Wed, 6 Mar 2024 at 02:09, Andy Fan <zhihuifan1213@163.com> wrote:
>>     This patch introduces a new attoptions like this:
>>
>>     ALTER TABLE t ALTER COLUMN col set (force_generic=true);
>>
>>     Then selfunc.c realizes this and ignore the special Const value, then
>>     average selectivity is chosen. This fall into the weakness of generic
>>     plan, but this patch doesn't introduce any new weakness and we leave the
>>     decision to user which could resolve some problem. Also this logic only
>>     apply to eqsel since the ineqsel have the get_actual_variable_range
>>     mechanism which is helpful for index choose case at least.
>
> If you don't want the planner to use the statistics for the column why
> not just do the following?

Acutally I didn't want the planner to ignore the statistics totally, I
want the planner to treat the "Const" which probably miss optimizer part
average, which is just like what we did for generic plan for the blow
query.  

prepare s as SELECT * FROM t WHERE a = $1 and b = $2;
explain (costs off) execute s(109, 8);
           QUERY PLAN            
---------------------------------
 Index Scan using t_a_c_idx on t
   Index Cond: (a = 109)
   Filter: (b = 8) 

(3 rows)

custom plan, Wrong index due to we have a bad estimation for a = 109.


set plan_cache_mode to force_generic_plan ;
explain (costs off) execute s(109, 8);
              QUERY PLAN               
---------------------------------------
 Index Scan using t_a_b_idx on t
   Index Cond: ((a = $1) AND (b = $2))   -- Correct index.
(2 rows)

Generic plan - we use the average estimation for the missed optimizer
statistics part and *if the new value is not so different from existing
ones*, we can get a disired result. 

It is true that the "generic" way is not as exactly accurate as the
"custom" way since the later one can use the data in MCV, but that is
the cost we have to pay to make the missed optimizer statistics less
imporant and generic plan has the same issue as well. As for this
aspect, I think the way you proposed probably have a wider use case.

-- 
Best Regards
Andy Fan