Incorrect index used in few cases..

From: AminPG Jaffer <aminjaffer(dot)pg(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Incorrect index used in few cases..
Date: 2019-06-18 13:11:54
Message-ID: CAJ9dAqn5UzOQ4wVoCxXPGEu3+WJeZ35xY0bijA4XEpizU8SS5g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2019-06-18 13:35:08 Re: Incorrect index used in few cases..
Previous Message Fabio Pardi 2019-06-17 07:53:13 Re: wal_log_hints benchmarks