Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-16 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] writes: Yep, patch attached. I also changed xactGetCommittedChildren to return the original array instead of copying it, as Alvaro suggested. Applied with minor corrections (mostly comment fixes, but there were a couple of real mistakes).

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-16 Thread Bruce Momjian
This has been applied by Tom. --- Heikki Linnakangas wrote: Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Elsewhere in our codebase where we use arrays that are enlarged as needed, we keep track of

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-12 Thread Heikki Linnakangas
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Elsewhere in our codebase where we use arrays that are enlarged as needed, we keep track of the allocated size and the used size of the array separately, and only call repalloc when the array fills up, and repalloc a larger than

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-12 Thread Pavan Deolasee
On Wed, Mar 12, 2008 at 7:13 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Yep, patch attached. I also changed xactGetCommittedChildren to return the original array instead of copying it, as Alvaro suggested. Any comments on the flag based approach I suggested earlier ? Am I missing some

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-12 Thread Tom Lane
Pavan Deolasee [EMAIL PROTECTED] writes: On Wed, Mar 12, 2008 at 7:13 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Yep, patch attached. I also changed xactGetCommittedChildren to return the original array instead of copying it, as Alvaro suggested. Any comments on the flag based approach

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-12 Thread Pavan Deolasee
On Wed, Mar 12, 2008 at 9:27 PM, Tom Lane [EMAIL PROTECTED] wrote: I didn't like it; it seemed overly complicated (consider dealing with XID wraparound), We are talking about subtransactions here. I don't think we support subtransaction wrap-around, do we ? and it would have problems with

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-12 Thread Tom Lane
Pavan Deolasee [EMAIL PROTECTED] writes: On Wed, Mar 12, 2008 at 9:27 PM, Tom Lane [EMAIL PROTECTED] wrote: and it would have problems with a slow transaction generating a sparse set of subtransaction XIDs. I agree thats the worst case. But is that common ? Thats what I was thinking when I

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-12 Thread Heikki Linnakangas
Pavan Deolasee wrote: Wait. Subtransaction ids are local to a transaction and always start from 1. See this: /* * reinitialize within-transaction counters */ s-subTransactionId = TopSubTransactionId; currentSubTransactionId = TopSubTransactionId; No, we're not talking

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-12 Thread Heikki Linnakangas
Pavan Deolasee wrote: On Wed, Mar 12, 2008 at 9:27 PM, Tom Lane [EMAIL PROTECTED] wrote: I didn't like it; it seemed overly complicated (consider dealing with XID wraparound), We are talking about subtransactions here. I don't think we support subtransaction wrap-around, do we ? Imagine

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-12 Thread Pavan Deolasee
On Wed, Mar 12, 2008 at 11:03 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Subtransactions are assigned regular XIDs as well, just like top-level transactions. Ah, got it now. I never noticed this before. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com --

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-12 Thread Pavan Deolasee
On Wed, Mar 12, 2008 at 10:44 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Imagine that you start a transaction just before transaction wrap-around, so that the top level XID is 2^31-10. Then you start 20 subtransactions. What XIDs will they get? Now how would you map those to a

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-11 Thread Heikki Linnakangas
(moved to pgsql-patches, as there's a patch attached) Tom Lane wrote: Getting rid of the linked-list representation would be a win in a couple of ways --- we'd not need the bogus list of XIDs support in pg_list.h, and xactGetCommittedChildren would go away. OTOH AtSubCommit_childXids would get

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-11 Thread Pavan Deolasee
On Tue, Mar 11, 2008 at 6:04 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: (moved to pgsql-patches, as there's a patch attached) I couldn't let this case go, so I wrote a patch. I replaced the linked list with an array that's enlarged at AtSubCommit_childXids when necessary. We can

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-11 Thread Alvaro Herrera
Heikki Linnakangas wrote: I couldn't let this case go, so I wrote a patch. I replaced the linked list with an array that's enlarged at AtSubCommit_childXids when necessary. Do you still need to palloc the return value from xactGetCommittedChildren? Perhaps you can save the

Re: [PATCHES] [PERFORM] Very slow (2 tuples/second) sequential scan after bulk insert; speed returns to ~500 tuples/second after commit

2008-03-11 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] writes: I initially thought that using a single palloc'd array to hold all the XIDs would introduce a new limit on the number committed subtransactions, thanks to MaxAllocSize, but that's not the case. Without patch, we actually allocate an array like