pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Corin <wakathane@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: planer chooses very bad plan
Date: Sun, 11 Apr 2010 16:49:49 -0600
Message-ID: <t2ndcc563d11004111549l4210a04fwe2fbb0958aaf931c@mail.gmail.com> (raw)
In-Reply-To: <4BC2500F.5000306@gmail.com>
References: <4BC23B3E.5020500@gmail.com>
	<o2jdcc563d11004111418pca3ad67dyda7e74d5252fd1c1@mail.gmail.com>
	<4BC2500F.5000306@gmail.com>

On Sun, Apr 11, 2010 at 4:41 PM, Corin <wakathane@gmail.com> wrote:
> On 11.04.2010 23:18, Scott Marlowe wrote:
>>
>> In both instances your number of rows estimated is WAY higher than the
>> actual number of rows returned.  Perhaps if you increased
>> default_statistics_target to 100, 200, 500 etc. re-analyzed, and then
>> reun explain analyze again.
>>
>> Also increasing work_mem might encourage the bitmap index scans to occur.
>>
>
> Increasing the statistics >= 500 indeed helped a lot and causes the planner
> to choose a good plan. :)
>
> I'm now thinking about increasing the default_statistics_target of the whole
> server from the default (100) to 1000, because I have many tables with
> similar data. As the size of the table index seems not change at all, I
> wonder how much additional storage is needed? I only care about runtime
> performance: are inserts/updates affected by this change? Or is only analyze
> affected (only run once during the night)?

default stats target has more to do with how many distinct values /
ranges of values you have.  If your data has a nice smooth curve of
distribution smaller values are ok.  Large datasets with very weird
data distributions can throw off the planner.

There's a cost for both analyzing and for query planning.  If 500
fixes this table, and all the other tables are fine at 100 then it
might be worth doing an alter table alter column for just this column.
 However, then you've got to worry about time spent monitoring and
analyzing queries in the database for if / when they need a higher
stats target.

Also, look at increasing effective cache size if the db fits into
memory.  Lowering random page cost helps too.



view thread (8+ messages)  latest in thread

Message-ID: <t2ndcc563d11004111549l4210a04fwe2fbb0958aaf931c@mail.gmail.com>
Permalink:  ../t2ndcc563d11004111549l4210a04fwe2fbb0958aaf931c@mail.gmail.com/
Also on:    postgresql.org/message-id/t2ndcc563d11004111549l4210a04fwe2fbb0958aaf931c@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: scott.marlowe@gmail.com, wakathane@gmail.com
  Subject: Re: planer chooses very bad plan
  In-Reply-To: <t2ndcc563d11004111549l4210a04fwe2fbb0958aaf931c@mail.gmail.com>
  References: <4BC23B3E.5020500@gmail.com> <o2jdcc563d11004111418pca3ad67dyda7e74d5252fd1c1@mail.gmail.com> <4BC2500F.5000306@gmail.com> <t2ndcc563d11004111549l4210a04fwe2fbb0958aaf931c@mail.gmail.com>

* 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