At 11:10 AM 3/30/00 -0500, Tom Lane wrote:
>Don Baccus <dhogaza@pacifier.com> writes:
>> This is an example where commercial systems that have indices
>> synchronized with data such that queries referencing only the
>> fields in indices can win big vs. PG in SOME (not all) cases.
>> In particular, when the indices are to a table that has a bunch
>> of other, perhaps long, columns.  PG has to read the table and
>> drag all that dead weight around to do RI referential checking
>> and semantic actions.
>
>Keep in mind, though, that once we have TOAST the long columns are
>likely to get pushed out to a secondary table, so that the amount
>of data you have to read is reduced (as long as you don't touch
>any of the long columns, of course).

Sure...and you can BLOB or CLOB longer data in Oracle, too.  TOASTing
isn't without costs, either...life's a tradeoff!

>
>The main reason that Postgres indexes can't be used without also
>consulting the main table is that we do not store transaction status
>information in index entries, only in real tuples.  After finding
>an index entry we must still consult the referenced tuple to see
>if it's been deleted, or even committed yet.  I believe this is a
>pretty good tradeoff.

I must wonder, though, given that proper syncing seems to be the
norm in commercial systems.  Or so I'm lead to believe when Gray's
book, for instance.  Or a good book on speeding up Oracle queries.

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. 

The point of my note is that this is probably worth documenting.

Don't get me wrong, these semantics and RI and multi-column keys
appear to be pretty inefficient by nature, I don't think anyone
is likely to be horrified to read that it might well be even worse
in PG than in certain commercial systems... 

>I suppose that keeping tuple status in index entries could be a win
>on nearly-read-only tables, but I think that on average it'd be
>a performance loser.

Well...I've personally not studied the issue in detail, but just
have to wonder if the folks at Oracle are really as stupid as the
above analysis would make them appear to be.  I presume that they
have a pretty good idea of the kind of mix large database installations
make, and presumably make choices designed to win on average.



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

Thu, 30 Mar 2000 19:00:05 -0800
Don Baccus <dhogaza@pacifier.com>
Re: slow join on postgresql6.5 
At 12:49 PM 3/30/00 -0500, Bernard Adrian Frankpitt wrote:

>Implementing the change is probably not as bad as it seems, access
>methods are beautifully self-contained in Postgres. The changes are
>quite fundamental though, and you sure would want to get it right. You
>would also want to retain the current behavior for users who do lots of
>updates.

Again, if you read my post carefully I was simply pointing out that
RI semantics will degrade rapidly (for some tables) once the index
is unusable because a full table scan will be necessary (where at
worst Oracle, say, would read the entire index to get the necessary
information, which for some tables won't be nearly as bad as going
to the actual table).  I was trying to point out that the degradation
may be more spectacular than, say, with Oracle because PG always
needs to go to the table rather than simply use the data in the
index.

And that it is worth documenting.  And that after I do MATCH PARTIAL
semantics for 7.1 I'll try to find time to do some documenting of
RI, including information on "gotchas" such as this one.

(people are likely to be mystified if setting the first column of
a foreign key to NULL makes PG go away for a long time while 
setting it to a non-NULL value doesn't, which is exactly what will
happen for SOME large tables if MATCH PARTIAL and MATCH <unspecified>
is used)



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

Thu, 30 Mar 2000 19:08:56 -0800
Don Baccus <dhogaza@pacifier.com>
Re: slow join on postgresql6.5
> -----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'.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp 

Fri, 31 Mar 2000 19:05:49 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: slow join on postgresql6.5 
sometimes ago...
i posted a question.
"How to store data in postgresql, query data.. and produce accessible data
for gnuplot'in a graph"

This is the first hack:
1) select data into temp-table from table where x.. y.. z..
2) copy temp-table into file.txt using delimiters ' '

This way create some problems...
a) to update temp-table with latest data.. i must drop this table before.

Why don't do directly "copy" on view?
This could be very usefull... than "copy" only on table (or temp-table).. a
"copy" on a view-data.

Let's me know about...

Daniele Medri


Fri, 31 Mar 2000 12:34:39 +0200
"Daniele Medri" <madrid@linux.it>
Re: ...copy hack
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 Service and other goodies at
  http://donb.photo.net.

Fri, 31 Mar 2000 06:33:49 -0800
Don Baccus <dhogaza@pacifier.com>
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 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@tpf.co.jp


Mon, 3 Apr 2000 00:16:28 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: slow join on postgresql6.5 
> > >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.

This is a Vadim item.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 2 Apr 2000 12:36:58 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: slow join on postgresql6.5