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 <[email protected]> wrote:

> AminPG Jaffer <[email protected]> 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
>