Re: disabling seqscan not using primary key index?

On Sat, May 15, 2021 at 4:40 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> The answer is fairly simple, the planner just never considers using
> the primary key index as there are no possible cases where it would be
> useful.

Does this mean that any UNIQUE constraint on the table is subject to
the same consideration?

> There are no quals it can help filter and there is no
> ordering require that it can help provide presorted input for. If
> you'd added an ORDER BY pk, you'll notice the planner does consider
> the index and it does come out much cheaper than the penalised seq
> scan. So the planner had no choice but to use the seqscan.

Yes, of course with an order by I would have been expecting the index,
my doubt was about the fact that was not used even with seqscan to
off, and since you explained that the primary index is never used, now
it does make sense to me.

>
> You should also be aware that the majority of the time when you
> disable a given planner node that we only just add a large startup
> cost penalty when costing paths for that node type. There are a
> handful of nodes that are hard disabled. The reason we just add the
> large penalty rather than stop that node it being used is that in many
> cases we'd just fail to produce a plan due to there being no other
> means to get the required results.
>

Yes, thanks, I understood this: the planner must have some choices, so
in order to "disable" a plan PostgreSQL raises the cost in the "hope"
the disabled access method will not be chosen.

> As for JIT being enabled. The query's cost is above jit_above_cost,
> so JIT is enabled. The reason or that is that enable_seqscan TO off
> added the startup penalty which pushed the plan's cost well above the
> jit threshold.

Ah, shame on me, I forgot about the jit_above_cost!

Thanks,
Luca