Re: Incorrect index used in few cases..

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