PostgreSQL: лучший способ объединения небольших подмножеств больших таблиц

Question

Я использую PostgreSQL 9.3 на компьютере с Windows. Допустим, у меня есть таблица клиентов с 4 миллионами записей и таблица заказов с 25 миллионами записей. Однако меня интересуют только мои нью-йоркские клиенты. Есть только 5 000 нью-йоркских клиентов, которые разместили 15 000 заказов, то есть значительно меньше.

Каков наилучший способ получения идентификаторов клиентов и общего количества заказов, когда-либо размещенных клиентами из Нью-Йорка?

Это коррелированный подзапрос, как

Select c.clientid
, ( select count(orders.clientid) from orders where orders.clientid = c.clientid) as NumOrders

From clients c
WHERE c.city = 'New York'

быстрее, чем соединение, как

Select c.clientid
,coalesce(o.NumOrders,0) as NumOrders

From clients c

Left outer join
( select clientid, count(*) as NumOrders from orders group by clientid ) o
on c.clientid = o.clientid

WHERE c.city = 'New York'

потому что последний проводит большую часть времени, считая записи, которые затем отбрасываются, поскольку они не относятся к клиентам из Нью-Йорка? Или есть лучший способ?

Спасибо!

PS Да, я знаю, я должен посмотреть на план выполнения, но я пишу это из дома, и у меня нет базы данных с миллионами записей, чтобы проверить это.

Answer 1

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

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

Итак, что-то вроде:

CREATE TEMP TABLE tmp_clients AS
SELECT c.clientid
FROM clients c
WHERE c.city = 'New York'
ORDER BY c.clientid;

SELECT *
FROM orders AS o
JOIN tmp_clients AS c ON (o.clientid = c.clientid)
ORDER BY o.clientid;

Сюда, tmp_clients содержит только нью-йоркских клиентов - ~5 тыс. строк - и именно эта таблица будет присоединена к таблице заказов.

Вы также можете, для дальнейшей оптимизации, создать индекс для временной таблицы (для клиентской базы), а затем ANALYZE это, прежде чем делать JOIN чтобы убедиться, что JOIN выполняется исключительно по индексу. Вы хотите проверить планы запросов в каждом случае, чтобы увидеть относительную разницу (или просто имейте это в виду, если JOIN не так быстро, как хотелось бы).

Ответ на комментарий от @poshest:

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

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

Answer 2

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

Во втором случае вы используете левое внешнее соединение в подзапросе, что немного странно. Это сохранит значения для клиентов, у которых нет заказов. Это немного похоже на разговор о клиентах, которые никогда ничего не покупали - действительно ли они клиенты? Я собираюсь сказать "Нет", потому что это мой ответ, и я могу придумать все, что мне нравится. Но я видел случаи, когда имеет смысл говорить о клиентах, на которых вы никогда не работали. (У юридических фирм иногда есть клиенты, у которых нет оплачиваемых часов.)

Тем не мение.,,

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

select o.clientid, count(*)
from orders o
inner join clients c on c.clientid = o.clientid
       and c.city = 'New York'
group by o.clientid;

Вы будете хотеть указатель на "город".

---

Я построил, заполнил и проиндексировал некоторые таблицы со случайными (ish) данными. Простой, простой запрос был быстрее на порядок.

explain analyze
Select c.clientid
      ,coalesce(o.NumOrders,0) as NumOrders
From clients c
Left outer join
    ( select clientid, count(*) as NumOrders from orders group by clientid ) o
on c.clientid = o.clientid
WHERE c.city = 'New York';

"Объединение правого слияния (стоимость = 4813633.08..5022834.83 строки = 5200 ширина = 12) (фактическое время = 105751.121..117901.326 строк = 5000 циклов = 1)"
"Объединить Cond: (orders.clientid = c.clientid)"
"  ->  GroupAggregate  (стоимость = 4799762.91..4996891.51 строк = 962770 ширина = 4) (фактическое время = 105702.262..117735.305 строк = 1000000 циклов =1)"
"        -> Сортировка (стоимость = 4799762.91..4862263.21 строк = 25000120 ширина = 4) (фактическое время = 105688.877..113148.881 строк = 25000000 циклов = 1)"
"Ключ сортировки: orders.clientid"
"Метод сортировки: внешний диск слияния: 342176kB"
"        ->  Seq Scan для заказов (стоимость = 0,00..360621.20 строк = 25000120 ширина = 4) (фактическое время = 14.866..35814.499 строк = 25000000 циклов =1)"
"        -> Сортировка (стоимость = 13870.18..13883.18 строк = 5200 ширины = 4) (фактическое время = 39.536..40.241 строк = 5000 циклов = 1)"
"Ключ сортировки: c.clientid"
"Метод сортировки: быстрая сортировка Память: 427 КБ"
"-> Сканирование кучи растрового изображения на клиентах c (стоимость = 144.73..13549.22 строк = ширина 5200 = 4) (фактическое время = 29.556..30.638 строк = 5000 циклов = 1)"
"Перепроверьте Cond: ((город)::text = 'Нью-Йорк'::text)"
"              -> Сканирование индекса растрового изображения на clients_city_idx  (стоимость = 0.00..143.43 строки = 5200 ширина = 0) (фактическое время = 29.538..29.538 строк = 5000 циклов =1)"
"                    Index Cond: ((город)::text = 'Нью-Йорк':: text)"
"Общее время выполнения: 118027,256 мс"

explain analyze
select o.clientid, count(*)
from orders o
inner join clients c on c.clientid = o.clientid
       and c.city = 'New York'
group by o.clientid;

"GroupAggregate (стоимость = 595747.05..596315.80 строк = 32500 ширина = 4) (фактическое время = 9167.518..9179.855 строк = 1250 циклов = 1)" "-> Сортировка (стоимость = 595747.05..595828.30 строк = 32500 ширина = 4) (фактическое время = 9167.504..9174.135 строк = 31336 циклов = 1) "" Ключ сортировки: o.clientid "" Метод сортировки: внешнее объединение Диск: 432kB "" -> Хеш-соединение (стоимость = 13614.22..593311.47 строк = 32500 ширины = 4) (фактическое время = 3.055..9123.568 строк = 31336 циклов = 1) "" Hash Cond: (o.clientid = c.clientid)"
"        ->  Seq Сканирование заказов o (стоимость = 0.00..360621.20 строк = Ширина 25000120 = 4) (фактическое время = 0.041..3833.387 строк = 25000000 циклов = 1) "" -> Хеш (стоимость = 13549.22..13549.22 строк = 5200 ширины = 4) (фактическое время = 2.915..2.915 строк =5000 loops=1)"
"                    Buckets: 1024 Пакеты: 1 Использование памяти: 176kB"
"                    -> Сканирование кучи растровых изображений на клиентах c (стоимость = 144.73..13549.22 строк = ширина 5200 = 4) (фактическое время = 0.672..1.769 строк = 5000 циклов = 1) "" Перепроверить Cond: ((город)::text = 'New York'::text)"
"              -> Сканирование индекса растрового изображения на clients_city_idx  (стоимость = 0.00..143.43 строк = 5200 ширина = 0) (фактическое время = 0.658..0.658 строк = 5000 циклов = 1) "" Index Cond: ((город)::text = 'New York':: text) "" Общее время выполнения: 9180,291 мс "