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..
Date: Tue, 18 Jun 2019 17:07:55 -0400
Message-ID: <23028.1560892075@sss.pgh.pa.us> (raw)
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>
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);
Attachments:
[text/plain] test-case-that-proves-nothing.sql (2.9K, ../23028.1560892075@sss.pgh.pa.us/2-test-case-that-proves-nothing.sql)
download | inline:
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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, aminjaffer.pg@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Incorrect index used in few cases..
In-Reply-To: <23028.1560892075@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox