Re: Incorrect index used in few cases..
Tom Lane <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <[email protected]> |
AminPG Jaffer <[email protected]> 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
test-case-that-proves-nothing.sql
(text/plain, 2.9 KB)
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);