Re: Forcing using index instead of sequential scan?

More information from the query:-

explain analyze
SELECT
d0.dmth,
count(f.fval )
FROM
dim0 AS d0,
fact0 AS f
WHERE d0.d0key = f.d0key
AND d0.ddate BETWEEN '2010-01-01' AND '2010-12-28'
GROUP BY
d0.dmth
;

QUERY PLAN

------------------------------------------------------------------------
-------------------------------------------------------------
HashAggregate (cost=336998.83..336998.84 rows=1 width=8) (actual
time=33823.124..33823.134 rows=12 loops=1)
-> Hash Join (cost=214.83..335343.83 rows=331000 width=8) (actual
time=61.065..33605.343 rows=336000 loops=1)
Hash Cond: ("outer".d0key = "inner".d0key)
-> Seq Scan on fact0 f (cost=0.00..281819.00 rows=10000000
width=8) (actual time=12.766..28945.036 rows=10000000 loops=1)
-> Hash (cost=214.00..214.00 rows=331 width=8) (actual
time=31.120..31.120 rows=336 loops=1)
-> Seq Scan on dim0 d0 (cost=0.00..214.00 rows=331
width=8) (actual time=26.362..30.895 rows=336 loops=1)
Filter: ((ddate >= '2010-01-01'::date) AND (ddate
<= '2010-12-28'::date))
Total runtime: 33823.220 ms
(8 rows)

benchw=# \d fact0
Table "public.fact0"
Column | Type | Modifiers
--------+------------------------+-----------
d0key | integer | not null
d1key | integer | not null
d2key | integer | not null
fval | integer | not null
ffill | character varying(100) | not null
Indexes:
"fact0_d0key" btree (d0key)
"fact0_d1key" btree (d1key)
"fact0_d2key" btree (d2key)

benchw=# \d dim0
Table "public.dim0"
Column | Type | Modifiers
--------+---------+-----------
d0key | integer | not null
ddate | date | not null
dyr | integer | not null
dmth | integer | not null
dday | integer | not null
Indexes:
"dim0_d0key" UNIQUE, btree (d0key)

The example on the web site has the following execution plan:-

QUERY PLAN

------------------------------------------------------------------------
--------------------
HashAggregate (cost=286953.94..286953.94 rows=1 width=8)
-> Nested Loop (cost=0.00..285268.93 rows=337002 width=8)
-> Seq Scan on dim0 d0 (cost=0.00..219.00 rows=337 width=8)
Filter: ((ddate >= '2010-01-01'::date) AND (ddate <=
'2010-12-28'::date))
-> Index Scan using fact0_d0key on fact0 f (cost=0.00..833.07
rows=1022 width=8)
Index Cond: ("outer".d0key = f.d0key)

It uses the index on the join condition.

When I disable the sequential scan with:-

SET enable_seqscan TO off;

The execution plan looks like:-

QUERY
PLAN
------------------------------------------------------------------------
----------------------------------------------------------------
HashAggregate (cost=648831.52..648831.53 rows=1 width=8) (actual
time=19155.060..19155.071 rows=12 loops=1)
-> Nested Loop (cost=7.51..647176.52 rows=331000 width=8) (actual
time=97.878..18943.155 rows=336000 loops=1)
-> Index Scan using dim0_d0key on dim0 d0 (cost=0.00..248.00
rows=331 width=8) (actual time=40.467..55.780 rows=336 loops=1)
Filter: ((ddate >= '2010-01-01'::date) AND (ddate <=
'2010-12-28'::date))
-> Bitmap Heap Scan on fact0 f (cost=7.51..1941.94 rows=1002
width=8) (actual time=0.991..55.391 rows=1000 loops=336)
Recheck Cond: ("outer".d0key = f.d0key)
-> Bitmap Index Scan on fact0_d0key (cost=0.00..7.51
rows=1002 width=0) (actual time=0.583..0.583 rows=1000 loops=336)
Index Cond: ("outer".d0key = f.d0key)
Total runtime: 19155.176 ms
(9 rows)

The query is 19 seconds long now; down from 34 seconds although the
execution plan doesn't match the example from the website.

Regards

Robin
-----Original Message-----
From: Peter Eisentraut [mailto:peter_e(at)gmx(dot)net]
Sent: 21 July 2006 12:46
To: pgsql-performance(at)postgresql(dot)org
Cc: 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:
> What is the best way to force the use of indexes in these queries?

Well, the brute-force method is to use SET enable_seqscan TO off, but if

you want to get to the bottom of this, you should look at or post the
EXPLAIN ANALYZE output of the offending queries.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/