From: Tom Lane <tgl@sss.pgh.pa.us>
To: AminPG Jaffer <aminjaffer.pg@gmail.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Incorrect index used in few cases..
In-reply-to: 
 <CAJ9dAq=mvWapLyo7y-ERc4WD=7cFM3nP9Zx_F6-zAdSnezX_=g@mail.gmail.com>
References: 
 <CAJ9dAqn5UzOQ4wVoCxXPGEu3+WJeZ35xY0bijA4XEpizU8SS5g@mail.gmail.com>
 <802.1560864908@sss.pgh.pa.us>
 <CAJ9dAq=mvWapLyo7y-ERc4WD=7cFM3nP9Zx_F6-zAdSnezX_=g@mail.gmail.com>
Comments: In-reply-to AminPG Jaffer <aminjaffer.pg@gmail.com>
	message dated "Tue, 18 Jun 2019 11:55:47 -0700"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <23000.1560892028.0@sss.pgh.pa.us>
Date: Tue, 18 Jun 2019 17:07:55 -0400
Message-ID: <23028.1560892075@sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <23000.1560892028.1@sss.pgh.pa.us>

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


------- =_aaaaaaaaaa0
Content-Type: text/plain; name="test-case-that-proves-nothing.sql";
	charset="us-ascii"
Content-ID: <23000.1560892028.2@sss.pgh.pa.us>
Content-Description: test-case-that-proves-nothing.sql
Content-Transfer-Encoding: quoted-printable

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 time=
zone('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) <=3D 45=
00)
);

insert into tc
select
  x as id,
  'tname_' || x as tname,
  case when x % 10 =3D 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 =3D $1)) AND ((tc.m_id =3D $2)) AN=
D
((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);

------- =_aaaaaaaaaa0--