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

int8, primary key, seq scan

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: (view raw, whole thread or download thread mbox)
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?


pgsql-general by date

Next:From: Daniel MartiniDate: 2004-08-19 21:17:30
Subject: Re: libpq: passwords WAS: scripting & psql issues
Previous:From: Gaetano MendolaDate: 2004-08-19 20:57:58
Subject: Re: Postgresql 8.0 beta 1 - strange cpu usage statistics and slow

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