PostgreSQL: Настройка производительности запроса

#postgresql #performance #query-optimization #postgresql-11

Вопрос:

У меня есть таблицы, как показано ниже:

Имя таблицы: Table_A

A_ID	Имя
21	XYZ
22	IJK

Имя таблицы: Table_S1

S_ID	S_Name	A_ID
123	ABC123	21
124	PQR321	22

Имя таблицы: Table_S2

S_ID	S2_Date_1	S2_Date_2
123	01/01/2015	02/04/2016
124	01/01/2021	02/04/2018
123	нулевой	02/04/2019
124	01/01/2017	нулевой

Table_A и Table_S1 имеют первичные ключи, а где как Table_S2 не имеет первичного ключа.

Table_S1.A_ID —> ссылается на —>> Первичный ключ A_ID Table_A

Table_S2.S_ID —> ссылается на —>> S_ID первичного ключа Table_S1

В таблице Table_S1 содержится около 400 000 записей, и каждая запись в таблице Table_S1 содержит 120 записей, на которые ссылаются в таблице Table_S2. Это означает, что Table_S2-огромная таблица, содержащая около 48 миллионов записей.

У меня есть требование извлекать годы из столбцов Table_S2 S2_Date_1 и S2_Date_2 по отдельности, используя 2 разных запроса, как показано ниже.

 SELECT DISTINCT EXTRACT(YEAR FROM s2.S2_Date_1)
  FROM Table_S2 s2, Table_S1 s1, Table_A a
 WHERE s2.S_ID = s1.S_ID
   AND s1.A_ID = a.A_ID
   AND s1.B_ID = b.B_ID
   AND a.A_Name = 'IJK';

Если я сгруппируюсь по A_ID, записи будут такими, как показано ниже:

A_ID	счет_из_таблиц 1	Count_of_Table_S2
21	100,000	12,000,000
22	150,000	18,000,000
23	90,000	10,800,000

В таблице Table_S2 нет разделов. Для извлечения всех лет из этих миллионов записей требуется около 1 минуты, мое требование-получить их за секунду. Возможно ли это сделать? Может ли кто-нибудь, пожалуйста, предложить лучший способ сделать это?

Заранее спасибо.

1. Пожалуйста, покажите EXPLAIN (ANALYZE, BUFFERS) выходные данные для запроса, в идеале после настройки track_io_timing = on .

2. Похоже, вам не нужны какие-либо столбцы объединенных таблиц, переписывание древних неявных соединений в СУЩЕСТВУЮЩЕЕ состояние может повысить производительность.

3. Вы не сможете динамически суммировать такое количество записей за секунду. Используйте материализованный вид.

Ответ №1:

У вас есть очень много строк для подведения итогов. Достоверно сократить это до одной секунды может оказаться невозможным.

Вы можете попробовать включить сохраненный сгенерированный столбец Table_S2 , а затем проиндексировать его.

 ALTER TABLE Table_S2
 ADD COLUMN S2_Date1_Year 
            GENERATED ALWAYS AS EXTRACT(YEAR FROM s2.S2_Date_1)
            STORED;

Это дает вам колонку. Затем

 ALTER TABLE TABLE_S2 ADD INDEX year_by_sid (S_ID, S2_Date1_Year);

добавляет индекс. Затем измените свой запрос на

 SELECT DISTINCT s2.S2_Date1_Year ...

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

Вместо этого вам может понадобиться материализованный вид.

 CREATE MATERIALIZED VIEW View_S2_Year AS
SELECT DISTINCT S_ID, EXTRACT(YEAR FROM s2.S2_Date_1) AS S2_Date1_Year
  FROM Table_S2;

CREATE INDEX year_by_sid ON View_S2_Year (S_ID, S2_Date1_Year);

Затем присоединитесь к этому View_S2_Year, а не к Table_S2.

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

 REFRESH MATERIALIZED VIEW View_S2_Year;