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 revision		Previous revision
Last revisionBoth sides next revision
sysadmin:postgresql [2014/11/25 08:48] – wiki syntax ferodasysadmin:postgresql [2015/02/12 09:01] – Indice su espressione feroda
Line 72: Line 72:
     * [5] = ... tabelle vuote     * [5] = ... tabelle vuote
  
-====== Occupazione disco per tabelle ======+====== 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) 
 +   
 +===== Se creo indice sull'espressione "duration IS NOT NULL" cosa succede? ===== 
 + 
 +Niente .... :( 
 + 
 +    postgres=# CREATE INDEX ON "acs_service".connection_session ((duration IS NOT NULL)); 
 +    CREATE INDEX 
 +    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 NOT NULL; 
 +                                                              QUERY PLAN                                                        
 +    ------------------------------------------------------------------------------------------------------------------------------- 
 +     Seq Scan on connection_session bcs  (cost=0.00..2829.90 rows=149220 width=20) (actual time=0.011..38.323 rows=149226 loops=1) 
 +       Filter: (duration IS NOT NULL) 
 +     Total runtime: 48.491 ms 
 +    (3 rows) 
 + 
 +    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.061..0.119 rows=65 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.050..0.050 rows=66 loops=1) 
 +             Index Cond: (duration IS NULL) 
 +     Total runtime: 0.148 ms 
 +    (5 rows) 
 + 
 + 
 +====== Occupazione disco ====== 
 + 
 +===== Database ===== 
 + 
 +Lo restituisce pure in formato carino :) 
 + 
 +# select (pg_database_size(current_database())/(1024*1024))::text || ' MB' as db_size; 
 + 
 +===== Tabelle =====
  
 Restituisce un elenco ordinato in modo discendente di: Restituisce un elenco ordinato in modo discendente di:
Line 96: Line 166:
             FROM information_schema.tables              FROM information_schema.tables 
             WHERE table_schema NOT IN ('information_schema', 'pg_catalog')              WHERE table_schema NOT IN ('information_schema', 'pg_catalog') 
-        ORDER BY size DESC, total_size DESC LIMIT 20+        ORDER BY size DESC, total_size DESC
     ) AS basic_infos      ) AS basic_infos 
         JOIN pg_class         JOIN pg_class