Re: Incorrect index used in few cases..

Sorry for late reply.

The initial values before upgrade for seq_page_cost=1, random_page_cost=4
and after upgrading when we started to see the issues as we were seeing
"Seq Scan" we change them seq_page_cost=1, random_page_cost=1

The issue happens only in production so making the index invalid would
affect service so it isn't something we can do.
I have tried to rebuild the PK index to see it helps or not but it doesn't
seem help.

Related to the same issue we sometimes see following Seq Scan on update
when querying by PK alone which appears to be related.

update tc set...where id = $1 and version <$2
Update on tc (cost=10000000000.00..10003184001.52 rows=1 width=1848)
-> Seq Scan on tc (cost=10000000000.00..10003184001.52 rows=1
width=1848)
Filter: ((version < '38'::numeric) AND (id =
'53670604704'::numeric))

I was trying to find where the cost=10000000000 is set in the source code
but wasn't able to find it, do anyone where it is set?
And if you someone can point me to the code where it goes through the
execution plans when SQL is sent i can try to go through the code to see if
can figure out what it is doing behind to scene in it's calculation?

Thanks

On Tue, Jun 18, 2019 at 3:23 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andres Freund <andres(at)anarazel(dot)de> writes:
> > Are those indexes used for other queries? Any chance they've been
> > recently created?
>
> > SELECT indexrelid::regclass, xmin, indcheckxmin, indisvalid, indisready,
> > indislive, txid_current(), txid_current_snapshot()
> > FROM pg_index WHERE indrelid = 'tc'::regclass;
>
> > might tell us.
>
> Oh, that's a good idea.
>
> > Amin, might be worth to see what the query plan is if you disable that
> > index. I assume it's too big to quickly drop (based on the ?
>
> Considering that the "right" query plan would have a cost estimate in
> the single digits or close to it, I have to suppose that the planner is
> rejecting that index as unusable, not making a cost-based decision not
> to use it. (Well, maybe if it's bloated by three orders of magnitude
> compared to the other indexes, it'd lose on cost. Doesn't seem likely
> though.)
>
> So I think we're looking for a hard "can't use the index" reason, and
> now we've eliminated datatype mismatch which'd be the most obvious
> such reason. But index-isnt-valid or index-isnt-ready might do the
> trick.
>
> regards, tom lane
>