Search Postgresql Archives

Difference in indexes

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

 



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

[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