Оптимизация запросов Postgresql Не разрешено внутреннее/внешнее соединение. Оптимизация postgresql запросов

Оптимизировать запрос postgresql, который выполняется быстрее в pgAdmin в приложении вместе с параллельными запросами

Я использовал ваш DDL для создания таблицы и индексов, а затем заполнил таблицу 2 миллионами строк бессмысленных данных.

insert into "Sales" ("ID", "DayOfSale", "StoreKeeper", "CustomerId") select n, timestamp '2014-08-05 08:00' - (n || ' minutes')::interval, random_integer(1, 100), 3 from generate_series(1, 2000000) n; analyze "Sales";

Я выбрал значение для «DayOfSale», чтобы гарантировать, что только небольшая часть таблицы будет удовлетворять ИНЕК. Я бы ожидал, что сканирование индекса для данных такого типа, и это именно то, что произошло.

"Sort (cost=173.76..173.76 rows=1 width=9) (actual time=2.639..2.647 rows=100 loops=1)" " Sort Key: (count("ID")), "StoreKeeper"" " Sort Method: quicksort Memory: 29kB" " -> HashAggregate (cost=173.74..173.75 rows=1 width=9) (actual time=2.542..2.557 rows=100 loops=1)" " -> Index Scan using sale_dayofsale_index on "Sales" (cost=0.43..156.12 rows=3525 width=9) (actual time=0.023..1.184 rows=3360 loops=1)" " Index Cond: ("DayOfSale" >= '2014-08-03 00:00:00'::timestamp without time zone)" "Total runtime: 2.689 ms"

Кроме того, оптимизатор не выполнял приведение с отметки времени до даты.

По моему опыту, если эта таблица действительно посвящена продажам, было бы редко сообщать о произвольных временных диапазонах. Я ожидаю, что запросы по таким данным будут связаны с календарными неделями, календарными месяцами или календарными годами. Таким образом, вы могли бы извлечь выгоду из

частичный индекс,
индекс на выражение или
секционирования.

Я также попробовал covering index. PostgreSQL 9.2+ может выполнять только индексирование; если у вас есть индекс покрытия, ему не нужно читать данные из таблицы.

Этот запрос будет показывать количество строк для каждого месяца.

select date_trunc('month', "DayOfSale"), count(*) from "Sales" group by date_trunc('month', "DayOfSale") order by 1;

В моем случае, это показывает, что диапазон значений от Окт 2010 по август 2014 г. только около 6000 строк в августе 2014 года, и не все, будет удовлетворять ваши ИНЕКЕ. Может быть полезно вставить результаты этого запроса в ваш вопрос.

stackoverrun.com

database - Оптимизация запросов Postgresql Не разрешено внутреннее/внешнее соединение

Мне задан этот запрос для оптимизации на POSTGRESQL 9.2:

SELECT C.name, COUNT(DISTINCT I.id) AS NumItems, COUNT(B.id) FROM Categories C INNER JOIN Items I ON(C.id = I.category) INNER JOIN Bids B ON (I.id = B.item_id) GROUP BY C.name

В рамках моего школьного задания.

Я создал эти индексы в соответствующей таблице: items(category) → 2ndary b + tree, bids(item_id) → 2ndary b + tree и categories(id) → здесь первичный индекс,

Странная часть: PostgreSQL делает последовательное сканирование в моей таблице Items, Categories и Bid, а когда я устанавливаю enable_seqscan=off, поиск индекса оказывается более ужасным, чем результат ниже.

Когда я запускаю объяснение в PostgreSQL, это результат: ПОЖАЛУЙСТА, НЕ СНИМАЙТЕ ПРОМЫСЛЫ, КАК ОНИ НЕОБХОДИМО!

