From: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
---|---|
To: | "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com> |
Cc: | <pgsql-performance(at)postgresql(dot)org>, "Carlos Benkendorf" <carlosbenkendorf(at)yahoo(dot)com(dot)br> |
Subject: | Re: Improving performance of a query |
Date: | 2005-09-06 14:47:16 |
Message-ID: | 6EE64EF3AB31D5448D0007DD34EEB3417DD27D@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)
>
> Unless I'm not seeing something, I don't think that's a correct
> reformulation in general. If customer < 4 and year > 2004 the original
> clause would return true but the reformulation would return false
since
> (year=2004 or customer >= 4) would be false.
You are correct, you also have to exchange '=' with '>' to exchange
'and' with 'or'.
Correct answer is:
> > (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)
It's easy to get tripped up here: the basic problem is how to get the
next record based on a multi part key. My ISAM bridge can write them
either way but the 'and' major form is always faster ;).
MErlin
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2005-09-06 14:56:26 | Re: insert performance for win32 |
Previous Message | Marc Cousin | 2005-09-06 14:12:27 | insert performance for win32 |