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
