Re: [PERFORM] Very Poor Insert Performance
On Wednesday 29 October 2003 2:23 pm, Tom Lane wrote: Your initial message stated plainly that the problem was in INSERTs; it's not surprising that you got unhelpful advice. But perhaps my use of the term insert to describe upload was a very bad call given the domain of the list... I assure you I wasn't setting out to deceive anyone! The only location i used INSERT (ie as a Postgres keyword) was towards the end of my mail when I tried to highlight the fact we couldn't use COPY to upload our data because of the difficulty in maintaining the code to generate inter-table relations ahead of time. The problem was showing itself during database upload - so I assumed (ASS out of U and ME and all that!) that the write delay was very large (hence the disappointing improvements by switching off fsync etc). It was only after further investigation that we discovered that simulated INSERTs were going fine, but the Read delays between INSERTs where holding us up. Your LEFT JOINs are constraining the join order --- see http://www.postgresql.org/docs/7.3/static/explicit-joins.html You'll need to reorder the joins into something that does what you want. Thanks very much for the heads-up, we'll reorder the joins into something more effecient! Damien ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Very Poor Insert Performance
GS == Greg Stark [EMAIL PROTECTED] writes: GS At a guess the foreign key relationships you're enforcing don't GS have indexes to help them. If they do perhaps postgres isn't using GS them. Or, if you do have indexes, they've bloated to be way too big and are overwhelming your shared buffers. Reindex them and see it it helps. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Very Poor Insert Performance
Damien Dougan [EMAIL PROTECTED] writes: Our batch upload is performing a number of stored procedures to insert data on the database. Initially, this results in quite good performance, but rapidly spirals down to approximately 1 per second after some minutes. It's fairly unlikely anyone will be able to help without you saying what you're doing. What are these procedures doing? What do the tables look like? What indexes exist? At a guess the foreign key relationships you're enforcing don't have indexes to help them. If they do perhaps postgres isn't using them. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Very Poor Insert Performance
Damien Dougan [EMAIL PROTECTED] writes: Has anyone any ideas as to what could be causing the spiraling performance? You really haven't provided any information that would allow anything but guesses, but I'll guess anyway: poor plans for foreign key checks? See nearby threads. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
