Thread: Index Scans Oddness

Index Scans Oddness

From
Orion
Date:

I posted two things I thought might be bugs about an hour ago with
postgresql-7.1.3-1PGDG.  I just ran a regression test with postgresql-7.0.3
and one problem vanished and one stayed.

(yes I vacuum analyzed)

1) the

        select * from foo where text_field like 'foo%'

will do an index scan with my data in 7.0.3 but not 7.1.3-1PGDG.
Makes me think there's a bug.

2) the

        select * from foo where order_date = current_date
and
        select * from foo
        where order_date < to_date(xxx) and order_date > to_date(yyy)

Both do sequence scans where current date is indexed on both 7.0.3 and
7.1.3.  This leaves me quite confused since there are 300,000 rows and a
sequential scan is doomed to take 15 to 20 seconds.  The few queries that
do use an index (indexed off of last_name for instance) come back in 1 or
2 seconds.

Am I doing something wrong?  The data is the same type so I don't need to
cast, right?  What other tricks are there to get the query planner to
be smart?

        Orion

Here's my exact table structure:

fdb=# \d mfps_orderinfo_435
        Table "mfps_orderinfo_435"
      Attribute      |  Type   | Modifier
---------------------+---------+----------
 order_number        | integer | not null
 source_code         | text    |
 last_name           | text    |
 first_name          | text    |
 title               | text    |
 address1            | text    |
 address2            | text    |
 city                | text    |
 state               | text    |
 zip                 | text    |
 telephone           | text    |
 bill_method         | text    |
 cc                  | text    |
 exp                 | text    |
 cc_auth_code        | text    |
 multi_billing_code  | text    |
 order_header_status | text    |
 order_date          | date    |
 ship_date           | date    |
 total_quantity      | integer |
 order_extension     | money   |
 sales_tax           | money   |
 shipping            | money   |
 total_discount      | money   |
 return_quantity     | integer |
 return_amount       | money   |
 num_billings        | integer |
 tracking_no1        | text    |
 tracking_no2        | text    |
 tracking_no3        | text    |
 email               | text    |
 amount_paid         | money   |
Indices: mfps_orderinfo_435_fname,
         mfps_orderinfo_435_lname,
         mfps_orderinfo_435_odate,
         mfps_orderinfo_435_pkey

fdb=# \d mfps_orderinfo_435_odate
Index "mfps_orderinfo_435_odate"
 Attribute  | Type
------------+------
 order_date | date
btree

fdb=# explain SELECT * FROM mfps_orderinfo_435 WHERE order_date =
current_date;
NOTICE:  QUERY PLAN:

Seq Scan on mfps_orderinfo_435  (cost=0.00..14272.07 rows=1340 width=288)

EXPLAIN
fdb=# explain SELECT count(*) FROM mfps_orderinfo_435 WHERE order_date >=
to_date('2001-05-01','YYYY-MM-DD') AND order_date <=
to_date('2001-10-10','YYYY-MM-DD');
NOTICE:  QUERY PLAN:

Aggregate  (cost=15115.73..15115.73 rows=1 width=4)
  ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..15031.36 rows=33746
width=4)

EXPLAIN
fdb=# explain verbose SELECT count(*) FROM mfps_orderinfo_435 WHERE
order_date >= to_date('2001-05-01','YYYY-MM-DD') AND order_date <=
to_date('2001-10-10','YYYY-MM-DD');
NOTICE:  QUERY DUMP:

{ AGG :startup_cost 15115.73 :total_cost 15115.73 :rows 1 :width 4 :state
<> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname count :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { AGGREG :aggname count :basetype 0 :aggtype 23
:target { CONST :consttype 23 :constlen 4 :constisnull false :constvalue  4
[ 1 0 0 0 ]  :constbyval true } :usenulls false :aggstar true :aggdistinct
false }}) :qpqual <> :lefttree { SEQSCAN :startup_cost 0.00 :total_cost
15031.36 :rows 33746 :width 4 :state <> :qptargetlist <> :qpqual ({ EXPR
:typeOid 16  :opType op :oper { OPER :opno 1098 :opid 1090 :opresulttype 16
} :args ({ VAR :varno 1 :varattno 18 :vartype 1082 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 18} { EXPR :typeOid 1082  :opType
func :oper { FUNC :funcid 1780 :functype 1082 :funcisindex false :funcsize
0  :func_fcache @ 0x0 :func_tlist ({ TARGETENTRY :resdom { RESDOM :resno 1
:restype 1082 :restypmod -1 :resname \<noname> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno -1 :varattno 1
:vartype 1082 :vartypmod -1  :varlevelsup 0 :varnoold -1 :varoattno 1}})
:func_planlist <>} :args ({ CONST :consttype 25 :constlen -1 :constisnull
false :constvalue  14 [ 14 0 0 0 50 48 48 49 45 48 53 45 48 49 ]
:constbyval false } { CONST :consttype 25 :constlen -1 :constisnull false
:constvalue  14 [ 14 0 0 0 89 89 89 89 45 77 77 45 68 68 ]  :constbyval
false })})} { EXPR :typeOid 16  :opType op :oper { OPER :opno 1096 :opid
1088 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 18 :vartype 1082 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 18} { EXPR :typeOid 1082  :opType func :oper { FUNC :funcid 1780
:functype 1082 :funcisindex false :funcsize 0  :func_fcache @ 0x0
:func_tlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1082
:restypmod -1 :resname \<noname> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno -1 :varattno 1 :vartype 1082 :vartypmod
-1  :varlevelsup 0 :varnoold -1 :varoattno 1}}) :func_planlist <>} :args ({
CONST :consttype 25 :constlen -1 :constisnull false :constvalue
 14 [ 14 0 0 0 50 48 48 49 45 49 48 45 49 48 ]  :constbyval false } { CONST
:consttype 25 :constlen -1 :constisnull false :constvalue  14 [ 14 0 0 0 89
89 89 89 45 77 77 45 68 68 ]  :constbyval false })})}) :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1 }
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 }
NOTICE:  QUERY PLAN:

Aggregate  (cost=15115.73..15115.73 rows=1 width=4)
  ->  Seq Scan on mfps_orderinfo_435  (cost=0.00..15031.36 rows=33746
width=4)

EXPLAIN


Re: Index Scans Oddness

From
Tom Lane
Date:
Orion <o2@trustcommerce.com> writes:
>         select * from foo where text_field like 'foo%'
> will do an index scan with my data in 7.0.3 but not 7.1.3-1PGDG.
> Makes me think there's a bug.

Yes: in 7.0.  You're probably running in a non-C locale, wherein
optimizing LIKE with an indexscan is unsafe.

>         select * from foo where order_date = current_date
> and
>         select * from foo
>         where order_date < to_date(xxx) and order_date > to_date(yyy)

> Both do sequence scans where current date is indexed

The problem is probably lack of cachability of current_date and to_date().
You can fix this with a wrapper function that's marked "iscachable".
Look in the mailing list archives for prior discussions.

            regards, tom lane