Skip site navigation (1) Skip section navigation (2)

Re: Query optimization help

From: Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query optimization help
Date: 2011-08-30 06:09:42
Message-ID: CAM6mieJaFU8+g35TLNRmd+kOieoY7Gk06x1MvnyaTy0EOvJxEQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

On 30 August 2011 15:36, Szymon Kosok <szymon(at)mwg(dot)pl> wrote:
> Hello,
>
> I asked that question on StackOverflow, but didn't get any valuable
> response, so I'll ask it here. :)
>
> I have such query:

Could you please re-post your explain using this web site:
http://explain.depesz.com/ and post links to Stackoverflow question?
What is your Postgres version? Database settings?
I see huge discrepancy between predicted and actual row numbers (like
1264420 vs 485). I would try the following:

- check column statistics (pg_stasts) and focus on the following
columns: n_distinct, null_frac, most_common_vals. If they are way-off
from the actual values then you should tweak (auto)analyze process:
run manual/auto analyse more often (check pg_stat_user_tables),
increase default_statistics_target (per column or global)

- try to disable nested loop join (set enable_nestloop=off)

-- 
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2011-08-30 06:39:43
Subject: Re: Re: How to track number of connections and hosts to Postgres cluster
Previous:From: Venkat BalajiDate: 2011-08-30 05:55:47
Subject: Re: Re: How to track number of connections and hosts to Postgres cluster

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group