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

Re: Poor performance o

From: "Craig A(dot) James" <cjames(at)modgraph-usa(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Poor performance o
Date: 2006-03-22 01:04:16
Message-ID: 4420A290.2050908@modgraph-usa.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
Tom Lane wrote:
> "Craig A. James" <cjames(at)modgraph-usa(dot)com> writes:
>> It looks to me like the problem is the use of nested loops when a hash
>> join should be used, but I'm no expert at query planning.
> 
> Given the sizes of the tables involved, you'd likely have to boost up
> work_mem before the planner would consider a hash join.  What nondefault
> configuration settings do you have, anyway?

shared_buffers = 20000
work_mem = 32768
effective_cache_size = 300000

This is on a 4GB machine.  Is there a guideline for work_mem that's related to table size?  Something like, "allow 2 MB per million rows"?

I'm also curious why the big difference between my "Query #1" and "Query #2".  Even though it does a nested loop, #2's outer loop only returns one result from a very tiny table, so shouldn't it be virtually indistinguishable from #1?

Thanks,
Craig

In response to

Responses

pgsql-performance by date

Next:From: Vivek KheraDate: 2006-03-22 02:22:52
Subject: Re: Best OS & Configuration for Dual Xeon w/4GB &
Previous:From: Tom LaneDate: 2006-03-21 23:33:15
Subject: Re: Poor performance o

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2006-03-22 02:04:00
Subject: Re: [GENERAL] A real currency type
Previous:From: Jonah H. HarrisDate: 2006-03-22 00:14:05
Subject: Re: 8.2 planning features

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