bug with indexing of infinite timestamps?

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=20 loops=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.954 rows=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'm just 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 value further in the past than yesterday, leaving just 20 at infinity being the only ones matched by the query. That update had 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.025 rows=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'::timestamp with 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general