Skip site navigation (1) Skip section navigation (2)

RE: slow join on postgresql6.5

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 (view raw or flat)
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


In response to

Responses

pgsql-hackers by date

Next:From: Thomas LockhartDate: 2000-04-02 15:35:38
Subject: Re: Call for porting reports
Previous:From: Magnus HaganderDate: 2000-04-02 14:32:29
Subject: Crash on UPDATE in 7.0beta3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group