Re: Forcing using index instead of sequential scan?

The tables have all been analysed.

I set the work_mem to 500000 and it still doesn't use the index :-(

Regards

Robin

-----Original Message-----
From: Richard Huxton [mailto:dev(at)archonet(dot)com]
Sent: 21 July 2006 12:54
To: Smith,R,Robin,XJE4JA C
Subject: Re: [PERFORM] Forcing using index instead of sequential scan?

robin(dot)c(dot)smith(at)bt(dot)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.

Well, you don't want to be forcing it if possible. Ideally, PG should be

able to figure out what to use itself.

In the case of query0 and query1 as shown on your web-page I'd expect a
sequential scan of dim0 then access via the index on fact0. Reasons why
this might not be happening include:
1. Inaccurate stats - ANALYSE your tables
2. Insufficient memory for sorting etc - issue SET work_mem=XXX before
the query and try increased values.
3. Other parameters are out-of-whack. For example, effective_cache_size
doesn't change how much cache PG uses, it tells PG how much the O.S.
will cache. You might find http://www.powerpostgresql.com/PerfList is a
good quick introduction.

So - ANALYSE your tables
http://www.postgresql.org/docs/8.1/static/sql-analyze.html

Then post EXPLAIN ANALYSE for the queries and we'll see what they're
doing.
--
Richard Huxton
Archonet Ltd