Skip site navigation (1) Skip section navigation (2)

Forcing the use of particular execution plans

From: "Tim Truman" <tim(at)advam(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Forcing the use of particular execution plans
Date: 2006-09-27 06:40:11
Message-ID: 000501c6e1ff$c80022c0$0340050a@sdg (view raw or flat)
Thread:
Lists: 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


Responses

pgsql-performance by date

Next:From: Tobias BroxDate: 2006-09-27 09:48:03
Subject: Re: Merge Join vs Nested Loop
Previous:From: Jim NasbyDate: 2006-09-27 03:42:16
Subject: Re: Decreasing BLKSZ

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group