Indexes not used in 7.1RC4: Bug? - Mailing list pgsql-general

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)