Thread: enable_seqscan to off -> initial cost 10000000000

Hi all,
this could be trivial, but I would like an explaination: if I turn off
sequential scans on a table without indexes, the same access plan is
increased by a 10000000000 factor. I suspect this is a warning for me
to remind that something is misconfigured, or is there anothe reason?

testdb=# create table tseq( pk serial );
CREATE TABLE
testdb=# explain select * from tseq;
                      QUERY PLAN
--------------------------------------------------------
Seq Scan on tseq  (cost=0.00..35.50 rows=2550 width=4)
(1 row)

testdb=# set enable_seqscan to off;
SET
testdb=# explain select * from tseq;
                               QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on tseq  (cost=10000000000.00..10000000035.50 rows=2550 width=4)
(1 row)

testdb=# show server_version;
server_version
----------------
12.5

Thanks,
Luca

Greetings,

* Luca Ferrari (fluca1978@gmail.com) wrote:
> this could be trivial, but I would like an explaination: if I turn off
> sequential scans on a table without indexes, the same access plan is
> increased by a 10000000000 factor. I suspect this is a warning for me
> to remind that something is misconfigured, or is there anothe reason?

No- that's what setting it to "off" means, in actuality we just make it
really expensive to encourage the planner to try and find another plan.
That's not always possible to do though, hence the resulting plan with a
really high cost.

The enable_* options are really just for poking around and not something
you'd want to set.

Thanks,

Stephen

On 4/23/21 10:39 AM, Luca Ferrari wrote:
> Hi all,
> this could be trivial, but I would like an explaination: if I turn off
> sequential scans on a table without indexes, the same access plan is
> increased by a 10000000000 factor. I suspect this is a warning for me
> to remind that something is misconfigured, or is there anothe reason?

Maybe I'm missing something, but in "a table without indexes", what else is 
there besides sequential scan?

-- 
Angular momentum makes the world go 'round.

On Sat, 24 Apr 2021 at 03:44, Ron <ronljohnsonjr@gmail.com> wrote:
>
> On 4/23/21 10:39 AM, Luca Ferrari wrote:
> > Hi all,
> > this could be trivial, but I would like an explaination: if I turn off
> > sequential scans on a table without indexes, the same access plan is
> > increased by a 10000000000 factor. I suspect this is a warning for me
> > to remind that something is misconfigured, or is there anothe reason?
>
> Maybe I'm missing something, but in "a table without indexes", what else is
> there besides sequential scan?

You're not really missing anything.

However, technically, in the yet-to-be-released PG14, if the query had
a TID qual then you could disable seqscan to encourage a TID Range
scan. That's a bit of a stretch of the imagination though.

David