Re: index paths and enable_indexscan

On Tue, Apr 14, 2020 at 5:12 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

>
>
> On Tue, Apr 14, 2020 at 4:58 PM Amit Langote <amitlangote09(at)gmail(dot)com>
> wrote:
>
>> On Tue, Apr 14, 2020 at 5:29 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
>> wrote:
>> > On Tue, Apr 14, 2020 at 3:40 PM Amit Langote <amitlangote09(at)gmail(dot)com>
>> wrote:
>> >> On Tue, Apr 14, 2020 at 4:13 PM Richard Guo <guofenglinux(at)gmail(dot)com>
>> wrote:
>> >> > On Tue, Apr 14, 2020 at 2:44 PM Amit Langote <
>> amitlangote09(at)gmail(dot)com> wrote:
>> >> >> Maybe I am missing something obvious, but is it intentional that
>> >> >> enable_indexscan is checked by cost_index(), that is, *after*
>> creating
>> >> >> an index path? I was expecting that if enable_indexscan is off,
>> then
>> >> >> no index paths would be generated to begin with, because I thought
>> >> >> they are optional.
>> >> >
>> >> > I think the cost estimate of index paths is the same as other paths
>> on
>> >> > that setting enable_xxx to off only adds a penalty factor
>> (disable_cost)
>> >> > to the path's cost. The path would be still generated and compete
>> with
>> >> > other paths in add_path().
>> >>
>> >> Yeah, but I am asking why build the path to begin with, as there will
>> >> always be seq scan path for base rels.
>> >
>> > I guess that is because user may disable seqscan as well. If so, we
>> > still need formula to decide with one to use, which requires index path
>> > has to be calculated. but since disabling the two at the same time is
>> rare,
>> > we can ignore the index path build if user allow seqscan
>>
>> I am saying that instead of building index path with disabled cost,
>> just don't build it at all. A base rel will always have a sequetial
>> path, even though with disabled cost if enable_seqscan = off.
>>
>
> Let's say user set enable_seqscan=off and set enable_indexscan=off;
> will you expect user to get seqscan at last? If so, why is seqscan
> (rather than index scan) since both are disabled by user equally?
>
>
The following test should demonstrate what I think.

demo=# create table t(a int);
CREATE TABLE
demo=# insert into t select generate_series(1, 10000000);
INSERT 0 10000000
demo=# create index t_a on t(a);
CREATE INDEX
demo=# analyze t;
ANALYZE
demo=# set enable_seqscan to off;
SET
demo=# set enable_indexscan to off;
SET
demo=# set enable_bitmapscan to off;
SET
demo=# set enable_indexonlyscan to off;
SET
demo=# explain select * from t where a = 1;
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using t_a on t (cost=10000000000.43..10000000008.45 rows=1
width=4)
Index Cond: (a = 1)
(2 rows)

If we just disable index path, we will get seqscan at last.

Regards
Andy Fan

>> Amit Langote
>> EnterpriseDB: http://www.enterprisedb.com
>>
>