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