overzealous sorting?
От | anthony.shipman@symstream.com |
---|---|
Тема | overzealous sorting? |
Дата | |
Msg-id | 201109261628.15363.anthony.shipman@symstream.com обсуждение исходный текст |
Ответы |
Re: overzealous sorting?
|
Список | pgsql-performance |
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.
В списке pgsql-performance по дате отправления: