Re: Incorrect index used in few cases..

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

 




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@xxxxxxxxxxxxx> wrote:
AminPG Jaffer <aminjaffer.pg@xxxxxxxxx> 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

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

  Powered by Linux