another "EXPLAIN -- NO INDEX?" question
От | will trillich |
---|---|
Тема | another "EXPLAIN -- NO INDEX?" question |
Дата | |
Msg-id | 20020210085459.A23651@serensoft.com обсуждение исходный текст |
Ответы |
Re: another "EXPLAIN -- NO INDEX?" question
Re: another "EXPLAIN -- NO INDEX?" question |
Список | 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@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!
В списке pgsql-general по дате отправления: