Il nostro punto della strada

User Tools

Site Tools

Trace:
sysadmin:postgresql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revisionBoth sides next revision
sysadmin:postgresql [2014/11/25 17:17] – occupazione db ferodasysadmin:postgresql [2015/02/12 08:57] – CREAZIONE E USO INDICI feroda
Line 71: Line 71:
     * [4] = ... indici usati ma non efficaci     * [4] = ... indici usati ma non efficaci
     * [5] = ... tabelle vuote     * [5] = ... tabelle vuote
 +
 +====== CREAZIONE E USO INDICI =======
 +
 +  postgres# \d+ acs_service.connection_session;
 +     Column   |           Type           |                                  Modifiers                                  | Storage |                                                   Description                                                    
 +  ------------+--------------------------+-----------------------------------------------------------------------------+---------+------------------------------------------------------------------------------------------------------------------
 +   id         | integer                  | not null default nextval('acs_service.connection_session_id_seq'::regclass) | plain   | 
 +   start_time | timestamp with time zone | not null                                                                    | plain   | Moment in which the VPN session started. Obtained from $time_unix OpenVPN's envvar.
 +   duration   | interval                 |                                                                             | plain   | Duration of the connection, value is NULL until connection drops. Obtained from $time_duration OpenVPN's envvar.
 +   id_chamber | integer                  | not null                                                                    | plain   | Id of the Chamber related to the connection.
 +  Indexes:
 +    "pk_id" PRIMARY KEY, btree (id)
 +    "connection_session_duration_idx" btree (duration)
 +    "connection_session_id_chamber_idx" btree (id_chamber)
 +  Foreign-key constraints:
 +    "fk_chamber" FOREIGN KEY (id_chamber) REFERENCES acs_service.chamber(id)
 +  Has OIDs: no
 +
 +  postgres# REINDEX TABLE "acs_service"."connection_session";
 +  REINDEX
 +  postgres# EXPLAIN ANALYZE SELECT bcs.id, bcs.duration FROM acs_service.connection_session bcs WHERE bcs.duration IS NULL;
 +                                                                QUERY PLAN                                                                
 +  ------------------------------------------------------------------------------------------------------------------------------------------
 +  Bitmap Heap Scan on connection_session bcs  (cost=4.82..234.49 rows=70 width=20) (actual time=0.117..0.180 rows=67 loops=1)
 +   Recheck Cond: (duration IS NULL)
 +   ->  Bitmap Index Scan on connection_session_duration_idx  (cost=0.00..4.80 rows=70 width=0) (actual time=0.106..0.106 rows=67 loops=1)
 +         Index Cond: (duration IS NULL)
 +  Total runtime: 0.208 ms (5 rows)
 +
 +  postgres=# EXPLAIN ANALYZE SELECT bcs.id, bcs.duration FROM acs_service.connection_session bcs WHERE bcs.duration IS NOT NULL;
 +                                                          QUERY PLAN                                                           
 +  -------------------------------------------------------------------------------------------------------------------------------
 +   Seq Scan on connection_session bcs  (cost=0.00..2829.78 rows=149208 width=20) (actual time=0.008..36.858 rows=149211 loops=1)
 +   Filter: (duration IS NOT NULL)
 +  Total runtime: 46.523 ms (3 rows)
  
 ====== Occupazione disco ====== ====== Occupazione disco ======