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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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