RE: slow join on postgresql6.5

> -----Original Message-----
> From: Don Baccus [mailto:dhogaza@pacifier.com]
> Sent: Friday, March 31, 2000 11:34 PM
>
> 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".
>

Oops I misunderstood NULL.

Hmm,is the following TODO worth the work ?
* Use index to restrict rows returned by multi-key index when used with non-consecutive keys or OR clauses, so fewer
heapaccesses.
 

Probably this is for the case likeSELECT .. FROM .. WHERE key1 = val1 and key3 = val3;
,where (key1,key2,key3) is a multi-column index.
Currently index scan doesn't take 'key3=val3' into account because
(key1,key3) isn't consecutive.
The TODO may include the caseSELECT .. FROM .. WHERE key2 = val2;
Though we have to scan the index entirely,access to the main table
is needed only when key2 = val2. If (key2 = val2) is sufficiently
restrictive,
the scan would be faster than simple sequential scan.

Comments ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp