Questions with the planner - Mailing list pgsql-general

I was noticing something odd about the query planner.  I'm using
postgresql-7.1.3-2 so if this has been fixed in a more recent version
please let me know...

Ok here's a plan for my query.  The meat is not so import its just that
I am pulling data out of a really big table from March 11th to present.
Now -- the query planner does the smart thing and uses the date index.
So far so good.

--------------------------------------------------------------------------
tcbase=# explain SELECT * FROM transdata a join status_info using (status)
join billingdata b using (billingid) WHERE a.custid = 1 AND a.trans_date >=
timestamp '03-11-2002' ORDER BY a.trans_date;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..1664.98 rows=1 width=62)
  ->  Nested Loop  (cost=0.00..1662.94 rows=1 width=46)
        ->  Index Scan using transdata_date_index on transdata a
(cost=0.00..1661.81 rows=1 width=30)
        ->  Seq Scan on status_info  (cost=0.00..1.06 rows=6 width=16)
  ->  Index Scan using billingdata_pkey on billingdata b  (cost=0.00..2.02
rows=1 width=16)

EXPLAIN
--------------------------------------------------------------------------

Now the only difference here is that I add "+ 0" to the rval of the date.
This makes the database lose its ability to use the date index.  In this
case it fails over to a much less useful index.  But the question being...
why does it not use the index here.  One thing that crossed my mind was that
perhaps the addition makes it into a Date type... well casting it back to
Timestamp fixes nothing.  Once I arithmetic on the timestamp I just cant
find any way to use the index.  Maybe it's the math that messes it up...
for reason thinking the result of a "+" is not cachable. ( yes I'm sure I
can get around this by making a "iscachable" function and passing the
timestamp through that...

--------------------------------------------------------------------------
tcbase=# explain SELECT * FROM transdata a join status_info using (status)
join billingdata b using (billingid) WHERE a.custid = 1 AND a.trans_date >=
timestamp '03-11-2002' + 0 ORDER BY a.trans_date;
NOTICE:  QUERY PLAN:

Sort  (cost=15566.24..15566.24 rows=1 width=62)
  ->  Nested Loop  (cost=0.00..15566.23 rows=1 width=62)
        ->  Nested Loop  (cost=0.00..15564.19 rows=1 width=46)
              ->  Index Scan using transdata_custid_index on transdata a
(cost=0.00..15563.06 rows=1 width=30)
              ->  Seq Scan on status_info  (cost=0.00..1.06 rows=6 width=16)
        ->  Index Scan using billingdata_pkey on billingdata b
(cost=0.00..2.02 rows=1 width=16)

EXPLAIN
--------------------------------------------------------------------------

Just to test my theroy I add 0 to the custid as well.  This does not change
the query plan.  So for some reason adding 0 to a timestamp makes the index
invalid and adding 0 to an integer does nothing.  This has GOT to be a
bug-not-afeature.  Right?

--------------------------------------------------------------------------
tcbase=# explain SELECT * FROM transdata a join status_info using (status)
join billingdata b using (billingid) WHERE a.custid = 1 + 0 AND
a.trans_date >= timestamp '03-11-2002' + 0 ORDER BY a.trans_date;
NOTICE:  QUERY PLAN:

Sort  (cost=15566.24..15566.24 rows=1 width=62)
  ->  Nested Loop  (cost=0.00..15566.23 rows=1 width=62)
        ->  Nested Loop  (cost=0.00..15564.19 rows=1 width=46)
              ->  Index Scan using transdata_custid_index on transdata a
(cost=0.00..15563.06 rows=1 width=30)
              ->  Seq Scan on status_info  (cost=0.00..1.06 rows=6 width=16)
        ->  Index Scan using billingdata_pkey on billingdata b
(cost=0.00..2.02 rows=1 width=16)

EXPLAIN
tcbase=#
--------------------------------------------------------------------------