Re: Random Page Cost and Planner

[David Jarvis <thangalin@xxxxxxxxx>]

Hi, Bryan.

I was just about to reply to the thread, thanks for asking. Clustering was key. After rebooting the machine (just to make sure absolutely
nothing was cached), I immediately ran a report on Toronto: 5.25 seconds!

Here's what I did:

1. Created a new set of tables that matched the old set, with statistics of 1000 on the station and taken (date) columns.
2. Inserted the data from the old hierarchy into the new set, ordered by station id then by date (same seven child tables as before: one per category).
• I wanted to ensure a strong correlation between primary key and station id.
  
3. Added three indexes per table: (a) station id; (b) date taken; and (c) station-taken-category.
4. Set the station-taken-category index as CLUSTER.
5. Vacuumed the new tables.
   
6. Dropped the old tables.
7. Set the following configuration values:
• shared_buffers = 1GB
• temp_buffers = 32MB
• work_mem = 32MB
• maintenance_work_mem = 64MB
• seq_page_cost = 1.0
• random_page_cost = 2.0
• cpu_index_tuple_cost = 0.001
• effective_cache_size = 512MB
  

I ran a few more reports (no reboots, but reading vastly different data sets):

• Vancouver: 4.2s
• Yellowknife: 1.7s
• Montreal: 6.5s
• Trois-Riviers: 2.8s

No full table scans. I imagine some indexes are not strictly
necessary and will test to see which can be removed (my guess: the station and taken indexes). The problem was that the station ids
were scattered and so PostgreSQL presumed a full table scan would
be faster. 

Physically ordering the data by station ids triggers index use every time.

Next week's hardware upgrade should halve those times -- unless anyone has further suggestions to squeeze more performance out of PG. ;-)

Dave