From: Iain <iain@mst.co.jp>
To: David Witham <davidw@unidial.com.au>
To: pgsql-sql@postgresql.org
Subject: Re: Indexes and statistics
Date: Thu, 19 Feb 2004 12:44:22 +0900
Message-ID: <005f01c3f69a$a9632770$7201a8c0@mst1x5r347kymb> (raw)
References: <CFA248776934FD43847E740E43C346D199DC13@ozimelb03.ozicom.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iain@mst.co.jp, davidw@unidial.com.au
Subject: Re: Indexes and statistics
In-Reply-To: <005f01c3f69a$a9632770$7201a8c0@mst1x5r347kymb>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox