Difference in indexes

From: "A(dot)j(dot) Langereis" <a(dot)j(dot)langereis(at)inter(dot)nl(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Difference in indexes
Date: 2005-11-21 23:17:38
Message-ID: 029a01c5eef1$c3d14cd0$3e01a8c0@aarjan2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Qingqing Zhou 2005-11-21 23:38:07 Re: Difference in indexes
Previous Message Geert Jansen 2005-11-21 23:13:27 Re: Weird results when using schemas