Разница в производительности: условие, установленное в позиции INNER JOIN vs WHERE

Скажем, что у меня есть таблица order как

id | clientid | type | amount | itemid | date
---|----------|------|--------|--------|-----------
23 | 258      | B    | 150    | 14     | 2012-04-03
24 | 258      | S    | 69     | 14     | 2012-04-03
25 | 301      | S    | 10     | 20     | 2012-04-03
26 | 327      | B    | 54     | 156    | 2012-04-04

clientid - это внешний ключ обратно в таблицу client
itemid - это внешний ключ обратно в таблицу item
type - это только B или S
amount - целое число

и таблицу processed как

id | orderid | processed | date
---|---------|-----------|---------
41 | 23      | true      | 2012-04-03
42 | 24      | true      | 2012-04-03
43 | 25      | false     | <NULL>
44 | 26      | true      | 2012-04-05

Мне нужно получить все строки из order, которые для того же clientid на одном и том же date имеют противоположные значения type. Помните, что type может иметь только одно из двух значений - B или S. В приведенном выше примере это будут строки 23 и 24.

Другим ограничением является то, что соответствующая строка в processed должна быть true для orderid.

Мой запрос пока

SELECT c1.clientid,
       c1.date,
       c1.type,
       c1.itemid,
       c1.amount,
       c2.date,
       c2.type,
       c2.itemid,
       c2.amount

FROM   order c1
INNER JOIN order c2 ON c1.itemid    =  c2.itemid AND
                       c1.date      =  c2.date   AND
                       c1.clientid  =  c2.clientid AND
                       c1.type     <>  c2.type AND
                       c1.id        <  c2.id

INNER JOIN processed p1 ON p1.orderid   =  c1.id AND
                         p1.processed =  true
INNER JOIN processed p2 ON p2.orderid   =  c2.id AND
                         p2.processed =  true

ВОПРОС: Сохранение processed = true как части предложения соединения замедляет запрос вниз. Если я переведу его в предложение WHERE, производительность будет намного лучше. Это вызвало мой интерес и , я хотел бы знать, почему.

Первичные ключи и соответствующие столбцы внешнего ключа индексируются, а столбцы значений (value, processed и т.д.) не являются.

Отказ от ответственности: я унаследовал эту структуру БД, а разница в производительности составляет примерно 6 секунд.

Ответ 1

Причина, по которой вы видите разницу, связана с планом выполнения, который планировщик собирает вместе, это, очевидно, отличается в зависимости от запроса (возможно, он должен оптимизировать два запроса одинаковыми, и это может быть ошибкой). Это означает, что планировщик считает, что он должен работать определенным образом, чтобы получить результат в каждом утверждении.

Когда вы делаете это в JOIN, планировщику, вероятно, придется выбирать из таблицы, фильтровать по "True", а затем присоединяться к наборам результатов. Я бы предположил, что это большая таблица и, следовательно, много данных для просмотра, и она не может эффективно использовать индексы.

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

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

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

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

Дополнительный материал для чтения:

http://www.postgresql.org/docs/current/static/explicit-joins.html

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