Difference in indexes

["A.j. Langereis" <a.j.langereis@xxxxxxxxxxxx>]

Dear all,

 

I'm using a PostgreSQL 8.1.0 dabase on a Fedora Core 3 machine here. In this table there is a table hosts:

 

CREATE TABLE hosts
(
  hostid int4 NOT NULL DEFAULT nextval('hosts_hostid_seq'::regclass),
  hostip cidr NOT NULL,
  hostname varchar(50),
  lastseen timestamp DEFAULT '1970-01-01 01:00:00'::timestamp without time zone,
  total int4 DEFAULT 0,
  image varchar(20) DEFAULT 'hosts/unknown.png'::character varying,
  CONSTRAINT hosts_pkey PRIMARY KEY (hostid)
)
WITHOUT OIDS;
ALTER TABLE hosts OWNER TO root;

 

CREATE INDEX hosts_hostip
  ON hosts
  USING btree
  (hostip);

CREATE INDEX hosts_hostname
  ON hosts
  USING btree
  (hostname);

 

When I run 2 queries on this table:

select * from hosts where hostname='Fabian'

select * from hosts where hostname='Foo'

 

I got 2 differen explain plans:

"Seq Scan on hosts  (cost=0.00..10.25 rows=21 width=59) (actual time=0.048..0.600 rows=21 loops=1)"
"  Filter: ((hostname)::text = 'Fabian'::text)"
"Total runtime: 0.794 ms"

"Index Scan using hosts_hostname on hosts  (cost=0.00..9.04 rows=2 width=59) (actual time=0.057..0.057 rows=0 loops=1)"
"  Index Cond: ((hostname)::text = 'Foo'::text)"
"Total runtime: 0.185 ms"

What is happening here? What am I overlooking? The length does not seem to be the problem: 'FooFooFoo' also uses the index..

Also the fact whenever there are results or not does not seem to influence the planner..

 

Yours,

 

Aarjan Langereis