GroupAggregate (cost=119575.55..125576.11 rows=20 width=23) (actual time=6912.523..9459.431 rows=20 loops=1) Buffers: shared hit=30 read=12306, temp read=6600 written=6598 -> Sort (cost=119575.55..121075.64 rows=600036 width=23) (actual time=6817.015..8031.285 rows=600036 loops=1) Sort Key: c.name Sort Method: external merge Disk: 20160kB Buffers: shared hit=30 read=12306, temp read=6274 written=6272 -> Hash Join (cost=9416.95..37376.03 rows=600036 width=23) (actual time=407.974..3322.253 rows=600036 loops=1) Hash Cond: (b.item_id = i.id) Buffers: shared hit=30 read=12306, temp read=994 written=992 -> Seq Scan on bids b (cost=0.00..11001.36 rows=600036 width=8) (actual time=0.009..870.898 rows=600036 loops=1) Buffers: shared hit=2 read=4999 -> Hash (cost=8522.95..8522.95 rows=50000 width=19) (actual time=407.784..407.784 rows=50000 loops=1) Buckets: 4096 Batches: 2 Memory Usage: 989kB Buffers: shared hit=28 read=7307, temp written=111 -> Hash Join (cost=1.45..8522.95 rows=50000 width=19) (actual time=0.082..313.211 rows=50000 loops=1) Hash Cond: (i.category = c.id) Buffers: shared hit=28 read=7307 -> Seq Scan on items i (cost=0.00..7834.00 rows=50000 width=8) (actual time=0.004..144.554 rows=50000 loops=1) Buffers: shared hit=27 read=7307 -> Hash (cost=1.20..1.20 rows=20 width=19) (actual time=0.062..0.062 rows=20 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB Buffers: shared hit=1 -> Seq Scan on categories c (cost=0.00..1.20 rows=20 width=19) (actual time=0.004..0.028 rows=20 loops=1) Buffers: shared hit=1 Total runtime: 9473.257 ms

Смотрите этот план на сайте explain.depesz.com.

Я просто хочу знать, почему это происходит, т.е. почему индексы делают запрос ужасно медленным по сравнению с последовательным сканированием.

Изменить: Думаю, мне удалось открыть пару вещей, просмотрев документацию postgresql. Postgresql решил выполнить seq-сканирование в некоторой таблице, например, ставки и элементы, потому что он предсказал, что он должен извлекать каждую строку в таблице (сравнивайте количество строк в скобке до фактического времени и количество строк в фактической временной части). Последовательное сканирование лучше всего извлекает все строки. В этой части ничего не может быть сделано.

Я создал дополнительный индекс для categories(name), и результат ниже - это то, что у меня есть. Это как-то улучшилось, но теперь хеш-соединение заменяется вложенным циклом. Любая подсказка в почему?

GroupAggregate (cost=0.00..119552.02 rows=20 width=23) (actual time=617.330..7725.314 rows=20 loops=1) Buffers: shared hit=178582 read=37473 written=14, temp read=2435 written=436 -> Nested Loop (cost=0.00..115051.55 rows=600036 width=23) (actual time=0.120..6186.496 rows=600036 loops=1) Buffers: shared hit=178582 read=37473 written=14, temp read=2109 written=110 -> Nested Loop (cost=0.00..26891.55 rows=50000 width=19) (actual time=0.066..2827.955 rows=50000 loops=1) Join Filter: (c.id = i.category) Rows Removed by Join Filter: 950000 Buffers: shared hit=2 read=7334 written=1, temp read=2109 written=110 -> Index Scan using categories_name_idx on categories c (cost=0.00..12.55 rows=20 width=19) (actual time=0.039..0.146 rows=20 loops=1) Buffers: shared hit=1 read=1 -> Materialize (cost=0.00..8280.00 rows=50000 width=8) (actual time=0.014..76.908 rows=50000 loops=20) Buffers: shared hit=1 read=7333 written=1, temp read=2109 written=110 -> Seq Scan on items i (cost=0.00..7834.00 rows=50000 width=8) (actual time=0.007..170.464 rows=50000 loops=1) Buffers: shared hit=1 read=7333 written=1 -> Index Scan using bid_itemid_idx on bids b (cost=0.00..1.60 rows=16 width=8) (actual time=0.016..0.036 rows=12 loops=50000) Index Cond: (item_id = i.id) Buffers: shared hit=178580 read=30139 written=13 Total runtime: 7726.392 ms

Посмотрите на план здесь, если это лучше.

Мне удалось уменьшить его до 114062.92, создав индекс для категории (id) и items(category). Postgresql использовал оба индекса для достижения стоимости 114062,92. Однако теперь postgresql играет со мной, не используя индекс! почему это так глючит?

qaru.site

indexing - Оптимизация запросов Postgres

Зачем?

Самый логичный ответ - из-за того, как настроены ваши таблицы базы данных.

Если вы не разместите свою схему таблиц, я могу только опасаться, что ваши индексы не имеют высокой мощности.

то есть, если ваш индекс содержит слишком много информации, чтобы быть полезной, то она будет намного менее эффективной или даже более медленной.

Кардинальность - это показатель того, насколько уникальной является строка в вашем индексе. Чем меньше мощность, тем медленнее будет ваш запрос.

Прекрасным примером является наличие булева поля в вашем индексе; возможно, у вас есть таблица контактов в вашей базе данных, и она имеет логический столбец, который записывает true или false в зависимости от того, с кем клиент хотел бы связаться со сторонним лицом.

В среднем, если вы выбрали * из контактов, где OptIn = true '; вы можете себе представить, что вы вернете много контактов; представьте 50% контактов в нашем случае.

Теперь, если вы добавите этот столбец "Optin" в индекс в той же таблице; Разумеется, независимо от того, насколько хороши другие селекторы, вы всегда будете возвращать 50% таблицы из-за значения "OptIn".

Это прекрасный пример низкой мощности; он будет медленным, потому что любой запрос, связанный с этим индексом, должен будет выбрать 50% строк в таблице; чтобы затем использовать другие WHERE-фильтры, чтобы снова уменьшить набор данных.

Короче; Если ваши индексы включают плохие поля или просто представляют каждый столбец в таблице; то SQL-движок должен прибегнуть к тестированию строки-по-агонизирующей строке.

Во всяком случае, вышесказанное теоретично в вашем случае; но это известная общая причина того, почему запросы внезапно начинают занимать гораздо больше времени.

Пожалуйста, заполните пробелы в отношении структуры данных, определений индексов и фактического запроса, который очень медленный!

qaru.site

Возможности оптимизации запросов в postgresql

Когда я смотрю план исполнения, как это, то, что выпрыгивает на меня, это «Seq Scan». Обучите свои глаза, чтобы «Seq Scan» выскочил на вас.

индекс столбцы, используемые в ИНЕКЕ

Одно правило для дизайна таблицы говорит, «индекс каждый столбец используется в ИНЕКЕ.» С одной стороны, я бы хотел увидеть индекс «ol_delivery_d». С другой стороны, я не думаю, что планировщик запросов использовал бы это в этом конкретном случае.

Предложение WHERE показывает, что вы вычисляете агрегаты на 1,3 миллиона строк и исключая только полмиллиона. Я бы не удивился, если бы планировщик запросов выбрал последовательное сканирование в этом случае, даже если были индексом, и здесь быстро и грязные тесты показывают, что это так. (Мои быстрые и грязные тесты могут быть неправильно, хотя мои данные не ваши данные,.. Мой сервер не сервер)

Если есть не указательном на «ol_delivery_d», создать. Это вряд ли поможет в этом запросе, но это поможет запросам, которые возвращают гораздо меньшее количество строк.

Используйте правильный тип данных

У вас есть столбец с именем «ol_delivery_d», который предполагает конструктор таблицы, предназначенный для хранения какого-то из даты доставки. Отсутствие данных времени, «00: 00: 00.000000», также предполагает, что вы запрашиваете по дате, а не по метке времени. Но в плане исполнения показано, что ваша ценность заносится в «метку времени без часового пояса».

Если вы запрашиваете только даты, измените тип данных этого столбца на «дату». Тип данных «дата» уже, чем «метка времени» - больше данных вписывается на страницу, индексы меньше, ваш запрос должен выполняться немного быстрее.

Избегайте внешних сортов

Наконец, метод сортировки для этого запроса является оптимальным. Сортировка в памяти намного быстрее, чем внешние.

Другие вещи, которые вы можете сделать

Есть целый много других вещей, которые вы можете сделать, чтобы запросы выполняются быстрее. (См. PostgreSQL wiki.) Удвоение скорости вашего сервера сделает их быстрее. Как правило, помогают создавать новые табличные пространства на SSD и перемещать таблицы и индексы в эти новые табличные пространства. Материализованное представление может или не поможет; Я не думаю, что это поможет в этом случае, но это не помешает попробовать.

stackoverrun.com

sql - Оптимизация запросов Postgresql

У меня есть две таблицы, и я должен запросить мою базу данных postgresql. В таблице 1 содержится около 140 миллионов записей, а в таблице 2 содержится около 50 миллионов записей о следующем.

таблица 1 имеет следующую структуру:

tr_id bigint NOT NULL, # this is the primary key query_id numeric(20,0), # indexed column descrip_id numeric(20,0) # indexed column

а таблица 2 имеет следующую структуру

query_pk bigint # this is the primary key query_id numeric(20,0) # indexed column query_token numeric(20,0)

Образец db таблицы 1 будет

1 25 96 2 28 97 3 27 98 4 26 99

Образец db таблицы2 будет

1 25 9554 2 25 9456 3 25 9785 4 25 9514 5 26 7412 6 26 7433 7 27 545 8 27 5789 9 27 1566 10 28 122 11 28 1456

Я предпочитаю запросы, в которых я мог бы запрашивать в блоках tr_id. В диапазоне 10 000, поскольку это мое требование.

Я хотел бы получить вывод следующим образом

25 {9554,9456,9785,9514} 26 {7412,7433} 27 {545,5789,1566} 28 {122,1456}

Я пробовал следующим образом

select query_id, array_agg(query_token) from sch.table2 where query_id in (select query_id from sch.table1 where tr_id between 90001 and 100000) group by query_id

Я выполняю следующий запрос, который занимает около 121346 мс, и когда запускается около 4 таких запросов, это все еще занимает больше времени. Можете ли вы помочь мне оптимизировать то же самое.

У меня есть машина, которая работает на Windows 7 с i7 2nd gen proc с 8 ГБ оперативной памяти.

Ниже приведена моя конфигурация postgresql

shared_buffers = 1GB effective_cache_size = 5000MB work_mem = 2000MB

Что делать, чтобы оптимизировать его.

Спасибо

EDIT: было бы здорово, если бы результаты упорядочивались в следующем формате

25 {9554,9456,9785,9514} 28 {122,1456} 27 {545,5789,1566} 26 {7412,7433}

т.е. в соответствии с порядком запроса, представленным в таблице 1, упорядоченным с помощью tr_id. Если это вычислительно дорогое может быть в клиентском коде, я бы попытался его оптимизировать. Но я не уверен, насколько это было бы эффективно.

Спасибо

qaru.site

database - Оптимизация запросов PostgreSQL

У меня такой запрос, и я пытаюсь его оптимизировать. Для выполнения требуется около 1 секунды. Это как-то узкое место в производительности, потому что оно работает несколько раз в секунду.

Вот запрос:

SELECT "spoleczniak_tablica"."id", "spoleczniak_tablica"."postac_id", "spoleczniak_tablica"."hash", "spoleczniak_tablica"."typ", "spoleczniak_tablica"."ikona", "spoleczniak_tablica"."opis", "spoleczniak_tablica"."cel", "spoleczniak_tablica"."data", "postac_postacie"."id", "postac_postacie"."user_id", "postac_postacie"."avatar", "postac_postacie"."ikonka", "postac_postacie"."imie", "postac_postacie"."nazwisko", "postac_postacie"."pseudonim", "postac_postacie"."plec", "postac_postacie"."wzrost", "postac_postacie"."waga", "postac_postacie"."ur_tydz", "postac_postacie"."ur_rok", "postac_postacie"."ur_miasto_id", "postac_postacie"."akt_miasto_id", "postac_postacie"."kasa", "postac_postacie"."punkty", "postac_postacie"."zmeczenie", "postac_postacie"."zdrowie", "postac_postacie"."kariera" FROM "spoleczniak_tablica" INNER JOIN "postac_postacie" ON ("spoleczniak_tablica"."postac_id" = "postac_postacie"."id") WHERE spoleczniak_tablica.postac_id = 1 or spoleczniak_tablica.id in(select wpis_id from spoleczniak_oznaczone where etykieta_id in(select tag_id from spoleczniak_subskrypcje where postac_id = 1)) or (spoleczniak_tablica.postac_id in(select obserwowany_id from spoleczniak_obserwatorium where obserwujacy_id = 1) and hash not in('dyskusja', 'kochanie', 'szturniecie')) or (spoleczniak_tablica.cel = 1 and spoleczniak_tablica.hash in('dyskusja', 'kochanie', 'obserwatorium', 'szturchniecie')) or spoleczniak_tablica.hash = 'administracja-info' or exists(select 1 from spoleczniak_komentarze where kredka_id = spoleczniak_tablica.id and postac_id = 1) ORDER BY "spoleczniak_tablica"."id" DESC LIMIT 21;

И вот EXPLAIN ANALYZE:

Limit (cost=52.80..184755.97 rows=21 width=282) (actual time=80.637..229.161 rows=21 loops=1) -> Nested Loop (cost=52.80..27584240184.45 rows=3136216 width=282) (actual time=80.637..229.153 rows=21 loops=1) -> Index Scan Backward using spoleczniak_tablica_pkey on spoleczniak_tablica (cost=52.80..27583220399.44 rows=3136216 width=193) (actual time=80.620..228.767 rows=21 loops=1) Filter: ((postac_id = 1) OR (SubPlan 1) OR ((hashed SubPlan 2) AND ((hash)::text <> ALL ('{dyskusja,kochanie,szturniecie}'::text[]))) OR ((cel = 1) AND ((hash)::text = ANY ('{dyskusja,kochanie,obserwatorium,szturchniecie}'::text[]))) OR ((hash)::text = 'administracja-info'::text) OR (alternatives: SubPlan 3 or hashed SubPlan 4)) SubPlan 1 -> Materialize (cost=13.22..11858.79 rows=1255820 width=4) (actual time=0.008..0.044 rows=486 loops=1517) -> Nested Loop (cost=13.22..673.69 rows=1255820 width=4) (actual time=11.818..14.028 rows=486 loops=1) -> HashAggregate (cost=5.89..5.90 rows=1 width=4) (actual time=0.051..0.056 rows=7 loops=1) -> Index Scan using spoleczniak_subskrypcje_postac_id on spoleczniak_subskrypcje (cost=0.00..5.88 rows=2 width=4) (actual time=0.022..0.046 rows=7 loops=1) Index Cond: (postac_id = 1) -> Bitmap Heap Scan on spoleczniak_oznaczone (cost=7.33..662.99 rows=384 width=8) (actual time=1.708..1.978 rows=69 loops=7) Recheck Cond: (etykieta_id = spoleczniak_subskrypcje.tag_id) -> Bitmap Index Scan on spoleczniak_oznaczone_etykieta_id (cost=0.00..7.23 rows=384 width=0) (actual time=1.694..1.694 rows=69 loops=7) Index Cond: (etykieta_id = spoleczniak_subskrypcje.tag_id) SubPlan 2 -> Index Scan using spoleczniak_obserwatorium_obserwujacy_id on spoleczniak_obserwatorium (cost=0.00..39.53 rows=21 width=4) (actual time=0.041..0.192 rows=26 loops=1) Index Cond: (obserwujacy_id = 1) SubPlan 3 -> Bitmap Heap Scan on spoleczniak_komentarze (cost=18.63..20.64 rows=1 width=0) (never executed) Recheck Cond: ((kredka_id = spoleczniak_tablica.id) AND (postac_id = 1)) -> BitmapAnd (cost=18.63..18.63 rows=1 width=0) (never executed) -> Bitmap Index Scan on spoleczniak_komentarze_kredka_id (cost=0.00..2.98 rows=24 width=0) (never executed) Index Cond: (kredka_id = spoleczniak_tablica.id) -> Bitmap Index Scan on spoleczniak_komentarze_postac_id (cost=0.00..15.40 rows=885 width=0) (never executed) Index Cond: (postac_id = 1) SubPlan 4 -> Index Scan using spoleczniak_komentarze_postac_id on spoleczniak_komentarze (cost=0.00..1616.70 rows=885 width=4) (actual time=0.044..54.812 rows=3607 loops=1) Index Cond: (postac_id = 1) -> Index Scan using postac_postacie_pkey on postac_postacie (cost=0.00..0.31 rows=1 width=89) (actual time=0.012..0.014 rows=1 loops=21) Index Cond: (id = spoleczniak_tablica.postac_id)

Если я удалю ORDER BY, запрос потребуется всего 2-3 мс. Какие-либо предложения?

qaru.site

sql - Postgres, где оптимизация запросов

В нашей базе данных есть таблица menus, имеющая 515502 строки. Он имеет столбец status, который имеет тип smallint.

В настоящее время простой запрос счетчика занимает 700 мс для набора документов, имеющих значение status как 3.

explain analyze select count(id) from menus where status = 2; Aggregate (cost=72973.71..72973.72 rows=1 width=4) (actual time=692.564..692.565 rows=1 loops=1) -> Bitmap Heap Scan on menus (cost=2510.63..72638.80 rows=133962 width=4) (actual time=28.179..623.077 rows=135429 loops=1) Recheck Cond: (status = 2) Rows Removed by Index Recheck: 199654 -> Bitmap Index Scan on menus_status (cost=0.00..2477.14 rows=133962 width=0) (actual time=26.211..26.211 rows=135429 loops=1) Index Cond: (status = 2) Total runtime: 692.705 ms (7 rows)

Некоторые строки имеют значение столбца 1, для которого запрос выполняется очень быстро.

explain analyze select count(id) from menus where status = 4; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=7198.73..7198.74 rows=1 width=4) (actual time=24.926..24.926 rows=1 loops=1) -> Bitmap Heap Scan on menus (cost=40.53..7193.53 rows=2079 width=4) (actual time=1.461..23.418 rows=2220 loops=1) Recheck Cond: (status = 4) -> Bitmap Index Scan on menus_status (cost=0.00..40.02 rows=2079 width=0) (actual time=0.858..0.858 rows=2220 loops=1) Index Cond: (status = 4) Total runtime: 25.089 ms (6 rows)

Я заметил, что наиболее общий индекс btree - лучшая стратегия индексирования для простых запросов, основанных на равенстве. И gin, и hash были медленнее, чем btree.

Любые советы по созданию запросов count быстрее для любого фильтра, использующего индекс?

Я понимаю, что это вопрос начального уровня, поэтому заранее извиняюсь за любые ошибки, которые я мог бы сделать.

источник поделиться

qaru.site