Skip site navigation (1) Skip section navigation (2)

Re: int8, primary key, seq scan

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Jeff Amiel <jamiel(at)istreamimaging(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: int8, primary key, seq scan
Date: 2004-08-20 00:51:03
Message-ID: 1092963062.1546.663.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-general
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(at)postgresql(dot)org
> 
-- 
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com


In response to

pgsql-general by date

Next:From: Tom LaneDate: 2004-08-20 01:03:55
Subject: Re: unserializable transaction?
Previous:From: Robby RussellDate: 2004-08-20 00:48:01
Subject: Re: Postgresql feature

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group