From: Scott Marlowe <scott.marlowe@gmail.com>
To: Steve McLellan <smclellan@mintel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query performance over a large proportion of data
Date: Tue, 10 Mar 2009 17:19:53 -0600
Message-ID: <dcc563d10903101619j4c668570m1e3fdc9a539f7acd@mail.gmail.com> (raw)
In-Reply-To: <1ca6422a7addad0207f758f54300dd0b.squirrel@semichrome.net>
References: <1ca6422a7addad0207f758f54300dd0b.squirrel@semichrome.net>
On Tue, Mar 10, 2009 at 3:12 PM, Steve McLellan <smclellan@mintel.com> wrote:
>
> Nested Loop (cost=466.34..192962.24 rows=15329 width=12) (actual
> time=13653.238..31332.113 rows=131466 loops=1)
Both your query plans end with this nested loop join which is taking
up about half your time in your query. Notice the estimation of the
result set is off by a factor of about 10 here, which means a nested
loop might be not so good a choice for this. Try increasing default
stats target and re-analyzing to see if that helps. 1000 is the max
you can give that a shot right off to see if it helps. If it does,
drop it until the numbers start to go off again and stop.
For a quicker test, you can set enable_nestloop = off in the psql
command line and then run the query by hand and see if that helps.
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: scott.marlowe@gmail.com, smclellan@mintel.com
Subject: Re: Query performance over a large proportion of data
In-Reply-To: <dcc563d10903101619j4c668570m1e3fdc9a539f7acd@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