Skip site navigation (1) Skip section navigation (2)

Index Scans Oddness

From: Orion <o2(at)trustcommerce(dot)com>
To: pgsql-admin(at)postgresql(dot)org(dot)pgsql-bugs(at)postgresql(dot)org(dot)pgsql-general(at)postgresql(dot)org
Subject: Index Scans Oddness
Date: 2001-10-11 21:14:33
Message-ID: 9q51rs$2o4b$1@news.tht.net (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-bugspgsql-general

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


Responses

pgsql-admin by date

Next:From: Rolf L├╝tteckeDate: 2001-10-12 07:31:02
Subject: Re: Alter column / array
Previous:From: Peter EisentrautDate: 2001-10-11 20:03:28
Subject: Re: Permission Denied When i am Trying to take Backup

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2001-10-11 21:31:47
Subject: Re: initdb must have write access to /tmp
Previous:From: Bruce MomjianDate: 2001-10-11 20:38:37
Subject: Re: Server crash caused by CHECK on child

pgsql-general by date

Next:From: Simon CruteDate: 2001-10-11 23:56:05
Subject: Re: error codes when running pg_dumpall from a perl script.
Previous:From: Bruce MomjianDate: 2001-10-11 20:40:18
Subject: Re: [HACKERS] Tupple statistics function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group