Bug#969311: UDD: please install pg_stat_statements extension

[Michael Banck]

owner 969311 mba...@debian.org
thanks

On Mon, Aug 31, 2020 at 10:02:52AM +0200, Michael Banck wrote:
> In order to best assess where and how to improve the situation, please
> install the pg_stat_statements extension in the udd database, like
> 
> CREATE EXTENSION pg_stat_statements;

Gah, I forgot that pg_stat_statements requires a preloaded library. So
before the above can work, postgresql.conf needs to be changed:

-#shared_preload_libraries = ''  # (change requires restart)
+shared_preload_libraries = 'pg_stat_statements' # (change requires restart)

As indicated, this will only take effect after the next server restart,
then the extension can be created.

pg_stat_statements is part of the core set of Postgres extensions and
does not need to be installed via packages.


Michael

Bug#969311: UDD: please install pg_stat_statements extension

[Michael Banck]

Package: qa.debian.org
Severity: wishlist
User: qa.debian@packages.debian.org
Usertags: udd

Dear Maintainer,

it seems some parts of the UDD schema are sub-optimal, e.g. some
non-trivial tables are missing primary keys or indexes alltogether,
resulting in many full table scans.

In order to best assess where and how to improve the situation, please
install the pg_stat_statements extension in the udd database, like

CREATE EXTENSION pg_stat_statements;

Also it would be beneficial if the 'track_io_timings' parameter in
postgresql.conf could be set to 'on' followed by a reload (a restart is
not necessary).

After a few days or weeks running with pg_stat_statements, I'd like to
take a look at the most problematic queries and how to possibly improve
them.


Thanks,

Michael

-- System Information:
Debian Release: 10.5
  APT prefers stable
  APT policy: (500, 'stable')
Architecture: amd64 (x86_64)
Foreign Architectures: i386

Kernel: Linux 4.19.0-6-amd64 (SMP w/2 CPU cores)
Locale: LANG=de_DE.UTF-8, LC_CTYPE=de_DE.UTF-8 (charmap=UTF-8), 
LANGUAGE=de_DE.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Init: systemd (via /run/systemd/system)
LSM: AppArmor: enabled