Re: Join over two tables of 50K records takes 2 hours

Thank you guys for spotting the problem immediately.
The reason for such autovacuum thresholds is that these tables are designed
for very high rate of inserts and I have a specific routine to analyze them
in a more controlled way. Infact the stats target of some of the fields is
also high. However that routine was failing to perform analyze on
appqosdata.icmptraffic and its children due to another issue...

Regards,
Svetlin Manavski

On Fri, Oct 14, 2011 at 5:37 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Svetlin Manavski <svetlin(dot)manavski(at)gmail(dot)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
>