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@gmx.net]
Sent: 21 July 2006 12:46
To: pgsql-performance@postgresql.org
Cc: Smith,R,Robin,XJE4JA C
Subject: Re: [PERFORM] Forcing using index instead of sequential scan?


robin.c.smith@bt.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/