Re: BigInt woes

Joshua D. Drake wrote:

> 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. #
#================================================== JanWieck(at)Yahoo(dot)com #