Re: Minor TODO list changes

A few minor typos/notes:

INDEXES

1. On 2nd bullet...
"The main difficulty with this item is the problem of creating an index
that can spam more than one table."

should be span, not spam

2. On 6th bullet
* "Use index to restrict rows returned by multi-key index when used
with non-consecutive keys to reduce heap accesses

For an index on col1,col2,col3, and a WHERE clause of col1 = 5 and col3
= 9, spin though the index checking for col1 and col3 matches, rather
than just col1 "

This is also known as "skip-scanning", so it would be good to use that
phrase in the TODO

3. Whatever happened to the FILLFACTOR todo item? AFAICS it disappeared
sometime at the beginning of August, but I'm not sure why?

REF INTEGRITY

...Didn't we just get rid of deferred triggers?? Perhaps I read that
wrong.

CACHE

1. 1st bullet....prefix it with "Use posix_fadvise() API to let OS know
how files will be used by PostgreSQL. This would allow, for example,
a..." (replacing the word "add").

--
Best Regards, Simon Riggs

On Thu, 2004-11-04 at 09:31, Simon Riggs wrote:
> A few minor typos/notes:
>
> INDEXES
>
> 1. On 2nd bullet...
> "The main difficulty with this item is the problem of creating an index
> that can spam more than one table."
>
> should be span, not spam
>
> 2. On 6th bullet
> * "Use index to restrict rows returned by multi-key index when used
> with non-consecutive keys to reduce heap accesses
>
> For an index on col1,col2,col3, and a WHERE clause of col1 = 5 and col3
> = 9, spin though the index checking for col1 and col3 matches, rather
> than just col1 "
>
> This is also known as "skip-scanning", so it would be good to use that
> phrase in the TODO
>
> 3. Whatever happened to the FILLFACTOR todo item? AFAICS it disappeared
> sometime at the beginning of August, but I'm not sure why?

4. Multiple column index statistics

Allow accurate statistics to be collected on indexes that have more than
one column, so that they are more frequently selected for use.

(following on from Manfred Koizar's exploratory patch to provide
this...)

>
> REF INTEGRITY
>
> ...Didn't we just get rid of deferred triggers?? Perhaps I read that
> wrong.
>
>
> CACHE
>
> 1. 1st bullet....prefix it with "Use posix_fadvise() API to let OS know
> how files will be used by PostgreSQL. This would allow, for example,
> a..." (replacing the word "add").
--
Best Regards, Simon Riggs

On Thu, 4 Nov 2004, Simon Riggs wrote:

> REF INTEGRITY
>
> ...Didn't we just get rid of deferred triggers?? Perhaps I read that
> wrong.

We got rid of deferred referential actions. Constraint check triggers
for referential integrity (insert/update to fk table, NO ACTION on pk
table) are still deferrable.

OK, I updated all your items. I removed fillfactor because I thought I
was the only one who thought it was valuable and as I remember it was
mostly useful for ISAM, which we don't support. Can you think of a use
for a non-100% fillfactor?

---------------------------------------------------------------------------

Simon Riggs wrote:
> A few minor typos/notes:
>
> INDEXES
>
> 1. On 2nd bullet...
> "The main difficulty with this item is the problem of creating an index
> that can spam more than one table."
>
> should be span, not spam
>
> 2. On 6th bullet
> * "Use index to restrict rows returned by multi-key index when used
> with non-consecutive keys to reduce heap accesses
>
> For an index on col1,col2,col3, and a WHERE clause of col1 = 5 and col3
> = 9, spin though the index checking for col1 and col3 matches, rather
> than just col1 "
>
> This is also known as "skip-scanning", so it would be good to use that
> phrase in the TODO
>
> 3. Whatever happened to the FILLFACTOR todo item? AFAICS it disappeared
> sometime at the beginning of August, but I'm not sure why?
>
> REF INTEGRITY
>
> ...Didn't we just get rid of deferred triggers?? Perhaps I read that
> wrong.
>
>
> CACHE
>
> 1. 1st bullet....prefix it with "Use posix_fadvise() API to let OS know
> how files will be used by PostgreSQL. This would allow, for example,
> a..." (replacing the word "add").
>
>
>
> --
> Best Regards, Simon Riggs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Simon Riggs wrote:
>
> 4. Multiple column index statistics
>
> Allow accurate statistics to be collected on indexes that have more than
> one column, so that they are more frequently selected for use.
>
> (following on from Manfred Koizar's exploratory patch to provide
> this...)

Added.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce,

Just to chime in. I also agree that fillfactor is useful. I have
been investigating different index variants and different fill
factors can greatly influence the performance of the index. I
also think it may play a key role in minimizing the small table/
many inserts/updates performance problem.

--Ken

