another "EXPLAIN -- NO INDEX?" question

From: will trillich <will(at)serensoft(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: another "EXPLAIN -- NO INDEX?" question
Date: 2002-02-10 14:54:59
Message-ID: 20020210085459.A23651@serensoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

okay, i've seen tom's discourse on the explain feature at
http://www.ca.postgresql.org/users-lounge/docs/7.0/user/c4884.htm
but i can't grok why this index is ignored:

create table servers (
id serial,
name varchar(80),
primary key ( id )
);

insert into servers(name)values('serensoft.com');
insert into servers(name)values('dontUthink.com');
insert into servers(name)values('midwestRepo.com');

create table hits (
at timestamp default now(),
client inet,
server integer references servers ( id ),
url varchar(255),
referer varchar(255),
primary key ( server, at )
);

-- insert thousands of records into hits() table
-- with references for servers.id set properly

hits=# explain
hits-# select * from hits where server = 3;

NOTICE: QUERY PLAN:
Seq Scan on hits (cost=0.00..7870.51 rows=10301 width=90)
EXPLAIN

hits=# explain
hits-# select * from hits where server = 1;

NOTICE: QUERY PLAN:
Seq Scan on hits (cost=0.00..7870.51 rows=10301 width=90)
EXPLAIN

hits=# explain
hits-# select * from hits where (server = 1 or server = 3);

NOTICE: QUERY PLAN:
Seq Scan on hits (cost=0.00..8537.01 rows=20205 width=90)
EXPLAIN

hits=# select count(*) from hits;
count
--------
266611
(1 row)

hits=# select count(*) from hits where (server=1 or server=3);
count
-------
3678
(1 row)

hits=# SHOW enable_indexscan ;

NOTICE: enable_indexscan is on
SHOW VARIABLE

--------------------

with 1.5% (3.7k of 267k) filtered, shouldn't it use the index?

$ psql -V
psql (PostgreSQL) 7.1
contains readline, history, multibyte support
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996 Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.

--
Legalize Liberty.

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2002-02-10 16:08:14 Re: what's the meaning of the word "Tioga" in source
Previous Message Mayan 2002-02-10 13:34:12 I found it, I FOUND IT!!