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

Re: Random Page Cost and Planner

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "David Jarvis" <thangalin(at)gmail(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Random Page Cost and Planner
Date: 2010-05-25 18:28:41
Message-ID: 4BFBD0890200002500031A6E@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
David Jarvis <thangalin(at)gmail(dot)com> wrote:
 
> The value for *random_page_cost* was at 2.0; reducing it to 1.1
> had a massive performance improvement (nearly an order of
> magnitude). While the results now return in 5 seconds (down from
> ~85 seconds)
 
It sounds as though the active portion of your database is pretty
much cached in RAM.  True?
 
> problematic lines remain. Bumping the query's end date by a single
> year causes a full table scan
 
> How do I persuade PostgreSQL to use the indexes, regardless of
> number of years between the two dates?
 
I don't know about "regardless of the number of years" -- but you
can make such plans look more attractive by cutting both
random_page_cost and seq_page_cost.  Some highly cached loads
perform well with these set to equal values on the order of 0.1 to
0.001.
 
> (A full table scan against 43 million rows is probably not the
> best plan.)
 
It would tend to be better than random access to 43 million rows, at
least if you need to go to disk for many of them.
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: Joshua TolleyDate: 2010-05-25 19:01:49
Subject: Re: prepared query performs much worse than regular query
Previous:From: Scott CareyDate: 2010-05-25 18:27:08
Subject: Re: prepared query performs much worse than regular query

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