On 10/15/06, Carlo Stonebanks <stonec.register@xxxxxxxxxxxx> wrote:
Hi Merlin, Well, I'm back. first of all, thanks for your dogged determination to help me out - it is much appreciated. I owe you a beer or twelve. The import has been running for a week. The import program got faster as I tuned things. I capture the dynamic SQL statements generated by the app, as well as an accompanying EXPLAIN - and put it out to an XML file. I turned off seq scan in the config, and ran a trial import. I knew that with seq scan off that if I saw a seq scan in my log, it's because there were no indexes available to satisfy the query - I adjusted accordingly and this worked really well. When the import runs against an empty or small db, it's blisteringly fast (considering that it's a heauristically based process). This proved that it wasn't the app or the SQL connection that was slow. Once again, though, as the data db grows, it slows down. Now it's crawling again. All of the queries appear to be fine, taking advantage of the indexes. There is ONE query, though, that seems to be the troublemaker - the same one I had brought up before. I believe that it is one sub-query that is causing the problem, taking what appears to be 500 to 1000+ms to run every time. (See below). Curiously, it's using index scans, and it really looks like a simple query to me. I am completely baffled. The two tables in question have about 800K rows each - not exactly an incredible number. The EXPLAIN is simple, but the performance is dreadful. All the other queries run much faster than this - does ANYTHING about this query strike you as odd?
Can you try temporarily disabling bitmap scans and see what comes up? merlin
