postgresql

dmzlj — 24.03.2010

                                     Table "public.event"
   Column   |            Type             |                     Modifiers                      
------------+-----------------------------+----------------------------------------------------
 uid        | integer                     | not null default nextval('seq_event_id'::regclass)
 id         | integer                     | not null
 dt         | timestamp without time zone | not null
 type       | integer                     | not null
 ownerphone | integer                     | not null
 lat        | double precision            | 
 lng        | double precision            | 
 fix        | integer                     | not null
 dev        | integer                     | not null default 10
 pl         | integer                     | not null default 0
 sq         | integer                     | not null default 0
 ht         | integer                     | not null default 0
 sa         | integer                     | not null default 0
 td         | integer                     | not null default 0
 sp         | integer                     | not null default 0
Indexes:
    "event_pkey" PRIMARY KEY, btree (uid)
    "event_id_dt" btree (id, dt)
    "event_id_idx" btree (id)
    "event_ts" btree (dt)
Foreign-key constraints:
    "event_fix_fkey" FOREIGN KEY (fix) REFERENCES fix_type(id)
    "event_type_fkey" FOREIGN KEY (type) REFERENCES event_type(id)


test3=# EXPLAIN ANALYZE select id, dt from event where id = 311 and dt > '2010-03-02 00:00:00' and dt < '2010-03-02 23:59:58';
                               QUERY PLAN                                                              
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on event  (cost=1217.27..157002.94 rows=70585 width=12) (actual time=304.872..2267.574 rows=69776 loops=1)
   Recheck Cond: (id = 311)
   Filter: ((dt > '2010-03-02 00:00:00'::timestamp without time zone) AND (dt < '2010-03-02 23:59:58'::timestamp without time zone))
   ->  Bitmap Index Scan on event_id_idx  (cost=0.00..1199.62 rows=70598 width=0) (actual time=258.710..258.710 rows=69824 loops=1)
         Index Cond: (id = 311)
 Total runtime: 2359.037 ms

test3=# EXPLAIN ANALYZE select id, dt from event where id = 511 and dt > '2010-03-02 00:00:00' and dt < '2010-03-02 23:59:58';
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on event  (cost=248.93..46203.60 rows=14293 width=12) (actual time=58.839..27309.695 rows=28078 loops=1)
   Recheck Cond: (id = 511)
   Filter: ((dt > '2010-03-02 00:00:00'::timestamp without time zone) AND (dt < '2010-03-02 23:59:58'::timestamp without time zone))
   ->  Bitmap Index Scan on event_id_idx  (cost=0.00..245.35 rows=14296 width=0) (actual time=39.794..39.794 rows=28118 loops=1)
         Index Cond: (id = 511)
 Total runtime: 27373.220 ms


ANALYZE
test3=# EXPLAIN ANALYZE select id, dt from event where id = 311 and dt > '2010-03-02 00:00:00' and dt < '2010-03-02 23:59:58';
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on event  (cost=1172.04..153174.37 rows=67846 width=12) (actual time=71.876..788.303 rows=69776 loops=1)
   Recheck Cond: (id = 311)
   Filter: ((dt > '2010-03-02 00:00:00'::timestamp without time zone) AND (dt < '2010-03-02 23:59:58'::timestamp without time zone))
   ->  Bitmap Index Scan on event_id_idx  (cost=0.00..1155.08 rows=67859 width=0) (actual time=46.876..46.876 rows=69824 loops=1)
         Index Cond: (id = 311)
 Total runtime: 873.292 ms
(6 rows)

test3=# EXPLAIN ANALYZE select id, dt from event where id = 911 and dt > '2010-03-02 00:00:00' and dt < '2010-03-02 23:59:58';
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on event  (cost=254.25..46686.71 rows=14465 width=12) (actual time=60.376..14776.822 rows=28078 loops=1)
   Recheck Cond: (id = 911)
   Filter: ((dt > '2010-03-02 00:00:00'::timestamp without time zone) AND (dt < '2010-03-02 23:59:58'::timestamp without time zone))
   ->  Bitmap Index Scan on event_id_idx  (cost=0.00..250.64 rows=14467 width=0) (actual time=21.753..21.753 rows=28102 loops=1)
         Index Cond: (id = 911)
 Total runtime: 14832.289 ms
(6 rows)



test3=# select count(1) from event;
  count   
----------
 19969800

Оставить комментарий

Популярные посты:

uksibsan

Двигатели на SHACMAN: обзор моторов Weichai, Cummins и Shanghai Diesel

• Ранее
• Архив