bug with indexing of infinite timestamps? - Mailing list pgsql-general

I hit this peculiar result last night with 9.1.3 (Intel, 64-bit). Check out the query and the explain analyze results:

explain analyze select count(*) from page_log where end_when > current_date - 1 and succeeded = 't';

                                                                    QUERY PLAN
                           

---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=380325.72..380325.73 rows=1 width=0) (actual time=312.169..312.169 rows=1 loops=1)
   ->  Bitmap Heap Scan on page_log  (cost=5445.24..379741.58 rows=233654 width=0) (actual time=307.596..312.160
rows=20loops=1) 
         Recheck Cond: (end_when > (('now'::text)::date - 1))
         Filter: succeeded
         ->  Bitmap Index Scan on page_log__end_pager  (cost=0.00..5386.83 rows=233989 width=0) (actual
time=278.954..278.954rows=287118 loops=1) 
               Index Cond: (end_when > (('now'::text)::date - 1))

287,118 rows found via the index scan, 20 rows after the recheck. There were no other transactions open at the time.
(I'mjust running some tests on my personal machine.) Analyzing the table did not change the results (as I expected). 

So there had originally been 287,118 rows with an end_when value of 'infinity', then I updated 287,098 of them to some
valuefurther in the past than yesterday, leaving just 20 at infinity being the only ones matched by the query. That
updatehad finished about an hour before I got the above result--but bear in mind that this db is running on a crappy
disk.

Reindexing the table fixed the issue:

                                                                QUERY PLAN
                   

-------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=378380.91..378380.92 rows=1 width=0) (actual time=0.059..0.059 rows=1 loops=1)
   ->  Bitmap Heap Scan on page_log  (cost=5371.15..377803.88 rows=230815 width=0) (actual time=0.031..0.056 rows=20
loops=1)
         Recheck Cond: (end_when > (('now'::text)::date - 1))
         Filter: succeeded
         ->  Bitmap Index Scan on page_log__end_pager  (cost=0.00..5313.45 rows=231146 width=0) (actual
time=0.025..0.025rows=20 loops=1) 
               Index Cond: (end_when > (('now'::text)::date - 1))

So are the 'infinite' values somehow "sticky" in the index??? The table definition is simple:

\d page_log
                                Table "public.page_log"
   Column    |           Type           |                  Modifiers
-------------+--------------------------+----------------------------------------------
 id          | bigint                   | not null default nextval('rowids'::regclass)
 ip          | character varying        | not null
 pager_num   | character varying        | not null
 message     | character varying        | not null
 succeeded   | boolean                  | not null
 user__id    | bigint                   |
 by_group    | boolean                  | not null
 by_schedule | boolean                  | not null
 start_when  | timestamp with time zone | not null
 end_when    | timestamp with time zone | not null
 request__id | bigint                   |
Indexes:
    "page_log_pkey" PRIMARY KEY, btree (id)
    "page_count__2012_12_28" btree (start_when) WHERE succeeded = true AND start_when >= '2012-12-28
00:00:00-07'::timestampwith time zone 
    "page_log__end" btree (end_when, succeeded)
    "page_log__end_pager" btree (end_when, pager_num)
    "page_log__pager_end" btree (pager_num, end_when)
    "page_log__start_ok" btree (start_when, succeeded)
Check constraints:
    "page_log_ip_check" CHECK (btrim(ip::text) <> ''::text)
    "page_log_message_check" CHECK (btrim(message::text) <> ''::text)
    "page_log_pager_num_check" CHECK (btrim(pager_num::text) <> ''::text)
Foreign-key constraints:
    "page_log_request__id_fkey" FOREIGN KEY (request__id) REFERENCES page_requests(id)
    "page_log_user__id_fkey" FOREIGN KEY (user__id) REFERENCES users(id) DEFERRABLE INITIALLY DEFERRED

(The currently-defined indexes are a little overlapping, because I'm experimenting with various queries and
performance.)

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice