Minor TODO list changes

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@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.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@candle.pha.pa.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

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@postgresql.org)

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

In my data warehousing situation, I'd like to be able to specify that
the indexes be as compact as possible (fillfactor = 100%) in order to
hit as few index pages as necessary.

For summary tables there will not be any more inserts or deletions so
the index will not change either. In that case, there's no point to
leaving any extra room for page-splitting.

At some point it would also be nice to be able to mark tables as
read-only and then any indexes created on that table after that would
have a fillfactor of 100%. Then I'd be able to load the table, alter it
to be read-only, then add the appropriate indexes that are automatically
compacted.

Darren

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Thursday, November 04, 2004 12:19 PM
To: Simon Riggs
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Minor TODO list changes

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@candle.pha.pa.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 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

At some point it would also be nice to be able to mark tables as
read-only and then any indexes created on that table after that would
have a fillfactor of 100%. Then I'd be able to load the table, alter it
to be read-only, then add the appropriate indexes that are automatically
compacted.

If it's read-only, you might as well remove a number of the internal
fields used for visibility as well.

On Thu, 2004-11-04 at 17:59, Darren King wrote:

In my data warehousing situation, I'd like to be able to specify that
the indexes be as compact as possible (fillfactor = 100%) in order to
hit as few index pages as necessary.

Yes, that's my intent.

At some point it would also be nice to be able to mark tables as
read-only and then any indexes created on that table after that would
have a fillfactor of 100%. Then I'd be able to load the table, alter it
to be read-only, then add the appropriate indexes that are automatically
compacted.

Sounds cool. Good idea,

--
Best Regards, Simon Riggs

On Thu, 2004-11-04 at 18:15, Rod Taylor wrote:

At some point it would also be nice to be able to mark tables as
read-only and then any indexes created on that table after that would
have a fillfactor of 100%. Then I'd be able to load the table, alter it
to be read-only, then add the appropriate indexes that are automatically
compacted.

If it's read-only, you might as well remove a number of the internal
fields used for visibility as well.

Yes, should be able to save 16 bytes/row for an INSERT only table that
would still allow multiple simultaneous COPY jobs against it, with no
more than 1 statement per transaction. I'd like to create an additional
tuple layout using the tuple version bits, so you'd be able to set a
flag at CREATE TABLE time to use that as an optional alternative from
the standard one. UPDATEs and DELETEs would be permanently disallowed
against such tables, just as if privileges had not been granted.
TRUNCATE would still work, however.

Call it something like NOMODIFY? You could then alter VACUUM to skip
such tables, so you'd be able to do a VACUUM database without scanning
all of the largest tables in your system.

Darren's first idea would then be interpreted as automatically setting
FILLFACTOR=100 on indexes of NOMODIFY tables.

Darren's second idea was dynamic: i.e. an ALTER TABLE READONLY after
loading, rather than using a different tuple layout, which would need to
be done before loading, probably at CREATE TABLE time.

Darren's second idea of READONLY tables is related, but not necessarily
the same as the NOMODIFY concept that Rod brings up.

--
Best Regards, Simon Riggs

You are correct.

I would envision being able to alter a table "read-write" at any point.
If the index(es) on the table are completely filled from being created
in read-only mode, then the affected pages should be split with the
default fillfactor when/if a row is inserted or updated. Altering the
table back to read-only would simple leave the index as is with a few
pages not filled, but still fully functional.

The insert-only index is intriguing as well though.

Darren

-----Original Message-----
From: Simon Riggs [mailto:simon@2ndquadrant.com]
Sent: Thursday, November 04, 2004 1:50 PM
To: Rod Taylor
Cc: Darren King; Bruce Momjian; PostgreSQL Development
Subject: Re: [HACKERS] Minor TODO list changes

On Thu, 2004-11-04 at 18:15, Rod Taylor wrote:

At some point it would also be nice to be able to mark tables as
read-only and then any indexes created on that table after that
would have a fillfactor of 100%. Then I'd be able to load the
table, alter it to be read-only, then add the appropriate indexes
that are automatically compacted.

If it's read-only, you might as well remove a number of the internal
fields used for visibility as well.

Yes, should be able to save 16 bytes/row for an INSERT only table that
would still allow multiple simultaneous COPY jobs against it, with no
more than 1 statement per transaction. I'd like to create an additional
tuple layout using the tuple version bits, so you'd be able to set a
flag at CREATE TABLE time to use that as an optional alternative from
the standard one. UPDATEs and DELETEs would be permanently disallowed
against such tables, just as if privileges had not been granted.
TRUNCATE would still work, however.

Call it something like NOMODIFY? You could then alter VACUUM to skip
such tables, so you'd be able to do a VACUUM database without scanning
all of the largest tables in your system.

Darren's first idea would then be interpreted as automatically setting
FILLFACTOR=100 on indexes of NOMODIFY tables.

Darren's second idea was dynamic: i.e. an ALTER TABLE READONLY after
loading, rather than using a different tuple layout, which would need to
be done before loading, probably at CREATE TABLE time.

Darren's second idea of READONLY tables is related, but not necessarily
the same as the NOMODIFY concept that Rod brings up.

--
Best Regards, Simon Riggs