Hello,
I believe that the Int8/BigInt items are known issues but I have a knew programmer that ran into it
over the weekend (he didn't call me when he encountered the problem, when he should of) and we have a
customer that burned some significant time on it as well. Will this be fixed in 7.4?
Here is a test case a customer sent me:
Suppose you have a table:
create table bid (
bid_id bigint not null,
bid_time timestamp, constraint bid_pk primary key (bid_id));
Populate it with a million rows or so.
This query:
explain select bid_id, bid_time from bid where bid_id = 10000
Will always sequential scan.
This query:
explain select bid_id, bid_time from bid where bid_id = '10000'
Will use the index.
Where this really gets to be a pain in the butt is with a UDF in plpgsql... this UDF will only sequential scan:
create function bid_check(bigint) returns bool as '
declare
in_bid_id alias for $1;
begin
if (select count(*) from bid where bid_id = in_bid_id) = 1 then
return true;
else
return false;
end if;
end;
' language 'plpgsql';
Without that million rows, my 7.3.4 uses a RESULT plan with a subselect of an AGG plan using an INDEX scan ... I guess that's not really PL/pgSQL related but more an SPI/param/optimizer issue. The optimizer get's different ideas about the selectivity of $n parameters vs. constant values, and the in_bid_id variable in that statement get's replaced by a $n parameter for preparing an SPI plan.
Jan
-- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
