Re: Incorrect index used in few cases..

Here is the table structure.

Column | Type |
Modifiers
-----------------------+-----------------------------+-----------------------------------------------------------
id | numeric(38,0) | not null
tname | character varying(255) | not null
ag_id | numeric(38,0) |
tc | character varying(255) | not null
status | numeric(10,0) | not null
internal_status | numeric(10,0) | not null
create_date | timestamp(6) with time zone | not null
version | numeric(38,0) | not null
match_type | numeric(10,0) | not null default 0
c_id | numeric(38,0) | not null
m_id | numeric(38,0) | not null
a_id | numeric(38,0) | not null
maxb | numeric(18,6) |
b_cc | character varying(10) |
ui_status | numeric(10,0) | not null default 0
destination_url | character varying(2084) |
created_by | character varying(64) | not null
creation_date | timestamp(0) with time zone | not null default
timezone('UTC'::text, clock_timestamp())
last_updated_by | character varying(64) | not null
last_updated_date | timestamp(0) with time zone | not null
pr | numeric(5,0) | not null default 0
ts | numeric(1,0) | not null default 0
uniqueness_hash_v2 | numeric(29,0) | not null
pt | numeric(5,0) |
history | bigint |
t_secondary | text |

Indexes:
"pk_id" PRIMARY KEY, btree (id)
"i_agi_tc_tcn" btree (ag_id, tname)
"i_cid_agid_tcn" btree (c_id, ag_id, tname)
"i_tc_adid_tid" btree (a_id, id)
"i_tc_advertiser_id" btree (a_id)
"i_tc_campaign_id" btree (c_id)
"i_tc_lud_agi" btree (last_updated_date, ag_id)
"i_tc_uniqueness_hash_v2" btree (uniqueness_hash_v2)
Check constraints:
"tc_secondary" CHECK (length(t_secondary) <= 4500)

On Tue, Jun 18, 2019 at 6:35 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> AminPG Jaffer <aminjaffer(dot)pg(at)gmail(dot)com> writes:
> > 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)))
>
> What data types are these columns? For that matter, could we see the
> whole schema for the table (psql \d+ output or equivalent)?
>
> regards, tom lane
>