RE: slow join on postgresql6.5

At 07:05 PM 3/31/00 +0900, Hiroshi Inoue wrote:
>> -----Original Message-----
>> From: majordomo-owner(at)hub(dot)org [mailto:majordomo-owner(at)hub(dot)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(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.