From: Mark Kirkwood <markir@paradise.net.nz>
To: robin.c.smith@bt.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Forcing using index instead of sequential scan?
Date: Sun, 23 Jul 2006 15:39:21 +1200
Message-ID: <44C2EF69.4020408@paradise.net.nz> (raw)
In-Reply-To: <D012B9600FFB984AB18C0D53C475A4A081F51B@E03MVZ3-UKDY.domain1.systemhost.net>
References: <D012B9600FFB984AB18C0D53C475A4A081F51B@E03MVZ3-UKDY.domain1.systemhost.net>
robin.c.smith@bt.com wrote:
> I have been testing the performance of PostgreSQL using the simple tool
> found at _http://benchw.sourceforge.net_ however I have found that all
> the queries it run execute with sequential scans. The website where the
> code runs has examples of the execution plan using indexes.
>
> When I disable the sequential plan query 0 and query 1 run faster (
> _http://benchw.sourceforge.net/benchw_results_postgres_history.html_ )
> by using the indexes as suggested by the website.
>
> I have tried increasing the effective_cache_size and reducing the
> random_page_cost to try and force the optimiser to use the index but it
> always uses the sequential scan.
>
> What is the best way to force the use of indexes in these queries?
> Currently testing with version 8.1.4.
>
>
Hi Robin,
Being responsible for this piece of software, I should try to help, only
saw this now sorry (nice to see someone using this).
Unless you really want to reproduce the numbers on the website, it is
best to test with Benchw's scale factor at least 2 x your physical ram,
as this makes the planner's defaults work more sensibly (and models
*most* real world data warehouse situations better!).
Cheers
Mark
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-performance@postgresql.org
Cc: markir@paradise.net.nz, robin.c.smith@bt.com
Subject: Re: Forcing using index instead of sequential scan?
In-Reply-To: <44C2EF69.4020408@paradise.net.nz>
* 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