Re: Incorrect index used in few cases.. - Mailing list pgsql-performance

AminPG Jaffer <aminjaffer.pg@gmail.com> writes:
> Here is the table structure.

Hpmh.  I thought it was just barely possible that you had a datatype
mismatch between the columns and the parameters, but nope, the columns
are "numeric" just like the parameters.

I'm pretty baffled.  I tried to duplicate the problem with some dummy
data (as attached) and could not.  In my hands, it wants to use the
i_tc_adid_tid index, or if I drop that then the pkey index, and any
other possible plan is orders of magnitude more expensive than those.

Another far-fetched theory is that the theoretically-better indexes
are so badly bloated as to discourage the planner from using them.
You could eliminate that one by checking the index sizes with "\di+".

Are you perhaps running with non-default values for any planner cost
parameters?  Or it's not a stock build of Postgres?

If you could find a way to adjust the attached example so that it
produces the same misbehavior you see with live data, that would be
very interesting ...

            regards, tom lane

drop table tc;

create table tc(
 id                     numeric(38,0)                primary key,
 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            CHECK (length(t_secondary) <= 4500)
);

insert into tc
select
  x as id,
  'tname_' || x as tname,
  case when x % 10 = 0 then null else x end as ag_id,
  'tc' as tc,
  0 as status,
  0 as internal_status,
  now() as create_date,
  42 as version,
  0 as match_type,
  (random()*1000)::int as c_id,
  x/4 as m_id,
  (random()*100)::int as a_id,
  0 as maxb,
  null as b_cc,
  0 as ui_status,
  null as destination_url,
  'c_b' as created_by,
  now() as creation_date,
  'l_u' as last_updated_by,
  now() as last_updated_date,
  0 as pr,
  0 as ts,
  x as uniqueness_hash_v2,
  x % 100 as pt,
  0 as history,
  null as t_secondary
from generate_series(1,1000000) x;

create index    "i_agi_tc_tcn" on tc (ag_id, tname);
create index    "i_cid_agid_tcn" on tc (c_id, ag_id, tname);
create index    "i_tc_adid_tid" on tc (a_id, id);
create index    "i_tc_advertiser_id" on tc (a_id);
create index    "i_tc_campaign_id" on tc (c_id);
create index    "i_tc_lud_agi" on tc (last_updated_date, ag_id);
create index    "i_tc_uniqueness_hash_v2" on tc (uniqueness_hash_v2);

vacuum analyze tc;

prepare p as
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)));

explain verbose execute p(0, 1, 42, 4,5,6);