Thread: 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) > 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) 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
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/ ...