Re: Indexes and statistics - Mailing list pgsql-sql

From Iain
Subject Re: Indexes and statistics
Date
Msg-id 005f01c3f69a$a9632770$7201a8c0@mst1x5r347kymb
Whole thread Raw
In response to Indexes and statistics  ("David Witham" <davidw@unidial.com.au>)
List pgsql-sql
> The computed cost of using the index was a factor of 10 higher which I
presume is why the query planner wasn't
> using the index, but it ran in half the time

Have you tried playing with the random_page_cost parameter? The default is
4. Try:

set random_page_cost = 1;

in psql to alter it for the current session (you can change this in
postgresql.conf too). This will make index usage more attractive by reducing
the computed cost. This is the simple way of looking at it anyway.

On my system I tested a 'typical' query exercising some joins on large
tables which didn't use an index, but I thought maybe it would perform
better if it did. I determined that a random_page_cost of 1.8 would cause
indexes to be used, but in this case the *actual* performance didn't improve
very much. I took this to mean that a random_page_cost of around 1.8/1.9
represents a rough balance point on my development server (one slow IDE
disk, and a big database).

As well as the other things mentioned by Tom, perhaps you should be looking
for the "correct" setting of random_page_cost for your system. It may be
appropriate to alter it globally using postgresql.conf, and for specific
situations such as you mentioned.

HTH
Iain



pgsql-sql by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: Need some help with crafting a query to do major update
Next
From: "George A.J"
Date:
Subject: Distributed Transactions