On Thu, Nov 04, 2004 at 11:51:15AM -0500, Bruce Momjian wrote:
>
> OK, I updated all your items. I removed fillfactor because I thought I
> was the only one who thought it was valuable and as I remember it was
> mostly useful for ISAM, which we don't support. Can you think of a use
> for a non-100% fillfactor?
>
> ---------------------------------------------------------------------------
>
> Simon Riggs wrote:
> > A few minor typos/notes:
> >
> > INDEXES
> >
> > 1. On 2nd bullet...
> > "The main difficulty with this item is the problem of creating an index
> > that can spam more than one table."
> >
> > should be span, not spam
> >
> > 2. On 6th bullet
> > * "Use index to restrict rows returned by multi-key index when used
> > with non-consecutive keys to reduce heap accesses
> >
> > For an index on col1,col2,col3, and a WHERE clause of col1 = 5 and col3
> > = 9, spin though the index checking for col1 and col3 matches, rather
> > than just col1 "
> >
> > This is also known as "skip-scanning", so it would be good to use that
> > phrase in the TODO
> >
> > 3. Whatever happened to the FILLFACTOR todo item? AFAICS it disappeared
> > sometime at the beginning of August, but I'm not sure why?
> >
> > REF INTEGRITY
> >
> > ...Didn't we just get rid of deferred triggers?? Perhaps I read that
> > wrong.
> >
> >
> > CACHE
> >
> > 1. 1st bullet....prefix it with "Use posix_fadvise() API to let OS know
> > how files will be used by PostgreSQL. This would allow, for example,
> > a..." (replacing the word "add").
> >
> >
> >
> > --
> > Best Regards, Simon Riggs
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Added:

* Add fillfactor to control reserved free space during index
creation

---------------------------------------------------------------------------

Kenneth Marshall wrote:
> Bruce,
>
> Just to chime in. I also agree that fillfactor is useful. I have
> been investigating different index variants and different fill
> factors can greatly influence the performance of the index. I
> also think it may play a key role in minimizing the small table/
> many inserts/updates performance problem.
>
> --Ken
>
> On Thu, Nov 04, 2004 at 11:51:15AM -0500, Bruce Momjian wrote:
> >
> > OK, I updated all your items. I removed fillfactor because I thought I
> > was the only one who thought it was valuable and as I remember it was
> > mostly useful for ISAM, which we don't support. Can you think of a use
> > for a non-100% fillfactor?
> >
> > ---------------------------------------------------------------------------
> >
> > Simon Riggs wrote:
> > > A few minor typos/notes:
> > >
> > > INDEXES
> > >
> > > 1. On 2nd bullet...
> > > "The main difficulty with this item is the problem of creating an index
> > > that can spam more than one table."
> > >
> > > should be span, not spam
> > >
> > > 2. On 6th bullet
> > > * "Use index to restrict rows returned by multi-key index when used
> > > with non-consecutive keys to reduce heap accesses
> > >
> > > For an index on col1,col2,col3, and a WHERE clause of col1 = 5 and col3
> > > = 9, spin though the index checking for col1 and col3 matches, rather
> > > than just col1 "
> > >
> > > This is also known as "skip-scanning", so it would be good to use that
> > > phrase in the TODO
> > >
> > > 3. Whatever happened to the FILLFACTOR todo item? AFAICS it disappeared
> > > sometime at the beginning of August, but I'm not sure why?
> > >
> > > REF INTEGRITY
> > >
> > > ...Didn't we just get rid of deferred triggers?? Perhaps I read that
> > > wrong.
> > >
> > >
> > > CACHE
> > >
> > > 1. 1st bullet....prefix it with "Use posix_fadvise() API to let OS know
> > > how files will be used by PostgreSQL. This would allow, for example,
> > > a..." (replacing the word "add").
> > >
> > >
> > >
> > > --
> > > Best Regards, Simon Riggs
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> > >
> >
> > --
> > Bruce Momjian | http://candle.pha.pa.us
> > pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> > + If your life is a hard drive, | 13 Roberts Road
> > + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thu, 2004-11-04 at 16:51, Bruce Momjian wrote:
> OK, I updated all your items.

Thanks

> I removed fillfactor because I thought I
> was the only one who thought it was valuable and as I remember it was
> mostly useful for ISAM, which we don't support. Can you think of a use
> for a non-100% fillfactor?
>

I was under the impression the factor was 67% for data loaded on the
leading-edge of an index, and 50% for other INSERTs.
(backend/access/nbtree/nbtinsert.c)

Not sure, without checking, what CREATE INDEX and COPY do, but I'm
guessing it is similar?

Other RDBMS use a higher leading-edge/standard fill factor.

There are situations where I'd want to set it at 90%, or even 100%. If I
know the update rate is likely to be zero, then I'd like my indexes to
fit in 10-30% less memory and disk, please.

Or am I missing something?

--
Best Regards, Simon Riggs

Simon Riggs wrote:
> On Thu, 2004-11-04 at 16:51, Bruce Momjian wrote:
> > OK, I updated all your items.
>
> Thanks
>
> > I removed fillfactor because I thought I
> > was the only one who thought it was valuable and as I remember it was
> > mostly useful for ISAM, which we don't support. Can you think of a use
> > for a non-100% fillfactor?
> >
>
> I was under the impression the factor was 67% for data loaded on the
> leading-edge of an index, and 50% for other INSERTs.
> (backend/access/nbtree/nbtinsert.c)
>
> Not sure, without checking, what CREATE INDEX and COPY do, but I'm
> guessing it is similar?
>
> Other RDBMS use a higher leading-edge/standard fill factor.
>
> There are situations where I'd want to set it at 90%, or even 100%. If I
> know the update rate is likely to be zero, then I'd like my indexes to
> fit in 10-30% less memory and disk, please.
>
> Or am I missing something?

Oh, good point. I was thinking of just the leaf pages which I think are
100% filled.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073