Re: Incorrect index used in few cases.. - Mailing list pgsql-performance
| From | AminPG Jaffer |
|---|---|
| Subject | Re: Incorrect index used in few cases.. |
| Date | |
| Msg-id | CAJ9dAq=mvWapLyo7y-ERc4WD=7cFM3nP9Zx_F6-zAdSnezX_=g@mail.gmail.com Whole thread Raw |
| In response to | Re: Incorrect index used in few cases.. (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Incorrect index used in few cases..
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-performance |
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)
-----------------------+-----------------------------+-----------------------------------------------------------
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@sss.pgh.pa.us> wrote:
AminPG Jaffer <aminjaffer.pg@gmail.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
pgsql-performance by date: