another "EXPLAIN -- NO INDEX?" question
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!
On Sun, 2002-02-10 at 09:54, will trillich wrote:
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
Keep in mind you're using old docs...
but i can't grok why this index is ignored:
Have you VACUUM ANALYZE'd or ANALYZE'd the tables in question?
Cheers,
Neil
--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC
Try 7.2, and don't forget to VACUUM ANALYZE after you've loaded the
data.
regards, tom lane
On Sun, Feb 10, 2002 at 11:29:57AM -0500, Tom Lane wrote:
Try 7.2, and don't forget to VACUUM ANALYZE after you've loaded the
data.
i'm stuck with 7.1 for now, and i do the vacuum analyze
nightly... any other ideas?
---------
create index hits_by_server on hits(server);
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)
--
Legalize Liberty.
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
On Sun, 10 Feb 2002, will trillich wrote:
On Sun, Feb 10, 2002 at 11:29:57AM -0500, Tom Lane wrote:
Try 7.2, and don't forget to VACUUM ANALYZE after you've loaded the
data.i'm stuck with 7.1 for now, and i do the vacuum analyze
nightly... any other ideas?---------
create index hits_by_server on hits(server);
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)
EXPLAINhits=# select count(*) from hits;
count
--------
266611
(1 row)hits=# select count(*) from hits where (server=1 or server=3);
count
-------
3678
(1 row)
Is there one value that's more common? It's estimating about 6 times
as many rows as actually match the condition (and 3 times the =1 or =3
real for even just one of them iirc from the first message).
On Sun, Feb 10, 2002 at 10:36:37AM -0800, Stephan Szabo wrote:
On Sun, 10 Feb 2002, will trillich wrote:
i'm stuck with 7.1 for now, and i do the vacuum analyze
nightly... any other ideas?Is there one value that's more common? It's estimating about 6 times
as many rows as actually match the condition (and 3 times the =1 or =3
real for even just one of them iirc from the first message).
yep -- of the more numerous hits, one overshadows them all:
count | name
--------+---------------------------
635 | SECSed.com
4790 | adminComputing.com
35013 | dontUthink.com
35610 | easydxftype.serensoft.com
15010 | flint.dontUthink.com
1826 | pix.dontUthink.com
3668 | www.midwestRepo.com
163629 | www.serensoft.com
still -- if we're looking for a LOW-FREQUENCY item, wouldn't the
index seem a reasonable tool to use? i can see that searching
for 'www.serensoft.com' wouldn't have much (any?) gain from the
index... but 'dontUthink.com' should, no?
--
Legalize Liberty.
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
will trillich <will@serensoft.com> writes:
yep -- of the more numerous hits, one overshadows them all:
[...]
still -- if we're looking for a LOW-FREQUENCY item, wouldn't the
index seem a reasonable tool to use? i can see that searching
for 'www.serensoft.com' wouldn't have much (any?) gain from the
index... but 'dontUthink.com' should, no?
7.1 has pretty primitive statistics; that's why people are suggesting
you upgrade.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
Import Notes
Reply to msg id not found: willtrillich'smessageofSun10Feb2002162950-0600
will trillich <will@serensoft.com> writes:
still -- if we're looking for a LOW-FREQUENCY item,
This is why I advised you to update to 7.2. 7.2 will get this
right, 7.1 will not.
regards, tom lane
On Sun, Feb 10, 2002 at 06:11:00PM -0500, Doug McNaught wrote:
will trillich <will@serensoft.com> writes:
still -- if we're looking for a LOW-FREQUENCY item, wouldn't the
index seem a reasonable tool to use? i can see that searching
for 'www.serensoft.com' wouldn't have much (any?) gain from the
index... but 'dontUthink.com' should, no?7.1 has pretty primitive statistics; that's why people are suggesting
you upgrade.
ah. and here i thought it was a peer-pressure clique thing. :)
i was hoping it was something i'd been doing wrong...
[any chance at getting 7.2 available as a debian-friendly
potato-happy *.deb?]
--
DEBIAN NEWBIE TIP #57 from Steve Kowalik <stevenk@hasnolife.com>
:
Wondering HOW TO SET YOUR TIME ZONE? Your system clock may be
showing UTC or GMT but you want it to display PDT or whatever.
Just run "tzconfig" as root. (You're sure to have it on your
debian system already -- it's provided in package "libc6".)
Also see http://newbieDoc.sourceForge.net/ ...