Re: Performance Optimization for Dummies 2 - the SQL

On 10/15/06, Carlo Stonebanks <stonec.register@sympatico.ca> 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