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
[email protected]
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
