sysadmin:postgresql
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionLast revisionBoth sides next revision | ||
sysadmin:postgresql [2014/11/25 08:46] – Query per occupazione disco delle tabelle feroda | sysadmin: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; | ||
+ | | ||
+ | ------------+--------------------------+-----------------------------------------------------------------------------+---------+------------------------------------------------------------------------------------------------------------------ | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | Indexes: | ||
+ | " | ||
+ | " | ||
+ | " | ||
+ | Foreign-key constraints: | ||
+ | " | ||
+ | Has OIDs: no | ||
+ | |||
+ | postgres# REINDEX TABLE " | ||
+ | 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) | ||
+ | | ||
+ | | ||
+ | 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) | ||
+ | | ||
+ | Total runtime: 46.523 ms (3 rows) | ||
+ | |||
+ | ===== Se creo indice sull' | ||
+ | |||
+ | Niente .... :( | ||
+ | |||
+ | postgres=# CREATE INDEX ON " | ||
+ | CREATE INDEX | ||
+ | postgres=# REINDEX TABLE " | ||
+ | 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) | ||
+ | | ||
+ | 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 | ||
+ | ------------------------------------------------------------------------------------------------------------------------------------------ | ||
+ | | ||
+ | | ||
+ | | ||
+ | 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())/ | ||
+ | |||
+ | ===== Tabelle | ||
Restituisce un elenco ordinato in modo discendente di: | Restituisce un elenco ordinato in modo discendente di: | ||
Line 90: | Line 160: | ||
END | END | ||
)::bigint AS estimated_row_count | )::bigint AS estimated_row_count | ||
- | |||
FROM ( | FROM ( | ||
- | |||
SELECT table_schema, | SELECT table_schema, | ||
pg_relation_size('"' | pg_relation_size('"' | ||
Line 98: | Line 166: | ||
FROM information_schema.tables | FROM information_schema.tables | ||
WHERE table_schema NOT IN (' | WHERE table_schema NOT IN (' | ||
- | 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 |