diff options
Diffstat (limited to 'doc/development/understanding_explain_plans.md')
-rw-r--r-- | doc/development/understanding_explain_plans.md | 34 |
1 files changed, 19 insertions, 15 deletions
diff --git a/doc/development/understanding_explain_plans.md b/doc/development/understanding_explain_plans.md index 8c71a27540d..797dfc676eb 100644 --- a/doc/development/understanding_explain_plans.md +++ b/doc/development/understanding_explain_plans.md @@ -198,8 +198,7 @@ There are quite a few different types of nodes, so we only cover some of the more common ones here. A full list of all the available nodes and their descriptions can be found in -the [PostgreSQL source file -"plannodes.h"](https://gitlab.com/postgres/postgres/blob/master/src/include/nodes/plannodes.h) +the [PostgreSQL source file `plannodes.h`](https://gitlab.com/postgres/postgres/blob/master/src/include/nodes/plannodes.h) ### Seq Scan @@ -315,25 +314,30 @@ the `Indexes:` section: ```sql Indexes: "users_pkey" PRIMARY KEY, btree (id) - "users_confirmation_token_key" UNIQUE CONSTRAINT, btree (confirmation_token) - "users_email_key" UNIQUE CONSTRAINT, btree (email) - "users_reset_password_token_key" UNIQUE CONSTRAINT, btree (reset_password_token) - "index_on_users_lower_email" btree (lower(email::text)) - "index_on_users_lower_username" btree (lower(username::text)) + "index_users_on_confirmation_token" UNIQUE, btree (confirmation_token) + "index_users_on_email" UNIQUE, btree (email) + "index_users_on_reset_password_token" UNIQUE, btree (reset_password_token) + "index_users_on_static_object_token" UNIQUE, btree (static_object_token) + "index_users_on_unlock_token" UNIQUE, btree (unlock_token) "index_on_users_name_lower" btree (lower(name::text)) + "index_users_on_accepted_term_id" btree (accepted_term_id) "index_users_on_admin" btree (admin) "index_users_on_created_at" btree (created_at) "index_users_on_email_trigram" gin (email gin_trgm_ops) "index_users_on_feed_token" btree (feed_token) - "index_users_on_ghost" btree (ghost) + "index_users_on_group_view" btree (group_view) "index_users_on_incoming_email_token" btree (incoming_email_token) + "index_users_on_managing_group_id" btree (managing_group_id) "index_users_on_name" btree (name) "index_users_on_name_trigram" gin (name gin_trgm_ops) + "index_users_on_public_email" btree (public_email) WHERE public_email::text <> ''::text "index_users_on_state" btree (state) - "index_users_on_state_and_internal_attrs" btree (state) WHERE ghost <> true AND support_bot <> true - "index_users_on_support_bot" btree (support_bot) + "index_users_on_state_and_user_type" btree (state, user_type) + "index_users_on_unconfirmed_email" btree (unconfirmed_email) WHERE unconfirmed_email IS NOT NULL + "index_users_on_user_type" btree (user_type) "index_users_on_username" btree (username) "index_users_on_username_trigram" gin (username gin_trgm_ops) + "tmp_idx_on_user_id_where_bio_is_filled" btree (id) WHERE COALESCE(bio, ''::character varying)::text IS DISTINCT FROM ''::text ``` Here we can see there is no index on the `twitter` column, which means @@ -652,7 +656,7 @@ different queries. The only _rule_ is that you _must always measure_ your query and related tools such as: - [`explain.depesz.com`](https://explain.depesz.com/). -- [Pev](http://tatiyants.com/postgres-query-plan-visualization/). +- [`explain.dalibo.com/`](https://explain.dalibo.com/). ## Producing query plans @@ -681,11 +685,11 @@ Planning time: 0.411 ms Execution time: 0.113 ms ``` -### Chatops +### ChatOps -[GitLab employees can also use our chatops solution, available in Slack using the +[GitLab employees can also use our ChatOps solution, available in Slack using the `/chatops` slash command](chatops_on_gitlabcom.md). -You can use chatops to get a query plan by running the following: +You can use ChatOps to get a query plan by running the following: ```sql /chatops run explain SELECT COUNT(*) FROM projects WHERE visibility_level IN (0, 20) @@ -714,7 +718,7 @@ with their own clone of the production database. Joe is available in the [`#database-lab`](https://gitlab.slack.com/archives/CLJMDRD8C) channel on Slack. -Unlike chatops, it gives you a way to execute DDL statements (like creating indexes and tables) and get query plan not only for `SELECT` but also `UPDATE` and `DELETE`. +Unlike ChatOps, it gives you a way to execute DDL statements (like creating indexes and tables) and get query plan not only for `SELECT` but also `UPDATE` and `DELETE`. For example, in order to test new index you can do the following: |