Hi
I didn't see my following response got posted on the mailing list so not sure if it is duplicate.
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))
-> 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?
On Sun, Jun 23, 2019 at 8:07 AM AminPG Jaffer <aminjaffer.pg@xxxxxxxxx> wrote:
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=1The 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 <$2Update 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?ThanksOn Tue, Jun 18, 2019 at 3:23 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:Andres Freund <andres@xxxxxxxxxxx> 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
