Indexes not used in 7.1RC4: Bug?

Hi,

I have the following table, containing about 570000 Rows, but some
indexes are not used, on 7.1RC4, freshly vacuumed (analyse). It was the
same at least in 7.1RC1

--- SNIP ---

CREATE TABLE access_log(
site_id int2 NOT NULL DEFAULT 0,
access_time timestamp NOT NULL DEFAULT NOW(),
time_taken interval NOT NULL,
remote_ip inet NOT NULL,
method_num int2 NOT NULL,
url_id int4 NOT NULL REFERENCES urls(id),
referer_id int4 REFERENCES referer(id),
browser_id int4 REFERENCES browser(id),
status int2 NOT NULL DEFAULT 0,
bytes int4 NOT NULL DEFAULT 0,
content_id int2 NOT NULL REFERENCES content_types(id),

https_flag boolean NOT NULL DEFAULT 'f',

session_id char(32),
user_id int4 REFERENCES users(id),
uname varchar(255),

note_id int4

);

CREATE INDEX site_idx ON access_log(site_id);
CREATE INDEX access_time_idx ON access_log(access_time);
CREATE INDEX time_taken_idx ON access_log(time_taken);
CREATE INDEX remote_ip_idx ON access_log(remote_ip);
CREATE INDEX method_idx ON access_log(method_num);
CREATE INDEX url_idx ON access_log(url_id);
CREATE INDEX referer_idx ON access_log(referer_id);
CREATE INDEX browser_idx ON access_log(browser_id);
CREATE INDEX status_idx ON access_log(status);
CREATE INDEX bytes_idx ON access_log(bytes);
CREATE INDEX content_idx ON access_log(content_id);
CREATE INDEX https_idx ON access_log(https_flag);
CREATE INDEX session_idx ON access_log(session_id);
CREATE INDEX user_id_idx ON access_log(user_id);
CREATE INDEX user_idx ON access_log(uname);
CREATE INDEX note_idx ON access_log(note_id);

--- SNAP ---

url_idx seems OK:

logger=# EXPLAIN SELECT * FROM access_log WHERE url_id = 1000;
Index Scan using url_idx on access_log
(cost=0.00..3618.92 rows=1002 width=89)

But the others not:

logger=# EXPLAIN SELECT * FROM access_log WHERE method_num = 0;
Seq Scan on access_log (cost=0.00..16443.71 rows=559371 width=89)

logger=# EXPLAIN SELECT * FROM access_log WHERE browser_id = 500;
Seq Scan on access_log (cost=0.00..16443.71 rows=7935 width=89)

logger=# EXPLAIN SELECT * FROM access_log WHERE content_id = 20;
Seq Scan on access_log (cost=0.00..16443.71 rows=20579 width=89)

....

And very strange:

logger=# EXPLAIN SELECT * FROM access_log WHERE access_time >
'2001-04-10 10:10:10';
Index Scan using access_time_idx on access_log
(cost=0.00..10605.12 rows=3251 width=89)

logger=# EXPLAIN SELECT * FROM access_log WHERE access_time >
'2001-04-08 10:10:10';
Seq Scan on access_log (cost=0.00..16443.71 rows=152292 width=89)

Indexes are also not used in Subselects:

logger=# EXPLAIN SELECT * FROM access_log WHERE url_id in (1);
Index Scan using url_idx on access_log
(cost=0.00..3618.92 rows=1002 width=89)

logger=# EXPLAIN SELECT * FROM access_log WHERE url_id in (1,2,3);
Index Scan using url_idx, url_idx, url_idx on access_log
(cost=0.00..10871.79 rows=3000 width=89)

But:

logger=# EXPLAIN SELECT * FROM access_log
WHERE url_id IN (SELECT 1);
Seq Scan on access_log (cost=0.00..16443.71 rows=572537 width=89)
SubPlan
-> Materialize (cost=0.00..0.00 rows=0 width=0)
-> Result (cost=0.00..0.00 rows=0 width=0)

Indexes are also not used for remote_ip, ORDER BY access_time
(timestamp), ORDER BY time_taken (interval), status, method_num etc. The
only I found where indexes are used is url_id!

hmmm, any hints? Bug?

Postgres configuration is default for all optimizations (geqo_...);
others:

sort_mem = 1024
shared_buffers = 512

Tested on Linux and Win2K/Cygwin.

For the hackers: explain verbose follows:

logger=# EXPLAIN VERBOSE SELECT * FROM access_log WHERE url_id = 1000;
NOTICE: QUERY DUMP:

