From: anthony.shipman@symstream.com
To: pgsql-performance@postgresql.org
Subject: overzealous sorting?
Date: Mon, 26 Sep 2011 16:28:15 +1000
User-Agent: KMail/1.9.4
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201109261628.15363.anthony.shipman@symstream.com>

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.