From: | Jeff Amiel <jamiel(at)istreamimaging(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | int8, primary key, seq scan |
Date: | 2004-08-19 21:01:39 |
Message-ID: | 41251533.80500@istreamimaging.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Martini | 2004-08-19 21:17:30 | Re: libpq: passwords WAS: scripting & psql issues |
Previous Message | Gaetano Mendola | 2004-08-19 20:57:58 | Re: Postgresql 8.0 beta 1 - strange cpu usage statistics and slow |