Обсуждение: int8, primary key, seq scan
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?
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
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: >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 > >---------------------------(end of broadcast)--------------------------- >TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > > > >
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: > > >>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 > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend > > > > >