Re: Improving performance of a query - Mailing list pgsql-performance

From Merlin Moncure
Subject Re: Improving performance of a query
Date
Msg-id 6EE64EF3AB31D5448D0007DD34EEB3417DD278@Herge.rcsinc.local
Whole thread Raw
In response to Improving performance of a query  (Carlos Benkendorf <carlosbenkendorf@yahoo.com.br>)
Responses Re: Improving performance of a query  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
List 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

pgsql-performance by date:

Previous
From: Kevin
Date:
Subject: Re: Advise about how to delete entries
Next
From: Chris Browne
Date:
Subject: Re: When to do a vacuum for highly active table