Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id A58F59FB331
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 27 Sep 2006 03:39:08 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 24630-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 27 Sep 2006 03:39:01 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp01.syd.iprimus.net.au (smtp01.syd.iprimus.net.au
	[210.50.30.196])
	by postgresql.org (Postfix) with ESMTP id EC68F9FB2D8
	for <pgsql-performance@postgresql.org>;
	Wed, 27 Sep 2006 03:39:00 -0300 (ADT)
Received: from sdg (211.26.53.233) by smtp01.syd.iprimus.net.au (7.2.072.1)
	id 442E344D03F6A9B4 for pgsql-performance@postgresql.org;
	Wed, 27 Sep 2006 16:38:57 +1000
From: "Tim Truman" <tim@advam.com>
To: <pgsql-performance@postgresql.org>
Subject: Forcing the use of particular execution plans
Date: Wed, 27 Sep 2006 16:10:11 +0930
Message-ID: <000501c6e1ff$c80022c0$0340050a@sdg>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 11
Thread-Index: Acbh/8eezyYwPKejQSqqUZV9ctHuuQ==
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200609/348
X-Sequence-Number: 21034

Hi,

I have the following query which has been running very slowly and after a
lot of testing/trial and error I found an execution plan that ran the query
in a fraction of the time (and then lost the statistics that produced it).
What I wish to know is how to force the query to use the faster execution
plan.

Query:
SELECT count(*) as count FROM 
( 
	SELECT *
		FROM transaction t, merchant m
		WHERE t.merchant_id = m.id 
			AND m.id = 198
			AND t.transaction_date >= '20050101'
			AND t.transaction_date <= '20060925'
			AND credit_card_no LIKE '1111%111'

	UNION ALL
	SELECT *
		FROM transaction t, merchant m
		WHERE t.merchant_id = m.id
			AND m.parent_merchant_id = 198
			AND t.transaction_date >= '20050101'
			AND t.transaction_date <= '20060925'
			AND credit_card_no LIKE '1111%111'
) AS foobar

Desired Execution Plan:
Aggregate (cost=97377.90..97377.90 rows=1 width=0)
  -> Subquery Scan foobar (cost=0.00..97377.86 rows=16 width=0)
        -> Append (cost=0.00..97377.70 rows=16 width=636)
              -> Subquery Scan "*SELECT* 1" (cost=0.00..10304.81 rows=3
width=636)
                    -> Nested Loop (cost=0.00..10304.78 rows=3 width=636)
                          -> Index Scan using pk_merchant on merchant m
(cost=0.00..5.11 rows=1 width=282)
                                Index Cond: (id = 198)
                          -> Index Scan using ix_transaction_merchant_id on
"transaction" t (cost=0.00..10299.64 rows=3 width=354)
                                Index Cond: (198 = merchant_id)
                                Filter: ((transaction_date >=
'2005-01-01'::date) AND (transaction_date <= '2006-09-25'::date) AND
((credit_card_no)::text ~~ '4564%549'::text))
              -> Subquery Scan "*SELECT* 2" (cost=13.86..87072.89 rows=13
width=636)
                    -> Hash Join (cost=13.86..87072.76 rows=13 width=636)
                          Hash Cond: ("outer".merchant_id = "inner".id)
                          -> Seq Scan on "transaction" t
(cost=0.00..87052.65 rows=1223 width=354)
                                Filter: ((transaction_date >=
'2005-01-01'::date) AND (transaction_date <= '2006-09-25'::date) AND
((credit_card_no)::text ~~ '4564%549'::text))
                          -> Hash (cost=13.85..13.85 rows=4 width=282)
                                -> Index Scan using
ix_merchant_parent_merchant_id on merchant m (cost=0.00..13.85 rows=4
width=282)
                                      Index Cond: (parent_merchant_id = 198)

Undesired Execution Plan:
Aggregate  (cost=88228.82..88228.82 rows=1 width=0)
  ->  Subquery Scan foobar  (cost=0.00..88228.73 rows=35 width=0)
        ->  Append  (cost=0.00..88228.38 rows=35 width=631)
              ->  Subquery Scan "*SELECT* 1"  (cost=0.00..1137.61 rows=1
width=631)
                    ->  Nested Loop  (cost=0.00..1137.60 rows=1 width=631)
                          ->  Index Scan using ix_transaction_merchant_id on
"transaction" t  (cost=0.00..1132.47 rows=1 width=349)
                                Index Cond: (198 = merchant_id)
                                Filter: ((transaction_date >=
'2005-01-01'::date) AND (transaction_date <= '2006-09-25'::date) AND
((credit_card_no)::text ~~ '4564%549'::text))
                          ->  Index Scan using pk_merchant on merchant m
(cost=0.00..5.11 rows=1 width=282)
                                Index Cond: (id = 198)
              ->  Subquery Scan "*SELECT* 2"  (cost=20.90..87090.77 rows=34
width=631)
                    ->  Hash Join  (cost=20.90..87090.43 rows=34 width=631)
                          Hash Cond: ("outer".merchant_id = "inner".id)
                          ->  Seq Scan on "transaction" t
(cost=0.00..87061.04 rows=1632 width=349)
                                Filter: ((transaction_date >=
'2005-01-01'::date) AND (transaction_date <= '2006-09-25'::date) AND
((credit_card_no)::text ~~ '4564%549'::text))
                          ->  Hash  (cost=20.88..20.88 rows=8 width=282)
                                ->  Seq Scan on merchant m
(cost=0.00..20.88 rows=8 width=282)
                                      Filter: (parent_merchant_id = 198)



Thanks for any help/ideas


Tim