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