RE: slow join on postgresql6.5

At 07:05 PM 3/31/00 +0900, Hiroshi Inoue wrote:
>> -----Original Message-----
>> From: majordomo-owner@hub.org [mailto:majordomo-owner@hub.org]On Behalf
>> Of Don Baccus
>> 
>> Whatever ... in this particular case  - referential integrity 
>> with MATCH <unspecified> and MATCH PARTIAL and multi-column
>> foreign keys - performance will likely drop spectacularly once the
>> leading column is NULL, while (say) with Oracle you'd expect much
>> less of a performance hit. 
>>
>
>As for NULL,it seems possible to look up NULL keys in a btree index
>because NULL == NULL for btree indexes.
>I've wondered why PostgreSQL's planner/executor never looks up
>indexes for queries using 'IS NULL'.

Unfortunately for the RI MATCH PARTIAL case, NULL is a "wildcard".

This doesn't affect the validity of your observation in the general
case, though.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.