Query optimization to select rows instead of too many or conditions

On Saturday, February 21, 2015 12:42:03 PM Alban Hertroys wrote:

On 21 Feb 2015, at 9:34, Arup Rakshit <aruprakshit@rocketmail.com> wrote:

Select * from Emp
where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14and attr2 = val15 and attr3 = val16);

Now suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my query as :

Select * from Emp
where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14 and attr2 = val15 and attr3 = val16) or
(attr1 = x1 and attr2 = x2 and attr3 = x3) or (attr1 = y1 and attr2 = y2 and attr3 = y3);

So for each new set I need to add one more `or` conditions. It seems wired.

Any better way to get it done ?

If the number of attributes to compare is always the same, you can write:

select * from Emp where (attr1, attr2, attr3) in ((val11, val12, val13), (val14, val15, val16), (x1, x2, x3), (y1, y2, y3));

Is that the sort of thing you're after?

Alban Hertroys

Thanks for the reply, Can the comparisons be done using Array ? I tried, but got some syntax error.

psql (9.2.7)
Type "help" for help.

app_development=# select * from pets where ARRAY[id, animals] IN '{{1,2,3},{4,5,6},{7,8,9}}';
ERROR: syntax error at or near "'{{1,2,3},{4,5,6},{7,8,9}}'"
LINE 1: select * from pets where ARRAY[id, animals] IN '{{1,2,3},{4,...
^
app_development=# select * from pets where ARRAY[id, animals] IN '{{6, 1}, {5, 1}}';
ERROR: syntax error at or near "'{{6, 1}, {5, 1}}'"
LINE 1: select * from pets where ARRAY[id, animals] IN '{{6, 1}, {5,...
^
app_development=#

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

--Brian Kernighan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 21 Feb 2015, at 9:34, Arup Rakshit <aruprakshit@rocketmail.com> wrote:

Select * from Emp
where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14and attr2 = val15 and attr3 = val16);

Now suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my query as :

Select * from Emp
where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14 and attr2 = val15 and attr3 = val16) or
(attr1 = x1 and attr2 = x2 and attr3 = x3) or (attr1 = y1 and attr2 = y2 and attr3 = y3);

So for each new set I need to add one more `or` conditions. It seems wired.

Any better way to get it done ?

If the number of attributes to compare is always the same, you can write:

select * from Emp where (attr1, attr2, attr3) in ((val11, val12, val13), (val14, val15, val16), (x1, x2, x3), (y1, y2, y3));

Is that the sort of thing you're after?

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 21 Feb 2015, at 12:03, Arup Rakshit <aruprakshit@rocketmail.com> wrote:

On Saturday, February 21, 2015 12:42:03 PM Alban Hertroys wrote:

On 21 Feb 2015, at 9:34, Arup Rakshit <aruprakshit@rocketmail.com> wrote:

Select * from Emp
where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14and attr2 = val15 and attr3 = val16);

Now suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my query as :

Select * from Emp
where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14 and attr2 = val15 and attr3 = val16) or
(attr1 = x1 and attr2 = x2 and attr3 = x3) or (attr1 = y1 and attr2 = y2 and attr3 = y3);

So for each new set I need to add one more `or` conditions. It seems wired.

Any better way to get it done ?

If the number of attributes to compare is always the same, you can write:

select * from Emp where (attr1, attr2, attr3) in ((val11, val12, val13), (val14, val15, val16), (x1, x2, x3), (y1, y2, y3));

Is that the sort of thing you're after?

Alban Hertroys

Thanks for the reply, Can the comparisons be done using Array ? I tried, but got some syntax error.

Why would you want that?

psql (9.2.7)
Type "help" for help.

app_development=# select * from pets where ARRAY[id, animals] IN '{{1,2,3},{4,5,6},{7,8,9}}';
ERROR: syntax error at or near "'{{1,2,3},{4,5,6},{7,8,9}}'"
LINE 1: select * from pets where ARRAY[id, animals] IN '{{1,2,3},{4,...
^
app_development=# select * from pets where ARRAY[id, animals] IN '{{6, 1}, {5, 1}}';
ERROR: syntax error at or near "'{{6, 1}, {5, 1}}'"
LINE 1: select * from pets where ARRAY[id, animals] IN '{{6, 1}, {5,…

At the very least you will need to put braces around the set in IN.

But even then, you'll have to write the query such that ALL elements in your left-hand array are being matched to ALL elements in any of the right-hand arrays. Is that possible? Probably, but at this point I feel like I should remind you of what you put in your signature.

Something like this is closer to what you need:

select * from pets where ARRAY[id, animals] = ANY('{6, 1}', '{5, 1}');

I'm sure that won't match anything though, as the types are all wrong.

Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

--Brian Kernighan

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general