From: Tom Lane <tgl@sss.pgh.pa.us>
To: Svetlin Manavski <svetlin.manavski@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Join over two tables of 50K records takes 2 hours
Date: Fri, 14 Oct 2011 00:37:48 -0400
Message-ID: <13425.1318567068@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMm+ggTh3DRKpWyeNfRi5nUSo3UaActDKqb=7JHW-NSgcOUrfg@mail.gmail.com>
References: <CAMm+ggTh3DRKpWyeNfRi5nUSo3UaActDKqb=7JHW-NSgcOUrfg@mail.gmail.com>
Svetlin Manavski <svetlin.manavski@gmail.com> writes:
> I am running 9.03 with the settings listed below. I have a prohibitively
> slow query in an application which has an overall good performance:
It's slow because the planner is choosing a nestloop join on the
strength of its estimate that there's only a half dozen rows to be
joined. You need to figure out why those rowcount estimates are so bad.
I suspect that you've shot yourself in the foot by raising
autovacuum_analyze_threshold so high --- most likely, none of those
tables have ever gotten analyzed. And what's with the high
autovacuum_naptime setting? You might need to increase
default_statistics_target too, but first see if a manual ANALYZE makes
things better.
regards, tom lane
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: tgl@sss.pgh.pa.us, svetlin.manavski@gmail.com
Subject: Re: Join over two tables of 50K records takes 2 hours
In-Reply-To: <13425.1318567068@sss.pgh.pa.us>
* 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