Incorrect index used in few cases..

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux