Re: On disable_cost - Mailing list pgsql-hackers

On 02/07/2024 22:54, Robert Haas wrote:
> On Tue, Jul 2, 2024 at 3:36 PM Tom Lane <tgl@sss.pgh.pa.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)