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.
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
duration: 49455.649 ms execute S_10: 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)))
DETAIL: parameters: $1 = '11786959222', $2 = '6', $3 = '54460816501', $4 = '3', $5 = '6', $6 = '103'
LOG: duration: 49455.639 ms plan:
Query Text: 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)))
Aggregate (cost=5009342.34..5009342.35 rows=1 width=8) (actual time=49455.626..49455.626 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=56288997
-> Index Scan using i_agi_tc_tcn on
b.tc (cost=0.57..5009342.34 rows=1 width=0) (actual time=46452.555..49455.616 rows=1 loops=1)
Output: id, tname, ...
Index Cond: (tc.ag_id IS NOT NULL)
Filter: ((tc.a_id = '11786959222'::numeric) AND (tc.m_id = '6'::numeric) AND (
tc.id = '54460816501'::numeric) AND (
tc.pt = ANY ('{3,6,103}'::numeric[])))
Rows Removed by Filter: 70996637
Buffers: shared hit=56288997
