This idea has been rejected to do poor performance results reported
later in the thread.
---
Heikki Linnakangas wrote:
> While thinking about index-organized-tables and similar ideas, it
> occurred to me that there's some l
On Tue, 10 Jul 2007, Tom Lane wrote:
It's just desktop-grade junk :-(. Feel free to repeat the test on
something more serious.
Right, but even such junk can be setup such that the disks honor commits,
just wanted to confirm you didn't go out of your way to do that--sounds
like you didn't.
Greg Smith <[EMAIL PROTECTED]> writes:
> On Mon, 9 Jul 2007, Tom Lane wrote:
>> The hardware is just a Dell x86_64 workstation with crappy IDE disk, so
>> maybe things would look better elsewhere, but it's all I have to work
>> with.
> Do you have write-caching turned off on the drive so INSERTs
Gregory Stark <[EMAIL PROTECTED]> writes:
> It's pretty serious what you're suggesting since it means that we'll
> basically never have a real cluster feature.
It seems entirely likely that this is not the way to go about "real
clustering".
regards, tom lane
-
On Mon, 9 Jul 2007, Tom Lane wrote:
The hardware is just a Dell x86_64 workstation with crappy IDE disk, so
maybe things would look better elsewhere, but it's all I have to work
with.
Do you have write-caching turned off on the drive so INSERTs are being
rate-limited by WAL syncs? Trying to
Gregory Stark wrote:
It's pretty serious what you're suggesting since it means that we'll basically
never have a real cluster feature. I would sure hope we're missing something
and there's a way to make this work usefully.
Another approach would be an online CLUSTER command. That means there'll
"Tom Lane" <[EMAIL PROTECTED]> writes:
> * delete a random 2% of the table
> * vacuum to recover space
> * insert a random 2% of the table
> * select (about) 1000 consecutively-numbered rows
> * select all the rows (this is just a cross check that the
> numbe
Tom Lane wrote:
What you would hope to see as the benefit of the patch is that the time
for the range SELECT degrades more slowly as more of the table is
replaced. Ignoring the first SELECT as being a startup transient, it
looks like HEAD degrades from about 3 msec to 6 msec over 10 iterations
(
[ back to the cluster-order patch ]
Awhile back, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
> The performance characteristics of this patch hasn't been thoroughly
> discussed yet. The reason why you want to cluster your tables is to
> speed up SELECTs that return a bunch of tuples with simila
[ back to this thread... ]
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> The other question is why is execMain involved in this? That makes the
>> design nonfunctional for tuples inserted in any other way than through
>> the main executor --- COPY for instance. Also, if thi
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> The reason for switching to the new API instead of the amsuggestblock API is
> CPU overhead. It avoids constructing the IndexTuple twice and descending the
> tree twice.
I wonder if it's possible to finesse this. Have the suggestblock function
re
Tom Lane wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
The implementation has changed a bit since August. I thought I had
submitted an updated version in the winter but couldn't find it. Anyway,
I updated and dusted off the source tree, tidied up the comments a
little bit, and fixed some
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> The implementation has changed a bit since August. I thought I had
> submitted an updated version in the winter but couldn't find it. Anyway,
> I updated and dusted off the source tree, tidied up the comments a
> little bit, and fixed some inconsiste
Jaime Casanova wrote:
On 5/27/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
On Mon, May 21, 2007 at 10:48:59AM +0100, Heikki Linnakangas wrote:
>
> IOW it's working as designed. But maybe it's not the desired behavior.
> Should we have a special case and always respect the fillfactor when
> insert
On 6/16/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Jaime Casanova" <[EMAIL PROTECTED]> writes:
> another question: if the fillfactor is 100% then is a complete waste
> of time to look for a suggested block. maybe we could check for that?
No, it isn't, since the page might have been vacuumed since
"Jaime Casanova" <[EMAIL PROTECTED]> writes:
> another question: if the fillfactor is 100% then is a complete waste
> of time to look for a suggested block. maybe we could check for that?
No, it isn't, since the page might have been vacuumed since it was last
filled up.
re
On 5/27/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
On Mon, May 21, 2007 at 10:48:59AM +0100, Heikki Linnakangas wrote:
>
> IOW it's working as designed. But maybe it's not the desired behavior.
> Should we have a special case and always respect the fillfactor when
> inserting to the last page of
On Mon, May 21, 2007 at 10:48:59AM +0100, Heikki Linnakangas wrote:
> >Another problem that I noticed with the patch is that it disregards
> >the fillfactor while inserting the tuples. ISTM that this is a correct
> >behavior when a tuple is being inserted in the block to preserve cluster
> >orderin
Pavan Deolasee wrote:
On 5/21/07, Pavan Deolasee <[EMAIL PROTECTED]> wrote:
On 5/19/07, Heikki Linnakangas <[EMAIL PROTECTED] > wrote:
>
>
> Ah, sorry about that. For some reason my source tree was checked out
> from the 8.2 branch, instead of CVS HEAD.
>
>
I looked at the patch. Not that I a
On 5/21/07, Pavan Deolasee <[EMAIL PROTECTED]> wrote:
On 5/19/07, Heikki Linnakangas <[EMAIL PROTECTED] > wrote:
>
>
> Ah, sorry about that. For some reason my source tree was checked out
> from the 8.2 branch, instead of CVS HEAD.
>
>
I looked at the patch. Not that I am very comfortable with
On 5/19/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
Ah, sorry about that. For some reason my source tree was checked out
from the 8.2 branch, instead of CVS HEAD.
I looked at the patch. Not that I am very comfortable with this part
of the code, but nevertheless here are my comments:
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---
He
Jaime Casanova wrote:
On 5/18/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
Jaime Casanova wrote:
>
> the patch doesn't apply in cvs... you'll need to update it...
Oh, here you are.
The implementation has changed a bit since August. I thought I had
submitted an updated version in the winte
On 5/18/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
Jaime Casanova wrote:
>
> the patch doesn't apply in cvs... you'll need to update it...
Oh, here you are.
The implementation has changed a bit since August. I thought I had
submitted an updated version in the winter but couldn't find it.
Tom Lane wrote:
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
The beef of the patch is two new optional indexam API functions:
amprepareinsert and amfinishinsert. amprepareinsert is called before
inserting the heap tuple. It descends the tree and finds and pins the
right leaf page to insert t
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> The beef of the patch is two new optional indexam API functions:
> amprepareinsert and amfinishinsert. amprepareinsert is called before
> inserting the heap tuple. It descends the tree and finds and pins the
> right leaf page to insert to, and ret
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---
He
Jaime Casanova wrote:
On 5/16/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
Jim C. Nasby wrote:
> What about adding the ability to ask the FSM for a page that's near a
> given page? That way if you did have to go to the FSM you could at
least
> try and insert close to the page you original
On 5/17/07, Jaime Casanova <[EMAIL PROTECTED]> wrote:
On 5/16/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
> Jim C. Nasby wrote:
> > What about adding the ability to ask the FSM for a page that's near a
> > given page? That way if you did have to go to the FSM you could at least
> > try and
On 5/16/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote:
Jim C. Nasby wrote:
> What about adding the ability to ask the FSM for a page that's near a
> given page? That way if you did have to go to the FSM you could at least
> try and insert close to the page you originally wanted.
Yeah, there's
Jim C. Nasby wrote:
What about adding the ability to ask the FSM for a page that's near a
given page? That way if you did have to go to the FSM you could at least
try and insert close to the page you originally wanted.
Yeah, there's always room for improvement. I made the patch when I was
work
What about adding the ability to ask the FSM for a page that's near a
given page? That way if you did have to go to the FSM you could at least
try and insert close to the page you originally wanted.
On Tue, May 15, 2007 at 11:26:51PM +0100, Heikki Linnakangas wrote:
> Ah, thanks! I had forgotten a
Ah, thanks! I had forgotten about it as well.
Bruce Momjian wrote:
[ Sorry I found this one only found recently.]
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL
[ Sorry I found this one only found recently.]
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
Gene <[EMAIL PROTECTED]> writes:
> "Your best bet might be to partition the table into two subtables, one
> with "stable" data and one with the fresh data, and transfer rows from
> one to the other once they get stable. Storage density in the "fresh"
> part would be poor, but it should be small
Jonah H. Harris wrote:
On 8/9/06, Tom Lane <[EMAIL PROTECTED]> wrote:
UPDATE tries to place the new tuple on the same page it's already
on.
I think he meant for INSERT.
Right. Update is indeed taken care of already.
One example where this would help would be a customer_history table that
You are correct the main part I'm worried about is the updates, being so far from the originals. fyi I
am partitioning the tables by the timestamp column,vacuum analyzing once per hour, creating one child
partition per day in a cron job. Because I'm using hibernate for database abstraction (statele
Gene <[EMAIL PROTECTED]> writes:
> I have a table that inserts lots of rows (million+ per day) int8 as primary
> key, and I cluster by a timestamp which is approximately the timestamp of
> the insert beforehand and is therefore in increasing order and doesn't
> change. Most of the rows are updated
I have a table that inserts lots of rows (million+ per day) int8 as primary key, and I cluster by a timestamp which is approximately the timestamp of the insert beforehand and is therefore in increasing order and doesn't change. Most of the rows are updated about 3 times over time roughly within th
On 8/9/06, Tom Lane <[EMAIL PROTECTED]> wrote:
UPDATE tries to place the new tuple on the same page it's already
on.
I think he meant for INSERT.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation| fax: 732.331.1301
33 Wood Ave S, 2nd Floor
Heikki Linnakangas <[EMAIL PROTECTED]> writes:
> While thinking about index-organized-tables and similar ideas, it
> occurred to me that there's some low-hanging-fruit: maintaining cluster
> order on inserts by trying to place new heap tuples close to other
> similar tuples.
Doesn't this happen
While thinking about index-organized-tables and similar ideas, it
occurred to me that there's some low-hanging-fruit: maintaining cluster
order on inserts by trying to place new heap tuples close to other
similar tuples. That involves asking the index am where on the heap the
new tuple should g
42 matches
Mail list logo
