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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tim@advam.com
Subject: Re: Forcing the use of particular execution plans
In-Reply-To: <000501c6e1ff$c80022c0$0340050a@sdg>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox