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

Re: Sub-optimal plan chosen

From: bricklen <bricklen(at)gmail(dot)com>
To: tv(at)fuzzy(dot)cz
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Sub-optimal plan chosen
Date: 2009-09-10 15:25:34
Message-ID: 33b743250909100825p6a8a4e5ay19a846f303a5b7a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Tomas,

2009/9/10 <tv(at)fuzzy(dot)cz>

> > default_statistics_target = 100 (tried with 500, no change). Vacuum
> > analyzed
> > before initial query, and after each change to default_statistics_target.
>
> Modifying the statistics target is useful only if the estimates are
> seriously off, which is not your case - so it won't help, at least not
> reliably.
>
> > The same query, with a different "ofid", will occasionally get the more
> > optimal plan -- I assume that the distribution of data is the
> > differentiator
> > there.
>
> Yes, the difference between costs of the two plans is quite small (11796
> vs. 13153) so it's very sensible to data distribution.
>
> > Is there any other data I can provide to shed some light on this?
>
> You may try to play with the 'cost' constants - see this:
>
>
> http://www.postgresql.org/docs/8.4/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS
>
> You just need to modify them so that the bitmap index scan / bitmap heap
> scan is prefered to plain index scan.
>
> Just be careful - if set in the postgresql.conf, it affects all the
> queries and may cause serious problems with other queries. So it deserves
> proper testing ...
>
> regards
> Tomas
>


Playing around with seq_page_cost (1) and random_page_cost (1), I can get
the correct index selected. Applying those same settings to our production
server does not produce the optimal plan, though.

In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2009-09-10 15:30:19
Subject: Re: Best Profiler for PostgreSQL
Previous:From: tvDate: 2009-09-10 14:57:39
Subject: Re: Sub-optimal plan chosen

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