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>
 <87msra9vgo.fsf@163.com>
 <CAApHDvrhKaeFzw7KczR13s=3OOb7SSjLHM7-cBpp492=0P9RSA@mail.gmail.com>
 <87edcl9hlu.fsf@163.com> <877cicao6e.fsf@163.com>
 <056d02c9-c9c7-4384-8522-851259209a17@postgrespro.ru>
User-agent: mu4e 1.10.7; emacs 29.1
From: Andy Fan <zhihuifan1213@163.com>
To: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Cc: David Rowley <dgrowleyml@gmail.com>, PostgreSQL Hackers
 <pgsql-hackers@postgresql.org>, pgsql-hackers@lists.postgresql.org
Subject: Re: a wrong index choose when statistics is out of date
Date: Wed, 13 Mar 2024 15:39:07 +0800
In-reply-to: <056d02c9-c9c7-4384-8522-851259209a17@postgrespro.ru>
Message-ID: <87jzm68tjv.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/87jzm68tjv.fsf%40163.com>
Precedence: bulk


>
> Having had the same problem for a long time, I've made an attempt and
> invented a patch that probes an index to determine whether the estimated
> constant is within statistics' scope.
> I remember David's remark on the overhead problem, but I don't argue it
> here. This patch is on the table to have one more solution sketch for
> further discussion.

I think the following code will be really horrendous on peformance
aspect, think about the cases where we have thousands of tuples.

+		index_rescan(index_scan, scankeys, 1, NULL, 0);
+		while (index_getnext_tid(index_scan, ForwardScanDirection) != NULL)
+		{
+			ntuples++;
+		}
+

> Also, Andy, if you have a specific problem with index choosing, you can
> try a tiny option that makes the index-picking technique less dependent
> on the ordering of index lists [1].

thanks, index choosing issue already not the only issue I want to address now.

You said the my patch was kind of lucky to work at [1], have you figure
out an example to prove that?

[1]
https://www.postgresql.org/message-id/701d2097-2c5b-41e2-8629-734e3c8ba613%40postgrespro.ru 

-- 
Best Regards
Andy Fan