int8, primary key, seq scan

This is a known issues with pre 8.0 postgresql...

Dave
On Thu, 2004-08-19 at 17:01, Jeff Amiel wrote:

7.4.2 running on FreeBSD 5.4.2

I have a table (called "draft" ) that has a bigserial as the primary key.

"check_id bigserial NOT NULL"

I do NOT have an additional index on that column.

I have a plpgsql function (stored procedure) that selects from that
table based on the primary key field

"select into draftrow * from draft where check_id=draftid;"
where draft id is declared in the DECLARE section as "draftid int8;"

This query is woefully slow. Sequential scan on table

However, if I modify the query as follows:

"select into draftrow * from draft where check_id=int8(draftid);"

Ultra mega speed improvement. Index scan using the primary key.

I declared the compared value (draftid) as an int8, why should I have to
cast it as such in the query to cause the optimizer to use the primary key?

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com

Jeff Amiel <jamiel@istreamimaging.com> writes:

I declared the compared value (draftid) as an int8, why should I have to
cast it as such in the query to cause the optimizer to use the primary key?

Seems like it should work (and it does work for me, in a quick test with
7.4.5). Could we see the full text of the problematic function?

regards, tom lane

sure....thanks for the assist....

CREATE TABLE public.draft
(
amount numeric(10,2) NOT NULL,
customer int4 NOT NULL,
check_id bigserial NOT NULL,
CONSTRAINT draft_pkey PRIMARY KEY (check_id)
) WITHOUT OIDS;

CREATE SEQUENCE public.draft_check_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 8223372036855129056
CACHE 1;

CREATE OR REPLACE FUNCTION public.insert_draft(int4, numeric)
RETURNS draft AS
'DECLARE
passed_customer ALIAS for $1;
passed_amount ALIAS for $2;
draftid int8;
draftrow draft%rowtype;
BEGIN
insert into draft( amount, customer) values (passed_amount,
passed_customer);
select into draftid currval(\'public.draft_check_id_seq\');
select into draftrow * from draft where check_id=int8(draftid);
return draftrow;
END;'
LANGUAGE 'plpgsql' VOLATILE;

Tom Lane wrote:

Jeff Amiel <jamiel@istreamimaging.com> writes:

CREATE OR REPLACE FUNCTION public.insert_draft(int4, numeric)
RETURNS draft AS
'DECLARE
passed_customer ALIAS for $1;
passed_amount ALIAS for $2;
draftid int8;
draftrow draft%rowtype;
BEGIN
insert into draft( amount, customer) values (passed_amount,
passed_customer);
select into draftid currval(\'public.draft_check_id_seq\');
select into draftrow * from draft where check_id=int8(draftid);
return draftrow;
END;'
LANGUAGE 'plpgsql' VOLATILE;

Hmm. I put this function into 7.4.5, minus the explicit cast to int8,
and it worked just fine (I used a debugger to verify directly that the
last SELECT was producing an indexscan plan). I don't see anything in
the CVS logs to suggest that there was a related bug fix between 7.4.2
and 7.4.5. So I'm baffled why it didn't work for you. Ideas anyone?

regards, tom lane

fyi....
outside the function (from psql) I receive these results (which I guess
are expected, because without casting, I assume it assumes an int4)

select * from draft where check_id=43
and it results in a seq scan...
but if I :
select * from draft where check_id=int8(43)
it results in an index scan using the primary key.

Jeff

Tom Lane wrote: