Re: Feature request: smarter use of conditional indexes

Поиск
Список
Период
Сортировка
От Christopher Kings-Lynne
Тема Re: Feature request: smarter use of conditional indexes
Дата
Msg-id 40468704.9030601@familyhealth.com.au
обсуждение исходный текст
Ответ на Re: Feature request: smarter use of conditional indexes  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
>>Given an index like this:
>>    CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL;
>>and a query like this:
>>    SELECT * FROM t1 WHERE c1 = 123;
>>I'd like the planner to be smart enough to use an index scan using i1.
>
>
> Send a patch ;-)
>
> The routine you want to teach about this is pred_test_simple_clause() in
> src/backend/optimizer/path/indxpath.c.  ISTM that it's legitimate to
> conclude that "foo IS NOT NULL" is implied by "foo op anything" or
> "anything op foo" if the operator is marked strict.

I've actually mentioned this one before in that of all the partial
indexes I have, almost all of then are a WHERE x IS NOT NULL format.  I
don't know if that's a common use, but if it is, then maybe it's worth
just adding the knowledge for IS NOT NULL...

The other thing is that at the moment, cascading foreign keys will not
use partial indexes even if they match the predicate.  Maybe an IS NOT
NULL hack will help there...

Chris


В списке pgsql-performance по дате отправления:

Предыдущее
От: Paul Thomas
Дата:
Сообщение: Re: Scaling further up
Следующее
От: Florian Weimer
Дата:
Сообщение: Re: Bulk INSERT performance in 7.4.1