From: | "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> |
---|---|
To: | "Don Baccus" <dhogaza(at)pacifier(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Wenjin Zheng" <wenjin(dot)zheng(at)lsbc(dot)com>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | RE: slow join on postgresql6.5 |
Date: | 2000-04-02 15:16:28 |
Message-ID: | NDBBIJLOILGIKBGDINDFAEMECDAA.Inoue@tpf.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> -----Original Message-----
> From: Don Baccus [mailto:dhogaza(at)pacifier(dot)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(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".
>
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 heap accesses.
Probably this is for the case like
SELECT .. 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 case
SELECT .. 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(at)tpf(dot)co(dot)jp
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Lockhart | 2000-04-02 15:35:38 | Re: Call for porting reports |
Previous Message | Magnus Hagander | 2000-04-02 14:32:29 | Crash on UPDATE in 7.0beta3 |