From: Merlin Moncure <mmoncure@gmail.com>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance Optimization for Dummies 2 - the SQL
Date: Mon, 16 Oct 2006 09:38:33 -0400
Message-ID: <b42b73150610160638o60a537dbm3eb5dfc4ddd60cf0@mail.gmail.com> (raw)
In-Reply-To: <egua6n$1fv4$1@news.hub.org>
References: <eft782$5a1$1@news.hub.org>
<b42b73150610042326v7fdeed17rf6b96e52a681a6db@mail.gmail.com>
<eg2cds$29en$1@news.hub.org>
<b42b73150610050630t76e9eedeh45a382729d35f2f6@mail.gmail.com>
<eg611q$l0m$1@news.hub.org>
<1160153623.6181.13.camel@state.g2switchworks.com>
<b42b73150610061153i3d11d7a4r23e1d7c5317feb9f@mail.gmail.com>
<eg6f8l$2det$1@news.hub.org>
<b42b73150610090649y74c4dc99g176366921f784e7b@mail.gmail.com>
<egua6n$1fv4$1@news.hub.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mmoncure@gmail.com, stonec.register@sympatico.ca
Subject: Re: Performance Optimization for Dummies 2 - the SQL
In-Reply-To: <b42b73150610160638o60a537dbm3eb5dfc4ddd60cf0@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox