From: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
---|---|
To: | "Carlos Benkendorf" <carlosbenkendorf(at)yahoo(dot)com(dot)br> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Improving performance of a query |
Date: | 2005-09-06 12:59:46 |
Message-ID: | 6EE64EF3AB31D5448D0007DD34EEB3417DD278@Herge.rcsinc.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Carlos wrote:
SELECT * FROM SSIRRA where
(YEAR = 2004 and CUSTOMER = 0000000004 and CODE = 00 and PART >= 00) or
(YEAR = 2004 and CUSTOMER = 0000000004 and CODE > 00) or
(YEAR = 2004 and CUSTOMER > 0000000004) or
(YEAR > 2004)
[snip]
ah, the positional query. You can always rewrite this query in the
following form:
(YEAR >= 2004) and
(YEAR = 2004 or CUSTOMER >= 0000000004) and
(YEAR = 2004 or CUSTOMER = 0000000004 or CODE >= 00) and
(YEAR = 2004 or CUSTOMER = 0000000004 or CODE = 00 or PART > 00)
This is better because it will index scan using 'year' (not customer or
part though). The true answer is to lobby for/develop proper row
constructor support so you can just
SELECT * FROM SSIRRA where (YEAR, CUSTOMER, CODE, PART) > (2004,
0000000004, 00, 00)
this is designed to do what you are trying to do but currently doesn't
work quite right.
note: in all these queries, 'order by YEAR, CUSTOMER, CODE, PART' should
probably be on the query.
Other solution: use cursor/fetch or some type of materialized solution.
Merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2005-09-06 14:09:08 | Re: Improving performance of a query |
Previous Message | Chris Browne | 2005-09-06 12:48:56 | Re: When to do a vacuum for highly active table |