Re: slow query performance

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

 



Is there a way to determine a reasonable value for random_page_cost
via some testing with OS commands. We have several postgres databases
and determining this value on a case by case basis may not be viable
(we may have to go with the defaults)

On Fri, Jun 11, 2010 at 5:44 AM, Kenneth Marshall <ktm@xxxxxxxx> wrote:
> Hi Anj,
>
> That is an indication that your system was less correctly
> modeled with a random_page_cost=2 which means that the system
> will assume that random I/O is cheaper than it is and will
> choose plans based on that model. If this is not the case,
> the plan chosen will almost certainly be slower for any
> non-trivial query. You can put a 200mph speedometer in a
> VW bug but it will never go 200mph.
>
> Regards,
> Ken
>
> On Thu, Jun 10, 2010 at 07:54:01PM -0700, Anj Adu wrote:
>> I changed random_page_cost=4 (earlier 2) and the performance issue is gone
>>
>> I am not clear why a page_cost of 2 on really fast disks would perform badly.
>>
>> Thank you for all your help and time.
>>
>> On Thu, Jun 10, 2010 at 8:32 AM, Anj Adu <fotographs@xxxxxxxxx> wrote:
>> > Attached
>> >
>> > Thank you
>> >
>> >
>> > On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas <robertmhaas@xxxxxxxxx> wrote:
>> >> On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu <fotographs@xxxxxxxxx> wrote:
>> >>> The plan is unaltered . There is a separate index on theDate as well
>> >>> as one on node_id
>> >>>
>> >>> I have not specifically disabled sequential scans.
>> >>
>> >> Please do "SHOW ALL" and attach the results as a text file.
>> >>
>> >>> This query performs much better on 8.1.9 on a similar sized
>> >>> table.(althought the random_page_cost=4 on 8.1.9 and 2 on 8.4.0 )
>> >>
>> >> Well that could certainly matter...
>> >>
>> >> --
>> >> Robert Haas
>> >> EnterpriseDB: http://www.enterprisedb.com
>> >> The Enterprise Postgres Company
>> >>
>> >
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


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

  Powered by Linux