Re: On disable_cost

On 02/07/2024 22:54, Robert Haas wrote:
> On Tue, Jul 2, 2024 at 3:36 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> One could argue for other things, of course. And maybe those other
>>> things are fine, if they're properly justified and documented.
>>
>> [ shrug... ] This isn't a hill that I'm prepared to die on.
>> But I see no good reason to change the very long-standing
>> behaviors of these GUCs.
>
> Well, I don't really know where to go from here. I mean, I think that
> three committers (David, Heikki, yourself) have expressed some
> concerns about changing the behavior. So maybe we shouldn't. But I
> don't understand how it's reasonable to have two very similarly named
> GUCs behave (1) inconsistently with each other and (2) in a way that
> cannot be guessed from the documentation.
>
> I feel like we're just clinging to legacy behavior on the theory that
> somebody, somewhere might be relying on it in some way, which they
> certainly might be. But that doesn't seem like a great reason, either.

I agree the status quo is weird too. I'd be OK to break
backwards-compatibility if we can make it better.

Tom mentioned enable_bitmapscan, and it reminded me that the current
behavior with that is actually a bit annoying. I go through this pattern
very often when I'm investigating query plans:

1. Hmm, let's see what this query plan looks like:

postgres=# explain analyze select * from foo where i=10;
QUERY PLAN

----------------------------------------------------------------------------------------------------------------
Index Scan using foo_i_idx on foo (cost=0.29..8.31 rows=1 width=36)
(actual time=0.079..0.090 rows=2 loops=1)
Index Cond: (i = 10)
Planning Time: 2.220 ms
Execution Time: 0.337 ms
(4 rows)

2. Ok, and how long would it take with a seq scan? Let's see:

postgres=# set enable_indexscan=off;
SET
postgres=# explain analyze select * from foo where i=10;
QUERY PLAN

------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=4.30..8.31 rows=1 width=36) (actual
time=0.102..0.113 rows=2 loops=1)
Recheck Cond: (i = 10)
Heap Blocks: exact=2
-> Bitmap Index Scan on foo_i_idx (cost=0.00..4.30 rows=1 width=0)
(actual time=0.067..0.068 rows=2 loops=1)
Index Cond: (i = 10)
Planning Time: 0.211 ms
Execution Time: 0.215 ms
(7 rows)

3. Oh right, bitmap scan, I forgot about that one. Let's disable that too:

postgres=# set enable_bitmapscan=off;
SET
postgres=# explain analyze select * from foo where i=10;
QUERY PLAN

--------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..1862.00 rows=1 width=36) (actual
time=0.042..39.226 rows=2 loops=1)
Filter: (i = 10)
Rows Removed by Filter: 109998
Planning Time: 0.118 ms
Execution Time: 39.272 ms
(5 rows)

I would be somewhat annoyed if we add another step to that, to also
disable index-only scans separately. It would be nice if
enable_indexscan=off would also disable bitmap scans, that would
eliminate one step from the above. Almost always when I want to disable
index scans, I really want to disable the use of the index altogether.
The problem then of course is, how do you force a bitmap scan without
allowing other index scans, when you want to test them both?

It almost feels like we should have yet another GUC to disable index
scans, index-only scans and bitmap index scans. "enable_indexes=off" or
something.

--
Heikki Linnakangas
Neon (https://neon.tech)