INDEX и JOIN по NULL значениям

Работаю с достаточно простым запросом и заметил странное поведение PostgreSQL:

EXPLAIN ANALYZE SELECT *
  FROM ZReport_receiptSaleDetail t1
  JOIN Stock_batch t0 ON t0.key0=t1.ZReport_batchReceiptSaleDetail_ReceiptSaleDetail

Особенность запроса, что обе таблицы большие, но batchReceiptSaleDetail практически всегда NULL (postgre об этом знает, статистика обновлена). При этом по этому полю построен индекс.

План запроса следующий:

"Hash Join  (cost=18739.23..220985.32 rows=1 width=1261) (actual time=434.276..434.276 rows=0 loops=1)"
"  Hash Cond: (t1.zreport_batchreceiptsaledetail_receiptsaledetail = t0.key0)"
"  ->  Seq Scan on zreport_receiptsaledetail t1  (cost=0.00..76855.24 rows=3425024 width=91) (actual time=0.005..258.117 rows=3425024 loops=1)"
"  ->  Hash  (cost=5494.99..5494.99 rows=83299 width=1170) (actual time=75.045..75.045 rows=83299 loops=1)"
"        Buckets: 1024  Batches: 128  Memory Usage: 281kB"
"        ->  Seq Scan on stock_batch t0  (cost=0.00..5494.99 rows=83299 width=1170) (actual time=0.001..7.012 rows=83299 loops=1)"
"Total runtime: 434.485 ms"

Как видим индекс она использовать не решается.

Если отключить enable_hashjoin, запрос работает быстрее:

"Nested Loop  (cost=0.43..527644.53 rows=1 width=1261) (actual time=85.152..85.152 rows=0 loops=1)"
"  ->  Seq Scan on stock_batch t0  (cost=0.00..5494.99 rows=83299 width=1170) (actual time=0.004..6.228 rows=83299 loops=1)"
"  ->  Index Scan using nn on zreport_receiptsaledetail t1  (cost=0.43..6.26 rows=1 width=91) (actual time=0.001..0.001 rows=0 loops=83299)"
"        Index Cond: (zreport_batchreceiptsaledetail_receiptsaledetail = t0.key0)"
"Total runtime: 85.218 ms"

Из этого плана можно сделать вывод что вариант с индексом у нее есть, и статистику она представляет (estimate результата 1 rows). Но план этот, она не применяет видимо из-за того что по ее мнению средний cost все же чуть выше. (хоть и ошибочно)

А вот если чуть видоизменить запрос:

EXPLAIN ANALYZE SELECT *
  FROM ZReport_receiptSaleDetail t1
  JOIN Stock_batch t0 ON t0.key0=t1.ZReport_batchReceiptSaleDetail_ReceiptSaleDetail WHERE t1.ZReport_batchReceiptSaleDetail_ReceiptSaleDetail IS NOT NULL

То план становится "идеальным":

"Nested Loop  (cost=0.72..16.77 rows=1 width=1261) (actual time=0.009..0.009 rows=0 loops=1)"
"  ->  Index Scan using nn on zreport_receiptsaledetail t1  (cost=0.43..8.45 rows=1 width=91) (actual time=0.008..0.008 rows=0 loops=1)"
"        Index Cond: (zreport_batchreceiptsaledetail_receiptsaledetail IS NOT NULL)"
"  ->  Index Scan using pk_stock_batch on stock_batch t0  (cost=0.29..8.31 rows=1 width=1170) (never executed)"
"        Index Cond: (key0 = t1.zreport_batchreceiptsaledetail_receiptsaledetail)"
"Total runtime: 0.042 ms"

При этом с логической точки зрения запросы эквивалентны : a=b при b is null в данном случае по сути равен false, то есть из a=b => b is not null. Вопрос почему постгре этого не понимает, и может ли он это вообще понять и использовать последний план?

Nitro_Junkie,

Есть много вещей которые понимает програмист или DBA и не умеет/не понимает планировщик.
Это ваша работа переписывать запросы в более эффективной форме.
Далеко не все возможные логические выводы делаются планировщиком так как время на планирования запроса всетаки тоже надо держать в рамках.

>>Вопрос почему постгре этого не понимает, и может ли он это вообще понять и использовать последний план?
Не сделано чтобы не утяжелять планировщик для узких use case когда можно запрос нормально переписать.
Нет не может.

PS: это не значит что не научится в будущем впрочем.

PPS: я бы кстати порекомендовал переделать индекс
nn on zreport_receiptsaledetail
на условие where ZReport_batchReceiptSaleDetail_ReceiptSaleDetail is not null
и индекс станет в 100 раз меньше и работы базе меньше при вышеупомянутом запросе.

--Maxim Boguk
www.postgresql-consulting.ru

Maxim Boguk

Nitro_Junkie, Есть много вещей которые понимает програмист или DBA и не умеет/не понимает планировщик. Это ваша работа переписывать запросы в более эффективной форме. Далеко не все возможные логические выводы делаются планировщиком так как время на планирования запроса всетаки тоже надо держать в рамках. >>Вопрос почему постгре этого не понимает, и может ли он это вообще понять и использовать последний план? Не сделано чтобы не утяжелять планировщик для узких use case когда можно запрос нормально переписать. Нет не может. PS: это не значит что не научится в будущем впрочем. PPS: я бы кстати порекомендовал переделать индекс nn on zreport_receiptsaledetail на условие where ZReport_batchReceiptSaleDetail_ReceiptSaleDetail is not null и индекс станет в 100 раз меньше и работы базе меньше при вышеупомянутом запросе. --Maxim Boguk www.postgresql-consulting.ru

Thx, так и думал. Case не такой уж и редкий, возникает при достаточно многих денормализациях, или когда есть функционал который не всегда используется...

Nitro_Junkie
Case не такой уж и редкий, возникает при достаточно многих денормализациях, или когда есть функционал который не всегда используется...

Частый это или редкий случай, авторы postgres считают очень просто, по количеству багрепортов, вот вы написали багрепорт? :-)

Гость_0
Nitro_Junkie Case не такой уж и редкий, возникает при достаточно многих денормализациях, или когда есть функционал который не всегда используется... Частый это или редкий случай, авторы postgres считают очень просто, по количеству багрепортов, вот вы написали багрепорт? :-)

Мы если честно, сделали так что если join идет по индексированному полю таблицы, и процент его не null значений существенно влияет на статистику, то в явную добавляем field IS NOT NULL в условие запроса. В принципе это решает проблему в общем случае. Понимаю что немного "эгоистично", но в постгре хватает других проблем с оптимизацией запросов, пусть лучше над ними работают :) ...