Adrien/org-roamings
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
Files
master
org-roamings/20220104155310-postgres.org

5.1 KiB
Raw Permalink Blame History

Postgres

Principales caractéristiques

  • Licence de type BSD,
  • Utilise des types de données modernes, dits composés ou enrichis,
  • Fonctionne sur Solaris, SunOS, Mac OS X, HP-UX, AIX, Linux, IRIX, Digital Unix, BSD, NetBSD, FreeBSD, OpenBSD, SCO unix, NeXTSTEP, UnixWare et toutes sortes d'Unix. Depuis la version 8.0, PostgreSQL fonctionne également nativement sur Windows.

Interfaces utilisateur

CLI

Graphique

Howto

Pour lister les utilisateurs 

\du

Pour supprimer un utilisateur 

sudo -u postgres dropuser -e <user>

Pour supprimer une base de données 

sudo -u postgres dropdb <database>

Pour modifier le password d'un utilisateur (dans psql) 

\password <user>

Profiling de requêtes (query plan)

Depuis Psql 

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT _stops.kind, _stops.id, _stops.name, _stops.town_name, _stops.postal_region, _stops.xepsg2154, _stops.yepsg2154, _stops.version, _stops.created_ts, _stops.changed_ts, stops.id AS id_1, stops.latitude, stops.longitude, stops.transport_mode, stops.accessibility, stops.visual_signs_available, stops.audible_signs_available, stops.record_id, stops.record_ts, stop_areas.id AS id_2, stop_areas.type
FROM _stops LEFT OUTER JOIN stops ON _stops.id = stops.id LEFT OUTER JOIN stop_areas ON _stops.id = stop_areas.id
WHERE _stops.name ILIKE '%Chaville rive droite%'
 Nested Loop Left Join  (cost=148.62..250.06 rows=5 width=194) (actual time=1.213..1.346 rows=12 loops=1)
   Output: _stops.kind, _stops.id, _stops.name, _stops.town_name, _stops.postal_region, _stops.xepsg2154, _stops.yepsg2154, _stops.version, _stops.created_ts, _stops.changed_ts, stops.id, stops.latitude, stops.longitude, stops.transport_mode, stops.accessibility, stops.visual_signs_available, stops.audible_signs_available, stops.record_id, stops.record_ts, stop_areas.id, stop_areas.type
   Inner Unique: true
   Buffers: shared hit=123
   ->  Nested Loop Left Join  (cost=148.33..208.53 rows=5 width=182) (actual time=1.196..1.296 rows=12 loops=1)
         Output: _stops.kind, _stops.id, _stops.name, _stops.town_name, _stops.postal_region, _stops.xepsg2154, _stops.yepsg2154, _stops.version, _stops.created_ts, _stops.changed_ts, stops.id, stops.latitude, stops.longitude, stops.transport_mode, stops.accessibility, stops.visual_signs_available, stops.audible_signs_available, stops.record_id, stops.record_ts
         Inner Unique: true
         Buffers: shared hit=97
         ->  Bitmap Heap Scan on public._stops  (cost=148.04..166.99 rows=5 width=93) (actual time=1.172..1.219 rows=12 loops=1)
               Output: _stops.kind, _stops.id, _stops.name, _stops.town_name, _stops.postal_region, _stops.xepsg2154, _stops.yepsg2154, _stops.version, _stops.created_ts, _stops.changed_ts
               Recheck Cond: ((_stops.name)::text ~~* '%Chaville rive droite%'::text)
               Heap Blocks: exact=11
               Buffers: shared hit=63
               ->  Bitmap Index Scan on name_idx_gin  (cost=0.00..148.04 rows=5 width=0) (actual time=1.155..1.155 rows=12 loops=1)
                     Index Cond: ((_stops.name)::text ~~* '%Chaville rive droite%'::text)
                     Buffers: shared hit=52
         ->  Index Scan using stops_pkey on public.stops  (cost=0.29..8.31 rows=1 width=89) (actual time=0.005..0.005 rows=1 loops=12)
               Output: stops.id, stops.latitude, stops.longitude, stops.transport_mode, stops.accessibility, stops.visual_signs_available, stops.audible_signs_available, stops.record_id, stops.record_ts
               Index Cond: (stops.id = _stops.id)
               Buffers: shared hit=34
   ->  Index Scan using stop_areas_pkey on public.stop_areas  (cost=0.29..8.30 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=12)
         Output: stop_areas.id, stop_areas.type
         Index Cond: (stop_areas.id = _stops.id)
         Buffers: shared hit=26
 Planning:
   Buffers: shared hit=22
 Planning Time: 1.015 ms
 Execution Time: 1.521 ms
(28 rows)

Time: 3.503 ms

Visualisation de Query plan

Différents sites permettent de visualiser graphiquement les query plans:

Audit

Possibilité de logger ou de stocker en base les ajout/modifications ou suppressions de donnéées:

Références