From: Jochem van Dieten <jochemd@oli.tudelft.nl>
To: pgsql-performance@postgresql.org
Subject: Re: Forcing the use of particular execution plans
Date: Wed, 27 Sep 2006 14:12:18 +0200
Message-ID: <451A6AA2.9060003@oli.tudelft.nl> (raw)
References: <00501c6e1ff$c80022c0$0340050a@sdg>
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
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: jochemd@oli.tudelft.nl
Subject: Re: Forcing the use of particular execution plans
In-Reply-To: <451A6AA2.9060003@oli.tudelft.nl>
* 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