Differences

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

--- sysadmin:postgresql [2014/11/25 08:48] – wiki syntax feroda
+++ sysadmin:postgresql [2015/02/12 08:57] – CREAZIONE E USO INDICI feroda
@@ Line 72: / Line 72: @@
     * [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)
+====== 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:
@@ Line 96: / Line 139: @@
             FROM information_schema.tables
             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
         JOIN pg_class