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

Re: Forcing the use of particular execution plans

From: Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)nl>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Forcing the use of particular execution plans
Date: 2006-09-27 12:12:18
Message-ID: 451A6AA2.9060003@oli.tudelft.nl (view raw or flat)
Thread:
Lists: pgsql-performance
Tim Truman wrote:
> 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
> 

Actually, I think the best course of action is to rewrite the query to a 
faster alternative. What you can try is:
SELECT SUM(count) AS count FROM
(
	SELECT count(*) AS count
		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 count(*) AS count
		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;


The next optimization is to merge the 2 subqueries into one. If you 
schema is such that m.id can not be the same as m.parent_merchant_id I 
think your query can be reduced to:
SELECT count(*) AS count
	FROM transaction t, merchant m
	WHERE t.merchant_id = m.id
		AND
		(
			m.id = 198
			OR
			m.parent_merchant_id = 198
		)
		AND t.transaction_date >= '20050101'
		AND t.transaction_date <= '20060925'
		AND credit_card_no LIKE '1111%111'


If m.id can be the same as m.parent_merchant_id you need something like:
SELECT SUM(
	CASE WHEN m.id = m.parent_merchant_id THEN 2 ELSE 1 END
	) AS count
	FROM transaction t, merchant m
	WHERE t.merchant_id = m.id
		AND
		(
			m.id = 198
			OR
			m.parent_merchant_id = 198
		)
		AND t.transaction_date >= '20050101'
		AND t.transaction_date <= '20060925'
		AND credit_card_no LIKE '1111%111'

Jochem

pgsql-performance by date

Next:From: Scott MarloweDate: 2006-09-27 14:58:30
Subject: Re: Merge Join vs Nested Loop
Previous:From: Tobias BroxDate: 2006-09-27 09:48:03
Subject: Re: Merge Join vs Nested Loop

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