Портал о домашнем оборудовании. Postgresql индексы оптимизация
Заметки веб-разработчика/админа ← Роман Теличко
Запрос отображает использование индексов. Что позволяет увидеть наиболее часто использованные индексы, а также и наиболее редко (у которых будет index_scans_count = 0).
Учитываются только пользовательские индексы и не учитываются уникальные, т.к. они используются как ограничения (как часть логики хранения данных).
В начале отображаются наиболее часто используемые индексы (отсортированы по колонке index_scans_count).
SELECT idstat.relname AS table_name, -- имя таблицы indexrelname AS index_name, -- индекс idstat.idx_scan AS index_scans_count, -- число сканирований по этому индексу pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, -- размер индекса tabstat.idx_scan AS table_reads_index_count, -- индексных чтений по таблице tabstat.seq_scan AS table_reads_seq_count, -- последовательных чтений по таблице tabstat.seq_scan + tabstat.idx_scan AS table_reads_count, -- чтений по таблице n_tup_upd + n_tup_ins + n_tup_del AS table_writes_count, -- операций записи pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size -- размер таблицы FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON indexrelname = indexname AND idstat.schemaname = pg_indexes.schemaname JOIN pg_stat_user_tables AS tabstat ON idstat.relid = tabstat.relid WHERE indexdef !~* 'unique' ORDER BY idstat.idx_scan DESC, pg_relation_size(indexrelid) DESC
Читайте также:
romantelychko.com
postgresql - postgres: индекс не используется или как оптимизировать
У меня есть таблица с индексом в булевом поле ( "test" ). Когда он является истинным, он использует индекс, чтобы он быстро загружался, но когда он ложный, он не использует его. Что-то не так?
Здесь я объясню его анализ:
DB_development=# explain analyze SELECT COUNT(*) FROM "users" WHERE "users"."is_test" = 't'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=22890.67..22890.68 rows=1 width=0) (actual time=1848.655..1848.656 rows=1 loops=1) -> Index Scan using index_users_on_is_test on users (cost=0.00..22846.51 rows=17665 width=0) (actual time=34.727..1844.081 rows=21457 loops=1) Index Cond: (is_test = true) Filter: is_test Total runtime: 1848.882 ms (5 rows) DB_development=# explain analyze SELECT COUNT(*) FROM "users" WHERE "users"."is_test" = 'f'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Aggregate (cost=84505.74..84505.75 rows=1 width=0) (actual time=9557.632..9557.632 rows=1 loops=1) -> Seq Scan on users (cost=0.00..84063.72 rows=176807 width=0) (actual time=71.653..9533.595 rows=219531 loops=1) Filter: (NOT is_test) Total runtime: 9557.655 ms (4 rows)Я увидел здесь Добавление индекса в логическое поле, чтобы он мог игнорировать индексы... Я думаю, что это правильно, потому что не тестируемые пользователи на самом деле довольно сильно сравниваются для тестирования пользователей.
DB_development=# SELECT COUNT(*) FROM "users" WHERE "users"."is_test" = 't'; count ------- 21457 (1 row) DB_development=# SELECT COUNT(*) FROM "users" WHERE "users"."is_test" = 'f'; count -------- 219531 (1 row)Обновление
Здесь создайте таблицу и индексы:
create_table "users", :force => true do |t| t.integer "genre_id" t.integer "country_id" t.boolean "is_test", :default => false t.datetime "created_at" t.datetime "updated_at" ... + 90 more fields (it my main table) end add_index "users", ["country_id"], :name => "index_users_on_country_id" add_index "users", ["genre_id"], :name => "index_users_on_genre_id" add_index "users", ["is_test"], :name => "index_users_on_is_test" ... + 17 more indexes задан index 20 июня '13 в 14:46 источник поделитьсяqaru.site
установка, настройка, обслуживание. Настройка PostgreSQL. Оптимизация работы PostgreSQL.
PostgreSQL (произносится как "Пост-Грес-Кью-Эл") - свободная современная СУБД с широкими возможностями. Её используют такие компании, как Alibaba, Instagram, Skype, Yahoo и многие другие. Это говорит о надёжности системы, и при этом она является простой в установке, использовании и обслуживании. Поверхностно ознакомиться с ней можно по статье в Википедии: https://ru.wikipedia.org/wiki/PostgreSQL
PostgreSQL является кроссплатформенной СУБД с открытым исходным кодом, поэтому её можно установить практически на любой сервер. Все конфигурации серверов и все операционные системы, предоставляемые vscale , позволяют использование PostgreSQL.
Рассмотрим установку и настройку на примере Ubuntu 16.04 64bit.
Технические требования
• Пользователь с sudo-правами• Ubuntu 16.04
Шаг 1. Выбор источника для установки
PostgreSQL является очень популярным сервером баз данных, поэтому присутствует в официальных репозиториях Ubuntu. Однако в PPA разработчиков PostgreSQL можно найти самую свежую версию. Например, на момент написания данной инструкции в репозитории Ubuntu имеется PostgreSQL 9.5, а из PPA можно установить 9.6. Если у вас нет потребности в самых последних возможностях данной СУБД, то текущий шаг можно пропустить. Иначе добавьте репозиторий PostgreSQL в системный список источников:
Sudo sh -c "echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list"
и добавьте для него ключ
Wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
Это позволит при обновлении пакетов получать наиболее свежие версии.
Шаг 2. Установка PostgreSQL
Если система установлена недавно и вы пропустили предыдущий шаг, то данный шаг можно пропустить, так как в процессе установки системы производится обновление индекса пакетов. В противном случае выполните команду:
Sudo apt-get update
Эта команда произведёт обновление индекса, что позволит устанавливать свежие и актуальные пакеты.
Установка PostgreSQL из официальных репозиториев и из PPA производится одинаково. Загрузим и установим пакеты PostgreSQL и contrib (contrib предоставляет некоторый дополнительный функционал и утилиты):
Шаг 3. Подключение к серверу баз данных
Во время установки программы в системе автоматически была создана учётная запись администратора баз данных - postgres. На данном этапе доступ к системе баз данных можно получить только через неё. Вы можете либо переключиться в сессию учётной записи postgres и запустить там оболочку программы:
Sudo su - postgrespsql
либо запустить оболочку от имени postgres без переключения сессии:
Sudo -u postgres psql
Попав тем или иным способом в командную строку psql, вам необходимо знать, как из неё выйти. Это можно сделать с помощью ввода команды выхода:
(сокращение от quit).
Шаг 4. Создание новой роли
Если вы производили установку по инструкции, то к этому моменту в вашей СУБД есть только одна роль - postgres. Рекомендуется не использовать данную роль для работы со своими базами данных, а создавать для каждой базы новую роль (или несколько при необходимости). Для создания новой роли предусмотрены два стандартных способа:
- интерактивный режим, в котором достаточно ответить на несколько простых вопросов;
- команда для создания роли через командную строку СУБД.
Мы не будем подробно останавливаться на интерактивном режиме, так как создать роль, которая полностью удовлетворяет требованиям в большинстве случаев, мы можем всего одной простой командой (перед этим нужно находиться в режиме командной строки как было описано на Шаге 3). Не забудьте заменить username на желаемое имя пользователя, а password - на пароль для этого пользователя:
Имя указывается без кавычек, а пароль - в одинарных кавычках.
Шаг 5. Создание базы данных
Находясь в режиме командной строки psql, создать базу данных мы можем командой create database и указав название базы данных. Например, чтобы воздать БД с именем vscale_db, выполните команду:
Create database vscale_db;
Шаг 6. Назначение прав
Созданной ранее нами роли нужно назначить права на базу данных. В большинстве проектов, где у вас будет использоваться всего один пользователь базы данных, ему будут требоваться полные права. Выдать их можно следующим образом:
где vscale_db - название базы данных, выбранное на шаге 5, а username - имя пользователя, заданное на шаге 4.
Вся минимально требующаяся предварительная настройка завершена. Вы можете выйти из psql, введя "\q".
Шаг 7. Аутентификация и начало работы с базой данных
К текущему моменту у вас есть всё необходимое для начала работы с базой данных. Чтобы подключиться к ней, запустите psql с параметрами: хост "-h localhost" (так как подключаемся к локальной базе данных), название БД "vscale_db" и имя пользователя "username":
Psql -h localhost vscale_db username
После этого вы сможете взаимодействовать с вашей базой данных: создавать таблицы, созранять в них информцию, делать выборки и т.д.
Закл
bazava.ru
