Améliorer les temps de réponse des requêtes PostgreSQL avec EXPLAIN. Un des pièges de SQL est que c'est un langage de haut niveau. Le rapport entre la commande qu'on tape et le travail que doit faire la machine est beaucoup moins direct et beaucoup plus dur à saisir qu'avec l'assembleur. Il est donc fréquent qu'une requête SQL qui n'aie pas l'air bien méchante prenne des heures à s'exécuter. Heureusement, PostgreSQL dispose d'une excellent commande EXPLAIN qui explique, avant même qu'on exécute la commande, ce qu'elle va faire et ce qui va prendre du temps. Je commence par une requête triviale, SELECT * FROM Votes et je la fais précéder de EXPLAIN pour demander à PostgreSQL de m'expliquer : so=> EXPLAIN SELECT * FROM Votes; QUERY PLAN ---------------------------------------------------------------- Seq Scan on votes (cost=0.00..36658.37 rows=2379537 width=16) (1 row) On apprend que PostgreSQL va faire une recherche séquentielle (Seq Scan), ce qui est normal, puisqu'il faudra tout trouver, que 2379537 tuples (lignes de la table SQL) sont à examiner.
Use Subqueries to Count Distinct 50X Faster. NB: These techniques are universal, but for syntax we chose Postgres. Thanks to the inimitable pgAdminIII for the Explain graphics. So Useful, Yet So Slow Count distinct is the bane of SQL analysts, so it was an obvious choice for our first blog post. First things first: If you have a huge dataset and can tolerate some imprecision, a probabilistic counter like HyperLogLog can be your best bet. (We’ll return to HyperLogLog in a future blog post.) Let’s start with a simple query we run all the time: Which dashboards do most users visit? Select dashboards.name, count(distinct time_on_site_logs.user_id) from time_on_site_logs join dashboards on time_on_site_logs.dashboard_id = dashboards.id group by name order by count desc In Periscope, this would give you a graph like this: For starters, let’s assume the handy indices on user_id and dashboard_id are in place, and there are lots more log lines than dashboards and users.
On just 10 million rows, this query takes 48 seconds. Aggregate, Then Join select. Gmcguire/django-db-pool: Django DB Pool - Another database persistance / pooling implementation for Django. Changer le propriétaire (owner) d'une base postgresql - linux professionnel. -- Creation d'une fonction pour exécuter les commandes a chaque occurrence create or replace function exec(text) returns void as ' begin execute $1; end ' language plpgsql; -- Modification du proprietaire de la base alter database mabase owner to mongroupe_owner; -- Modification du propriétaire des tables select table_schema||'.' from information_schema.tables where table_schema not in ('information_schema', 'pg_catalog'); -- Modification du proprietaire des sequences select sequence_schema||'.' as "Changed sequence ownership" from information_schema.sequences where sequence_schema not in ('information_schema', 'pg_catalog'); -- modification du propriétaire des schemas select distinct table_schema || exec ('alter schema '||table_schema||' owner to mongroupe_owner') as "Changed schema ownership" from information_schema.tables where table_schema not in ('information_schema', 'pg_catalog'); -- attribution des droits d’exécution des fonctions.
PostgreSQL: Documentation: 8.1: Backup and Restore. As with everything that contains valuable data, PostgreSQL databases should be backed up regularly. While the procedure is essentially simple, it is important to have a basic understanding of the underlying techniques and assumptions. There are three fundamentally different approaches to backing up PostgreSQL data: Each has its own strengths and weaknesses. The idea behind the SQL-dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump.
Pg_dump dbname > outfile As you see, pg_dump writes its results to the standard output. Pg_dump is a regular PostgreSQL client application (albeit a particularly clever one). To specify which database server pg_dump should contact, use the command line options -h host and -p port. As any other PostgreSQL client application, pg_dump will by default connect with the database user name that is equal to the current operating system user name. Or. Postgresql - Granting access to all tables for a user. First, you have to be able to connect to the database in order to run queries. This can be achieved by REVOKE CONNECT ON DATABASE your_database FROM PUBLIC; GRANT CONNECTON DATABASE database_name TO user_name; The REVOKE is necessary because The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later.
If you really want to restrict your user to DML statements, then you have a little more to do: REVOKE ALLON ALL TABLES IN SCHEMA public FROM PUBLIC; GRANT SELECT, INSERT, UPDATE, DELETEON ALL TABLES IN SCHEMA public TO user_name; These assume that you will have only one schema (which is named 'public' by default). As Jack Douglas pointed out, the above only gives the privileges for the already existing tables. ALTER DEFAULT PRIVILEGES FOR ROLE some_role -- Alternatively "FOR USER" IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;
Obtenir un plan d'exécution | Base de données PostgreSQL. Un plan d’exécution PostgreSQL est récupéré en plaçant la commande explain au début d’une requête SQL. Néanmoins, il existe une limitation importante : il n’est pas possible de récupérer un plan d’exécution de cette façon avec les requêtes SQL comprenant des paramètres liés (par exemple $1, $2, etc.). Elles doivent d’abord être préparées : PREPARE req(int) AS SELECT $1; Notez que PostgreSQL utilise la notation $n pour les paramètres liés.
La couche d’abstraction de la base de données peut vous le cacher pour que vous puissiez utiliser des points d’interrogation comme définis dans le standard SQL. Il est possible d’obtenir le plan d’exécution de la requête préparée ainsi : EXPLAIN EXECUTE req(1); Jusqu’à PostgreSQL 9.1, le plan d’exécution était déjà créé lors de l’appel à prepare et pouvait donc ne pas prendre en considération les valeurs réelles pour des paramètres liés lors du execute. Note Les plans d’exécution des requêtes sans paramètres liés s’obtiennent directement : Avertissement.
Postgres Guide. PostgreSQL: Documentation: 9.5: Performance Tips. Exploring Query Locks in Postgres - Big elephants. Understanding query locking is key to using postgres concurrently. So let’s look at an example to learn more about how locking works and how to see what’s going on within your database. Playing in the sandbox To play around with locks let’s first create a sandbox.
Now let’s open two postgres consoles to play in the sandbox. For clarification let’s name them Alice and Bob. You can SET the console prompt with: Now Alice takes a look for what kind of toys there are. Note we introduce the statement with BEGIN to open the transaction explicitly and keep it open until we commit or rollback. If Bob does the same he would get the same immediate output. That is the two SELECT statements do not interfere and can safely run concurrent without blocking each other. Pg_lock Nevertheless our transactions are still open. Each active lock is stored in pg_catalogs.pg_lock view. To not see locks from our own query we also filter out entries from our own backend_pid. Let’s read the fifth row: transactionid. Tuning Your PostgreSQL Server. By Greg Smith, Robert Treat, and Christopher Browne PostgreSQL ships with a basic configuration tuned for wide compatibility rather than performance. Odds are good the default parameters are very undersized for your system.
Rather than get dragged into the details of everything you should eventually know (which you can find if you want it at the GUC Three Hour Tour), here we're going to sprint through a simplified view of the basics, with a look at the most common things people new to PostgreSQL aren't aware of. You should click on the name of the parameter in each section to jump to the relevant documentation in the PostgreSQL manual for more details after reading the quick intro here. There is also additional information available about many of these parameters, as well as a list of parameters you shouldn't adjust, at Server Configuration Tuning. Background Information on Configuration Settings The types of settings When they take effect Important notes about configuration files autovacuum. PostgreSQL: The world's most advanced open source database.