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

Re: experiments in query optimization

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Faheem Mitha" <faheem(at)email(dot)unc(dot)edu>, "Robert Haas" <robertmhaas(at)gmail(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: experiments in query optimization
Date: 2010-03-30 16:08:10
Message-ID: 4BB1DB9A020000250003020E@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
Faheem Mitha <faheem(at)email(dot)unc(dot)edu> wrote:
 
>> If you're concerned about memory usage, try reducing work_mem;
>> you've probably got it set to something huge.
> 
> work_mem = 1 GB (see diag.{tex/pdf}).
> 
> The point isn't that I'm using so much memory. Again, my question
> is, why are these changes affecting memory usage so drastically?
 
Because the planner looks at a very wide variety of plans, some of
which may use many allocations of work_mem size, and some of which
don't.  The costs are compared and the lowest cost one is chosen. If
you are close to the "tipping point" then even a very small change
might affect which is chosen.  It pays to keep the work_mem setting
sane so that unexpected plan changes don't cause problems.
 
Look at the plans and their costs to get a feel for what's being
chosen and why.  Although it's a very bad idea to use these in
production, you can often shift the plan to something you *think*
would be better using the enable_* settings, to see what the planner
thinks such a plan will cost and where it thinks the cost would be;
that can help in tuning the settings.
 
>> You might need to create some indices, too.
> 
> Ok. To what purpose? This query picks up everything from the
> tables and the planner does table scans, so conventional wisdom
> and indeed my experience, says that indexes are not going to be so
> useful.
 
There are situations where scanning the entire table to build up a
hash table is more expensive than using an index.  Why not test it?
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: Faheem MithaDate: 2010-03-30 16:30:14
Subject: Re: experiments in query optimization
Previous:From: Kevin GrittnerDate: 2010-03-30 15:05:23
Subject: Re: REINDEXing database-wide daily

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