Thread

1. seq scan on a table whereas an index exist

   Nicolas GIMMILLARO <nicolas.gimmillaro@wmi.fr> — 2001-03-06T11:22:43Z

   I don't understand the query plan used to resolve this query :
   select * from web_access where date between '01/01/2001' and
   '06/03/2001'
   
   The web_access table as this structure :
   
            Table "web_access"
    Attribute |     Type     | Modifier
   -----------+--------------+----------
    source    | varchar(128) | not null
    date      | date         |
    heure     | time         |
    action    | varchar(128) |
    code1     | integer      |
    code2     | integer      |
    util      | text         |
    refer     | varchar(128) |
    browser   | varchar(20)  |
    brorel    | varchar(10)  |
    os        | varchar(20)  |
    osrel     | varchar(10)  |
   Indices: web_access_date,
            web_access_source
   
   Index "web_access_date"
    Attribute | Type
   -----------+------
    date      | date
   btree
   
   A classical select on table web_access uses a seq scan :
   -----------------------------------------------------------------------------------------------------------
   
   explain select * from web_access where date between '01/01/2001' and
   '06/03/2001';
   NOTICE:  QUERY PLAN:
   
   Seq Scan on web_access  (cost=0.00..35380.36 rows=117694 width=116)
   -----------------------------------------------------------------------------------------------------------
   
   The same query with 2 sub-queries uses an index scan :
   -----------------------------------------------------------------------------------------------------------
   
   explain select * from web_access where date between (select
   '01/01/2001'::date)  and (select '06/03/2001'::date);
   NOTICE:  QUERY PLAN:
   
   Index Scan using web_access_date on web_access  (cost=0.00..26858.67
   rows=7908 width=116)
     InitPlan
       ->  Result  (cost=0.00..0.00 rows=0 width=0)
       ->  Result  (cost=0.00..0.00 rows=0 width=0)
   -----------------------------------------------------------------------------------------------------------
   
   
   
   What's wrong with my "select * from web_access where date between
   '01/01/2001' and '06/03/2001' "  query ?
   
   Nicolas GIMMILLARO
   
   
   

2. Re: seq scan on a table whereas an index exist

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-08T02:07:04Z

   Nicolas GIMMILLARO <Nicolas.GIMMILLARO@wmi.fr> writes:
   > What's wrong with my "select * from web_access where date between
   > '01/01/2001' and '06/03/2001' "  query ?
   
   Nothing.  The planner is estimating that this query will retrieve a
   sufficiently large fraction of the rows in the table that a seqscan
   will be cheaper than an indexscan.  That estimate might or might not
   be correct, but it's operating as designed.
   
   The form with the subselects confuses the planner enough that it falls
   back on default selectivity estimates, which do lead it to choose an
   indexscan.
   
   			regards, tom lane