optimizer ignoring primary key and doing sequence scan

"Chris Hoy" <chris.hoy@xxxxxxxxxxxxxxxx> · Mon, 14 Jul 2008 20:54:31 +0100

Hi

I have a number of tables in my database where the queries appear to
ignoring the primary key and doing a seq scan instead, however other tables
appear to be fine. I can see any difference between them.

Is their any way of determination why the otimizer isn't picking up the
primary key?

Version 8.3.3 windows

An example of a non working table is:

select * from industries where industryid = 1;
"Seq Scan on industries  (cost=0.00..1.02 rows=1 width=116) (actual
time=0.011..0.013 rows=1 loops=1)"

CREATE TABLE industries
(
  industryid serial NOT NULL,
  industryname character varying(45) NOT NULL,
  prn_key integer,
  CONSTRAINT pk_industry PRIMARY KEY (industryid),
  CONSTRAINT un_industry UNIQUE (industryname)
)

A example of woring one

explain select * from currencies where currencyid = 1
"Index Scan using pk_currencyid on currencies  (cost=0.00..8.27 rows=1
width=196)"
CREATE TABLE currencies
(
  currencyid serial NOT NULL,
  currencyname character varying(85) NOT NULL,
  prn_key integer,
  CONSTRAINT pk_currencyid PRIMARY KEY (currencyid)
)

Chris

No virus found in this outgoing message.
Checked by AVG - http://www.avg.com
Version: 8.0.138 / Virus Database: 270.4.10/1551 - Release Date: 14/07/2008
06:49