DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=ZWk0V/MBcyAl4oqyp1awEYXLSDp78AMf9xnY0s4SiVwBDRtyziIJ94MJHGQxq9hw0wV6w+E4ZPCvkOzFEYgOVKYdiStl/rZ5ATKwVbl3ToblBz+5vuz1wH832lBJNjIeXm7csi9yJDLLxPYdyretm9TE1YS9f5dwSM8o4UFjt2I=
Message-ID: <44EBCC2D.5050206@gmail.com>
Date: Wed, 23 Aug 2006 13:31:57 +1000
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
To: Dan Langille <dan@langille.org>
CC: pgsql-performance@postgresql.org
Subject: Re: Forcing index usage without 'enable_hashjoin = FALSE'
References: <44EA9190.5306.162A8DE9@dan.langille.org>
In-Reply-To: <44EA9190.5306.162A8DE9@dan.langille.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dan Langille wrote:
> 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;
> 

I doubt it would make a difference but if you:

...
FROM categories C JOIN ports P on P.category_id=C.id JOIN element E on 
P.element_id = E.id
WHERE P.status      = 'D';

does it change anything?

-- 
Postgresql & php tutorials
http://www.designmagick.com/