Re: Interpreting query plan

On Fri, 2 Jul 2004, Chris Smith wrote:

> I've just noticed in the regular profiling information from our web
> application that a particular query on a fairly small database is taking about
> 15 seconds. The query is generated from software on the fly, hence its
> quirkiness -- if any of that is the problem, then I'll go ahead and fix it,
> but I can't imagine a few repeated WHERE conditions fooling the query
> optimizer.
>
> Anyway, I don't know how to interpret query plans. Can anyone give me a hand?
> To get the plan, I just plugged in various values -- The actual query is run
> with various different values, and even a few different lengths for the IN
> clause.
>
> miqweb=> explain select distinct t0.* from UserAccount t0, UserMapping t1
> where
> (t0.companyid = 123) and ((t0.companyid = 123) and (t0.userid = t1.userid)
> and
> (t1.groupid in (123, 234, 345, 456))) and (t0.companyid = 123);

Plain explain output is useful for finding what the plan is, but not as
useful for determining why a query takes a particular amount of time.
You might want to use "explain analyze" and send that result (which gives
the real time and real number of rows for different steps).