Incorrect index used in few cases..

AminPG Jaffer <[email protected]>
Newsgroups gmane.comp.db.postgresql.performance
Message-ID <CAJ9dAqn5UzOQ4wVoCxXPGEu3+WJeZ35xY0bijA4XEpizU8SS5g@mail.gmail.com>
Hi

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


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

Thanks
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.