Thread: Questions with the planner
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=# --------------------------------------------------------------------------
I experienced the same thing. On the same platform. Any calculation on the timestamp caused the planner to not use the index on the timestamp field and fall back to a seqscan of the table. If I just put a timestamp value there (no calculation) it used the index. I tried casting the results of the calc to timestamp too and that did not help. I resorted to calculating the dates in my code and just using a date with no calculation in the query. Anyone know if this has been fixed on 7.2? > > 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. [snip] > 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... [snip]