From: "Dan Langille" <dan@langille.org>
To: pgsql-performance@postgresql.org
Date: Tue, 22 Aug 2006 05:09:36 -0400
MIME-Version: 1.0
Subject: Forcing index usage without 'enable_hashjoin = FALSE'
Message-ID: <44EA9190.5306.162A8DE9@dan.langille.org>
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

I'm using PostgreSQL 8.1.4 and I'm trying to force the planner to use
an index.  With the index, I get executions times of 0.5 seconds. 
Without, it's closer to 2.5 seconds.

Compare these two sets of results (also provided at 
http://rafb.net/paste/results/ywcOZP66.html
should it appear poorly formatted below):

freshports.org=# \i test2.sql
                                                                 
QUERY PLAN
----------------------------------------------------------------------
----------------------------------------------------------------------
-
 Merge Join  (cost=24030.39..24091.43 rows=3028 width=206) (actual 
time=301.301..355.261 rows=3149 loops=1)
   Merge Cond: ("outer".id = "inner".category_id)
   ->  Sort  (cost=11.17..11.41 rows=97 width=4) (actual 
time=0.954..1.300 rows=95 loops=1)
         Sort Key: c.id
         ->  Seq Scan on categories c  (cost=0.00..7.97 rows=97 
width=4) (actual time=0.092..0.517 rows=97 loops=1)
   ->  Sort  (cost=24019.22..24026.79 rows=3028 width=206) (actual 
time=300.317..314.114 rows=3149 loops=1)
         Sort Key: p.category_id
         ->  Nested Loop  (cost=0.00..23844.14 rows=3028 width=206) 
(actual time=0.082..264.459 rows=3149 loops=1)
               ->  Seq Scan on ports p  (cost=0.00..6141.11 rows=3028 
width=206) (actual time=0.026..133.575 rows=3149 loops=1)
                     Filter: (status = 'D'::bpchar)
               ->  Index Scan using element_pkey on element e  
(cost=0.00..5.83 rows=1 width=4) (actual time=0.022..0.026 rows=1 
loops=3149)
                     Index Cond: ("outer".element_id = e.id)
 Total runtime: 369.869 ms
(13 rows)

freshports.org=# set enable_hashjoin = true;
SET
freshports.org=# \i test2.sql
                                                           QUERY PLAN
----------------------------------------------------------------------
----------------------------------------------------------
 Hash Join  (cost=6156.90..13541.14 rows=3028 width=206) (actual 
time=154.741..2334.366 rows=3149 loops=1)
   Hash Cond: ("outer".category_id = "inner".id)
   ->  Hash Join  (cost=6148.68..13472.36 rows=3028 width=206) 
(actual time=153.801..2288.792 rows=3149 loops=1)
         Hash Cond: ("outer".id = "inner".element_id)
         ->  Seq Scan on element e  (cost=0.00..4766.70 rows=252670 
width=4) (actual time=0.022..1062.626 rows=252670 loops=1)
         ->  Hash  (cost=6141.11..6141.11 rows=3028 width=206) 
(actual time=151.105..151.105 rows=3149 loops=1)
               ->  Seq Scan on ports p  (cost=0.00..6141.11 rows=3028 
width=206) (actual time=0.027..131.072 rows=3149 loops=1)
                     Filter: (status = 'D'::bpchar)
   ->  Hash  (cost=7.97..7.97 rows=97 width=4) (actual 
time=0.885..0.885 rows=97 loops=1)
         ->  Seq Scan on categories c  (cost=0.00..7.97 rows=97 
width=4) (actual time=0.076..0.476 rows=97 loops=1)
 Total runtime: 2346.877 ms
(11 rows)

freshports.org=#

Without leaving "enable_hashjoin = false", can you suggest a way to 
force the index usage?

FYI, the query is:

explain analyse
SELECT P.id,
       P.category_id,
       P.version         as version,
       P.revision        as revision,
       P.element_id,
       P.maintainer,
       P.short_description,
       to_char(P.date_added - SystemTimeAdjust(), 'DD Mon YYYY 
HH24:MI:SS') as date_added,
       P.last_commit_id  as last_change_log_id,
       P.package_exists,
       P.extract_suffix,
       P.homepage,
       P.status,
       P.broken,
       P.forbidden,
       P.ignore,
       P.restricted,
       P.deprecated,
       P.no_cdrom,
       P.expiration_date,
       P.latest_link
  FROM categories C, ports P JOIN element E on P.element_id = E.id
 WHERE P.status      = 'D'
   AND P.category_id = C.id;

-- 
Dan Langille : Software Developer looking for work
my resume: http://www.freebsddiary.org/dan_langille.php