{ INDEXSCAN :startup_cost 0.00 :total_cost 3618.92 :rows 1002 :width 89
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 21
:restypmod -1 :resname site_id :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 21 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 1184 :restypmod -1 :resname access_time :reskey
0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 2 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 2}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1186
:restypmod -1 :resname time_taken :reskey 0 :reskeyop 0 :ressortgroupref
0 :resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 1186
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3}} { TARGETENTRY
:resdom { RESDOM :resno 4 :restype 869 :restypmod -1 :resname remote_ip
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 4 :vartype 869 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 5
:restype 21 :restypmod -1 :resname method_num :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 5
:vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 5}} {
TARGETENTRY :resdom { RESDOM :resno 6 :restype 23 :restypmod -1 :resname
url_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
VAR :varno 1 :varattno 6 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 6}} { TARGETENTRY :resdom { RESDOM :resno 7
:restype 23 :restypmod -1 :resname referer_id :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 7
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7}} {
TARGETENTRY :resdom { RESDOM :resno 8 :restype 23 :restypmod -1 :resname
browser_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 8 :vartype 23 :vartypmod -1 :varlevelsup
0 :varnoold 1 :varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 9
:restype 21 :restypmod -1 :resname status :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 9
:vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 9}} {
TARGETENTRY :resdom { RESDOM :resno 10 :restype 23 :restypmod -1
:resname bytes :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 10 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 10}} { TARGETENTRY :resdom {
RESDOM :resno 11 :restype 21 :restypmod -1 :resname content_id :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 11 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 11}} { TARGETENTRY :resdom { RESDOM :resno 12 :restype 16
:restypmod -1 :resname https_flag :reskey 0 :reskeyop 0 :ressortgroupref
0 :resjunk false } :expr { VAR :varno 1 :varattno 12 :vartype 16
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY
:resdom { RESDOM :resno 13 :restype 1042 :restypmod 36 :resname
session_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 13 :vartype 1042 :vartypmod 36
:varlevelsup 0 :varnoold 1 :varoattno 13}} { TARGETENTRY :resdom {
RESDOM :resno 14 :restype 23 :restypmod -1 :resname user_id :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 14 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 14}} { TARGETENTRY :resdom { RESDOM :resno 15 :restype 1043
:restypmod 259 :resname uname :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 15 :vartype 1043
:vartypmod 259 :varlevelsup 0 :varnoold 1 :varoattno 15}} { TARGETENTRY
:resdom { RESDOM :resno 16 :restype 23 :restypmod -1 :resname note_id
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 16 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 16}}) :qpqual <> :lefttree <> :righttree <>
:extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid (
1870492) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER :opno
96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 6} {
CONST :consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ -24 3 0 0 ] })})) :indxqualorig (({ EXPR :typeOid 16
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({
VAR :varno 1 :varattno 6 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 6} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue 4 [ -24 3 0 0 ] })})) :indxorderdir
1 }
NOTICE: QUERY PLAN:

Index Scan using url_idx on access_log (cost=0.00..3618.92 rows=1002
width=89)

EXPLAIN

logger=# EXPLAIN VERBOSE SELECT * FROM access_log WHERE referer_id =
1000;
NOTICE: QUERY DUMP:

{ SEQSCAN :startup_cost 0.00 :total_cost 16443.71 :rows 11715 :width 89
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 21
:restypmod -1 :resname site_id :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 21 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 1184 :restypmod -1 :resname access_time :reskey
0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 2 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 2}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1186
:restypmod -1 :resname time_taken :reskey 0 :reskeyop 0 :ressortgroupref
0 :resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 1186
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3}} { TARGETENTRY
:resdom { RESDOM :resno 4 :restype 869 :restypmod -1 :resname remote_ip
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 4 :vartype 869 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 5
:restype 21 :restypmod -1 :resname method_num :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 5
:vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 5}} {
TARGETENTRY :resdom { RESDOM :resno 6 :restype 23 :restypmod -1 :resname
url_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
VAR :varno 1 :varattno 6 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 6}} { TARGETENTRY :resdom { RESDOM :resno 7
:restype 23 :restypmod -1 :resname referer_id :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 7
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7}} {
TARGETENTRY :resdom { RESDOM :resno 8 :restype 23 :restypmod -1 :resname
browser_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 8 :vartype 23 :vartypmod -1 :varlevelsup
0 :varnoold 1 :varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 9
:restype 21 :restypmod -1 :resname status :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 9
:vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 9}} {
TARGETENTRY :resdom { RESDOM :resno 10 :restype 23 :restypmod -1
:resname bytes :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 10 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 10}} { TARGETENTRY :resdom {
RESDOM :resno 11 :restype 21 :restypmod -1 :resname content_id :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 11 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 11}} { TARGETENTRY :resdom { RESDOM :resno 12 :restype 16
:restypmod -1 :resname https_flag :reskey 0 :reskeyop 0 :ressortgroupref
0 :resjunk false } :expr { VAR :varno 1 :varattno 12 :vartype 16
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 12}} { TARGETENTRY
:resdom { RESDOM :resno 13 :restype 1042 :restypmod 36 :resname
session_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 13 :vartype 1042 :vartypmod 36
:varlevelsup 0 :varnoold 1 :varoattno 13}} { TARGETENTRY :resdom {
RESDOM :resno 14 :restype 23 :restypmod -1 :resname user_id :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 14 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 14}} { TARGETENTRY :resdom { RESDOM :resno 15 :restype 1043
:restypmod 259 :resname uname :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 15 :vartype 1043
:vartypmod 259 :varlevelsup 0 :varnoold 1 :varoattno 15}} { TARGETENTRY
:resdom { RESDOM :resno 16 :restype 23 :restypmod -1 :resname note_id
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 16 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 16}}) :qpqual ({ EXPR :typeOid 16 :opType op
:oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 7 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 7} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue 4 [ -24 3 0 0 ] })}) :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 }
NOTICE: QUERY PLAN:

Seq Scan on access_log (cost=0.00..16443.71 rows=11715 width=89)

EXPLAIN

Thanks & Ciao

Alvar

--
AGI
Magirusstrasse 21B, 70469 Stuttgart
Fon +49 (0)711.228 74-50, Fax +49 (0)711.228 74-88
+++news+++news+++news+++
Beste Image-Website 2001 kommt von AGI
http://www.agi.de/tagebuch
http://www.agi.com/diary (english)