Search Postgresql Archives

bug with indexing of infinite timestamps?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxxx
http://www.elevated-dev.com/
(303) 722-0567 voice






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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux