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@sss.pgh.pa.us> wrote:

Andres Freund <andres@anarazel.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