Postgres Pro
Downloads
Home   >   mailing lists

Thread: int8, primary key, seq scan

int8, primary key, seq scan

From
Jeff Amiel
Date:
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?

Re: int8, primary key, seq scan

From
Dave Cramer
Date:
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

Re: int8, primary key, seq scan

From
Tom Lane
Date:
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

Re: int8, primary key, seq scan

From
Tom Lane
Date:
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

Re: int8, primary key, seq scan

From
Jeff Amiel
Date:
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
>
>
>
>
>

Re: int8, primary key, seq scan

From
Jeff Amiel
Date:
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
>
>
>
>
>