GeoMapX

Давно сталкиваюсь в продакшене с проблемой, что выборки с подзапросами выполняются неприлично долго. Причем в тестах воспроизвести такое поведение никак не удавалось. И вот наконец получилось! Оказывается, проблема существует всегда, но становится заметной только на таблицах с миллионами записей - простейшая выборка "тормозит", если увеличиваются _значения_ используемых в выборке идентификаторов, потому в таблицах с десятками тысяч и менее записей баг просто незаметен. Сначала приведу один из рабочих запросов, над которыми ломал голову: SELECT d.id AS document_id, d.phone_number AS phone, r.name AS region, p.code AS point, p.trademark AS point_trademark, (case when p.is_priority then '1' else '' end) AS color_it, c.code AS center_code, a.value AS status, u2.name AS user_2, tp.name AS template, c.name AS center, (a.save_date at time zone interval '04:00')::date AS work_date, date_trunc('second',(a.save_date at time zone interval ...

Понадобилось мне быстро добавить уникальный идентификатор (uuid) к таблице. На тикле нужная функция пишется очень легко - с помощью пакета uuid или вызова внешней утилиты. Генерация 10 000 идентификаторов во втором случае занимает около 10 с на десктопе, что вполне себе шустро, так что на нем и остановимся. Установим пакет с нужной нам утилитой: sudo aptitude install uuid А вот и реализация функции: --DROP FUNCTION uuid(); CREATE OR REPLACE FUNCTION uuid() RETURNS text AS $BODY$ return [string map {- ""} [exec uuid]] $BODY$ LANGUAGE 'pltclu' VOLATILE SECURITY DEFINER; ALTER FUNCTION uuid() OWNER TO offline; --select uuid(); alter table offline.documents add column checksum text not null default uuid(); CREATE UNIQUE INDEX documents_checksum_idx ON offline.documents USING btree (checksum); Очень даже наглядная иллюстрация "unix way". Разумеется, так делать можно только под юниксами, где порождение множества процессов есть совершенно но...

КОгда-то я тестировал поиск по словарю, для этого конвертировал словари myspell. А теперь существует и стандартная утилит для этого: $ apt-file search pg_updatedicts postgresql-common: /usr/sbin/pg_updatedicts postgresql-common: /usr/share/man/man8/pg_updatedicts.8.gz man pg_updatedicts PG_UPDATEDICTS(8) Debian PostgreSQL infrastructure PG_UPDATEDICTS(8) NAME pg_updatedicts - build PostgreSQL dictionaries from myspell/hunspell ones SYNOPSIS pg_updatedicts DESCRIPTION pg_updatedicts makes dictionaries and affix files from installed myspell and hunspell dictionary packages available to PostgreSQL for usage with tsearch and word stem support. In particular, it takes all *.dic and *.aff files from /usr/share/myspell/dicts/, converts them to UTF-8, puts them into /var/cache/postgresql/dicts/ with *.dict and *.affix suffixes, and symlinks them into /usr/share/postgresql/>version/tsearch_data/system_*, where Po...

Продолжение статьи Тестирование PostgreSQL 8.3 на больших таблицах: денормализация (40М записей) . В предыдущих заметках мы выполняли некоторые типичные выборки из таблиц с десятками миллионов записей в разных версиях PostgreSQL. Пожалуй, такие таблицы и в самом деле можно назвать большими. Но можно ли назвать нашу тестовую БД большой? Разумеется, нет. Нельзя потому, что вся наша БД умещалась не только в ОЗУ сервера, но даже и в буфере памяти PostgreSQL (shared memory). Да, объемы памяти растут, но растут и массивы информации, которые мы хотим обрабатывать, потому для работы с действительно большими БД мы должны быть готовы к тому, что Бд будет значительно больше, чем объем доступной памяти. Для того, чтобы получить удобные для сравнения результаты, мы воспользуемся той же тестовой БД, но объем памяти на запрос и для кэша уменьшим вдесятеро и теперь наша таблица вдесятеро превышает размер кэша постгреса. Для того, чтобы обнспечить работу с "горячим" кэшем (когда нужные данные...

Продолжение статьи Тестирование PostgreSQL 8.3 на больших таблицах: денормализация (10М записей) . Параметры сервера и PostgreSQL те же. Нормализованная таблица без справочников (40M записей) select count(*) from tmp_facts7 where a1=2 and a2=2 and a3=2 and a4=2; count ------- 391 (1 row) explain analyze select * from tmp_facts7 where a1=2 and a2=2 and a3=2 and a4=2; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on tmp_facts7 (cost=102820.61..123140.53 rows=307 width=16) (actual time=1240.778..1250.728 rows=391 loops=1) Recheck Cond: ((a4 = 2) AND (a3 = 2) AND (a2 = 2)) Filter: (a1 = 2) -> BitmapAnd (cost=102820.61..102820.61 rows=5832 width=0) (actual time=1238.241..1238.241 rows=0 loops=1) -> Bitmap Index Scan on tmp_facts7_a4_idx (cost=0.00..34273.29 ro...

Продолжение статьи Тестирование PostgreSQL 8.1 на больших таблицах: денормализация Как один из вариантов оптимизации скорости выборки может быть использована денормализация таблиц. Продолжим работать с тем же тестовым распределением, но теперь в таблице фактов заменим числовые ключи на строковые значения, соответствующие значениям из справочников. Для генерации тестового распределения использована приведенная ниже функция, создающая строки из символа "0", повторенного случайное число раз от нуля до заданного аргументом значения. Для вычисления времени выполнения запроса на этот раз использую команду explain analyze, хотя имхо она дает заниженное значение времени. Количество возвращаемых строк также можно увидеть в выводе названной команды, но из соображений удобства привожу и результат запроса на получение числа строк. Параметры сервера: Intel(R) Core(TM)2 Quad CPU Q6700 @ 2.66GHz MemTotal: 8310900 kB Параметры сервера PostgreSQL: shared_buffers = 1600MB work_mem = 1...

For testing is used the production PostgreSQL database. I did build SQLite database as described here: Trigger-based PostgreSQL to SQLite online replication The test requests is real queries from the production system. The PostgreSQL is too slow on these queries and I search more performance database system for the intranet/internet portal. All tables have needed indices. Some tables are renamed in SQLite database but their names and fields names are similar. Note The PostgreSQL quries is rewrited for better index usage but SQLite query planner work fine without any additional tricks. The query conditions are generated by application query builder for user filters and settings and when you can see identical conditions these are not always identical. Hardware Intel(R) Core(TM)2 Quad CPU Q6700 @ 2.66GHz MemTotal: 8310900 kB HDD: 1 VelociRaptor 10000 rpm PostgreSQL parameters work_mem = 100000 # min 64, size in KB maintenance_work_mem = 2000000 # mi...