Forcing the use of particular execution plans - Mailing list pgsql-performance

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