Search Postgresql Archives

Re: Help with an index and the optimizer

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I am using pg 8.1.4, I have a table with 1.1 million rows of data (see below for table definition). One field state is numeric and has an index. The index is not always picked up when searching the table by state only and I can't figure out why.
 
So:
 
SELECT * FROM STUFF WHERE state=12;   --causes a seq scan of the table
 
where
 
SELECT * FROM STUFF WHERE state=16  --Uses the index.
 
 
I have run Analyze on the table as well as vacuumed it and reindexed it. At first I thought it might be a type mismatch but forcing the number to numeric (i.e cast(12 as numeric(2,0)) doesn't change the behavior. However setting the enable_seqscan=off does force both queries to use the index. Using the index in all cases is faster than a seq scan according to explain analyze.
 
 
Any thoughts on how to get the optimizer to pick up the index at all times? I am desperate for fresh ideas.
 
Thanks,
 
Rob.
 
 
Table/index definitions:


CREATE TABLE stuff(
  id serial NOT NULL,
  module character(8),
  tlid numeric(10),
  dirp character(2),
  name character(30),
  type character(4),
  dirs character(2),
  zip numeric(5),
  state numeric(2),
  county numeric(3),
  CONSTRAINT stuff_pk PRIMARY KEY (id),
)
WITHOUT OIDS;

 

CREATE INDEX ndx_cc_state
  ON stuff
  USING btree
  (state);


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux