Блог

#sql #postgresql #subquery #where #clause

#sql #postgresql #подзапрос #где #предложение

Вопрос:

 create table "country"
(
  country_id integer,  
  country_name varchar(50),
  continent varchar(50),
  constraint country_pkey primary key (country_id)
);

create table "person"
(
  person_id integer,
  person_name varchar(100),
  country_id integer,
  constraint person_pkey primary key (person_id)
);

create table "event"
(
  event_id integer,
  event_desc varchar(100),
  country_id integer,
  constraint event_pkey primary key (event_id)
);
 

 select c.country_name, sum(sub1.person_count) as person_count, sum(sub2.event_count) as event_count
from
  "country" c
  left join (select country_id, count(*) as person_count from "person" group by country_id) sub1
    on (c.country_id=sub1.country_id)
  left join (select country_id, count(*) as event_count from "event" group by country_id) sub2
    on (c.country_id=sub2.country_id)
group by c.country_name
 

 select c.country_name, 
  sum(sub1.person_count) as person_count, 
  sum(sub2.event_count) as event_count
from
  "country" c
  left join (select country_id, count(*) as person_count from "person" group by country_id) sub1
    on (c.country_id=sub1.country_id)
  left join (select country_id, count(*) as event_count from "event" group by country_id) sub2
    on (c.country_id=sub2.country_id)
where c.country_name='UNITED STATES'
group by c.country_name
 

Комментарии:

1. Как выглядит план объяснения?

2. Это только гипотетический сценарий. Но в рабочей базе данных, выполняющей очень похожий запрос, он выполнил последовательное сканирование таблиц в подзапросе. Он никогда не использовал индекс. И эти таблицы были большими.

Ответ №1:

 from
  "country" c
  left join sub1 on (c.country_id=sub1.country_id)
  left join sub2 on (c.country_id=sub2.country_id)
 

 from
  "country" c
  left join (select * from sub1 where country_id = ?)
    on (c.country_id=sub1.country_id)
  left join (select * from sub2 where country_id = ?)
    on (c.country_id=sub2.country_id)
 

Комментарии:

1. Но разве современные системы баз данных не будут выполнять оптимизацию в этом случае? Как вы предлагаете мне изменить запрос, чтобы он оставался гибким / повторно используемым / легко изменяемым и при этом выполнялся быстро? Я не думаю, что целесообразно пытаться заново создавать фильтр для каждого подзапроса.

2. Конечно. Все СУБД будут оптимизировать там, где они могут. Но вы четко сообщаете СУБД, что хотите, чтобы sub1 была таблицей из двух столбцов (country_id, person_count), и что вы хотите, чтобы в ней была одна строка для каждого country_id. Те же рассуждения для sub2.

3. Жаль, что у нас нет хорошего решения для этого. Это значительно усложняет поддержку / адаптацию кода SQL к изменяющимся фильтрам, сохраняя при этом быструю производительность.

4. На концептуальном уровне ваш исходный запрос будет называться примерно так: «подсчитайте количество событий для одной страны». Его имя и имена его столбцов являются частью общедоступного интерфейса базы данных. Предложения WHERE реализуют часть «для одной страны». Вы могли бы написать функцию PostgreSQL, которая принимает название страны в качестве параметра и заменяет этот параметр во всех необходимых предложениях WHERE. Любая более сложная фильтрация — скажем, по населению, или событию, или полу человека, или адресам — не имеет смысла в объекте СУБД с именем «count person events для одной страны».

5. Преимущество запроса … выберите <список полей> из «country» c левое соединение (выберите country_id, count( ) как person_count из группы «person» по country_id) sub1 on (c.country_id=sub1.country_id) левое соединение (выберите country_id, count( ) как event_count из «event» группа по country_id) sub2 на (c.country_id=sub2.country_id) группа по <список группирующих полей> … заключается в том, что я могу легко изменить список полей и список группировок, чтобы сделать его по континенту или по конкретной стране, не изменяя предложение from . Это упрощает адаптацию / поддержку кода SQL.

Ответ №2:

Комментарии:

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

2. Конечно, они это делают. Потому что вы не фильтруете данные. Индекс используется только тогда, когда оптимизатор считает, что вы извлекаете небольшое количество строк (используя индекс для быстрого сканирования). Когда вы сканируете всю таблицу, быстрее проходить через всю таблицу, чтобы получить все записи. Итак … оптимизатор в порядке, и вам следует прочитать документы о стратегиях индексации 🙂

3. У меня есть фильтр «where c.country_name =’СОЕДИНЕННЫЕ ШТАТЫ'». Итак, почему подзапросы все еще сканируют всю таблицу?

4. из-за оптимизатора, потому что он работает таким образом. Он начинает анализировать запрос снизу вверх. Итак, в вашем случае сначала будет выполняться два подзапроса и будут получены два подмножества. Затем он присоединится к country таблице it и отфильтрует данные. Пожалуйста, объясните этот запрос, и вы его увидите.

5. Могу ли я что-нибудь сделать, чтобы избежать помещения фильтра внутри подзапроса? Допустим, я хочу превратить этот запрос в представление. Это означало бы, что использование этого представления будет очень медленным, если вы фильтруете определенную страну. Было бы невозможно заставить это представление работать быстро, если вы фильтруете определенную страну, потому что это означало бы, что вам нужно изменить внутренние подзапросы, чтобы также иметь свой фильтр.