Hi, On 2019-06-18 06:11:54 -0700, AminPG Jaffer wrote: > We recently upgraded one of the instances from 9.6.3 to 9.6.12 and seeing > following issue that occurs for few cases. > > We recently upgraded one of the instances from 9.6.3 to 9.6.12 and seeing > following issue that occurs for few cases. > > I have tried running analyze on the table with different values from 1000 - > 5000 but it doesn't seem to help the issue. There is some skew in a_id > but the combination index i_tc_adid_tid btree (a_id, id) makes the index > unique as it includes primary key. > > Is there an explanation why it is using incorrect index? > > SQL: > SELECT count(*) FROM tc WHERE ((tc.a_id = $1)) AND ((tc.m_id = $2)) AND > ((tc.ag_id is not null)) AND ((tc.id in ($3))) AND ((tc.pt in ($4, $5, $6))) > > Indexes on the table: > i_tc_adid_tid btree (a_id, id) > pk_id PRIMARY KEY, btree (id) > i_agi_tc_tcn btree (ag_id, tname) ---> index that gets used 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. On 2019-06-18 17:07:55 -0400, Tom Lane wrote: > I'm pretty baffled. I tried to duplicate the problem with some dummy > data (as attached) and could not. In my hands, it wants to use the > i_tc_adid_tid index, or if I drop that then the pkey index, and any > other possible plan is orders of magnitude more expensive than those. > Another far-fetched theory is that the theoretically-better indexes > are so badly bloated as to discourage the planner from using them. > You could eliminate that one by checking the index sizes with "\di+". > > Are you perhaps running with non-default values for any planner cost > parameters? Or it's not a stock build of Postgres? > > If you could find a way to adjust the attached example so that it > produces the same misbehavior you see with live data, that would be > very interesting ... 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 ? Something like: BEGIN; LOCK tc; UPDATE pg_index SET indisvalid = false WHERE indexrelid = 'name_of_index'::regclass AND indisvalid; EXPLAIN yourquery; ROLLBACK; might allow to test that without actually dropping the index. But that of course requires superuser access. Greetings, Andres Freund
