In Mammoth Replicator (PG 8.3) I have a table described as
Table "public.tevent_cdr"
Column | Type | Modifiers
----------------+--------------------------+------------------------------------------------------------
event_id | integer | not null default
nextval(('event_id_seq'::text)::regclass)
timestamp | timestamp with time zone | not null
classification | character varying | not null
area | character varying | not null
kind | character varying |
device_id | integer |
device_name | character varying |
fleet_id | integer |
fleet_name | character varying |
customer_id | integer |
customer_name | character varying |
event | text |
Indexes:
"tevent_cdr_event_id" UNIQUE, btree (event_id)
"tevent_cdr_timestamp" btree ("timestamp")
Check constraints:
"tevent_cdr_classification_check" CHECK (classification::text
= 'cdr'::text)
Inherits: tevent
This simple query puzzles me. Why does it need to sort the records? Don't they
come from the index in order?
"explain analyze select * from tevent_cdr where timestamp >=
'2011-09-09 12:00:00.000000+0' and timestamp < '2011-09-09
13:00:00.000000+0' and classification = 'cdr' order by timestamp;"
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=9270.93..9277.12 rows=2477 width=588) (actual
time=9.219..11.489 rows=2480 loops=1)
Sort Key: "timestamp"
Sort Method: quicksort Memory: 2564kB
-> Bitmap Heap Scan on tevent_cdr (cost=57.93..9131.30 rows=2477
width=588) (actual time=0.440..3.923 rows=2480 loops=1)
Recheck Cond: (("timestamp" >= '2011-09-09
22:00:00+10'::timestamp with time zone) AND ("timestamp" < '2011-09-09
23:00:00+10'::timestamp with time zone))
Filter: ((classification)::text = 'cdr'::text)
-> Bitmap Index Scan on tevent_cdr_timestamp
(cost=0.00..57.31 rows=2477 width=0) (actual time=0.404..0.404 rows=2480
loops=1)
Index Cond: (("timestamp" >= '2011-09-09
22:00:00+10'::timestamp with time zone) AND ("timestamp" < '2011-09-09
23:00:00+10'::timestamp with time zone))
Total runtime: 13.847 ms
(9 rows)
--
Anthony Shipman | flailover systems: When one goes down it
Anthony.Shipman@symstream.com | flails about until the other goes down too.