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
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 |