0

I have a data ware house running on PostgreSQL and I would like to check what all are the missing indices are in my database.

I tried to install the extension pg_qualstats but it is giving the below error.

root@Ubuntu-1604-xenial-64-minimal ~ # sudo pip install pg_qualstats
Collecting pg_qualstats
  Could not find a version that satisfies the requirement pg_qualstats (from versions: )
No matching distribution found for pg_qualstats
You are using pip version 18.1, however version 19.2.3 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
root@Ubuntu-1604-xenial-64-minimal ~ #

So how can i install this extension? I tried the CREATE EXTENSION pg_qualstats but it was giving error.

Also is there any other database maintenance need to be done for the database regularly?

What all parameter I have to check?

Can we automate the maintenance activity?

I was a SQL server DB admin and it was much easier to find out the missing index, understand the execution plan, DB maintenance but I find it hard, when it comes to PostgreSQL.

So any guidance will be of great help.

Improve this question
7
  • 1
    Consider reasking on dba.stackexchange.com as RDMS server/config/extension installation is in the gray area of considerd to be offtopic here.. Commented Oct 10, 2019 at 11:50
  • "I was a SQL server DB admin and it was much easier to understand the Execution plan when coming to PostgreSQL." SQL Server Management Studio most likely spoiled you with image generated based explain outputs... But what i read here the pgadmin tool should also have that feature more or less.. Commented Oct 10, 2019 at 12:00
  • 2
    @RaymondNijland: I personally find the graphical representation of execution plans next to useless because they hide so many interesting and important details. Especially with Postgres the plain text output is much more detailed and easier to understand (plus I never found out the equivalent to explain (analyze) in SQL Server where the statement is actually executed Commented Oct 10, 2019 at 12:26
  • @a_horse_with_no_name Can you post it as an answer please. Commented Oct 10, 2019 at 13:02
  • 1
    @RaymondNijland: interesting, thanks Commented Oct 10, 2019 at 13:13

1 Answer 1

Reset to default
1

I am only answering the immediate question regarding the installation of pg_qualstats - the rest of the questions are way too broad for a platform like stackoverflow (or dba.stackexchange).

Many interesting extensions are provided as source code in Postgres (that's one of the reasons why it's highly recommended to run Postgres on Linux, because compiling the extensions is way easier in Linux than it is on Windows, and may extensions are only developed for Linux).

pg_qualstats is no different.

It is provided together with PoWA and the installation of the extension is documented as part of their installation guide

In a nutshell:

Download the source:

wget https://github.com/powa-team/pg_qualstats/archive/1.0.7.tar.gz -O pg_qualstats-1.0.7.tar.gz
tar zxvf pg_qualstats-1.0.7.tar.gz
cd pg_qualstats-1.0.7

the compile it:

make
make install

Register the shared libraries by editing postgresql.conf and after restarting Postgres the extension can be created using CREATE EXTENSION pg_qualstats;

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.