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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> wrote:
Svetlin Manavski <svetlin.manavski@xxxxxxxxx> 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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux