Re: where (x,y,z) in ((x1,y1, z1), (x1,y1, z1), (x1,y1, z1), (x2,y2, z2)) (not) optimized

[Filip Rembiałkowski <plk.zuber@xxxxxxxxx>]

2009/1/26 Grzegorz Jaśkiewicz <gryzman@xxxxxxxxx>

Hey folks,

I have question really for all mighty developers, but don't want to
spam -hackers with it.

why :
select * from foo where X in (1,1,1,1,1,1,1,1) --- same values in search.
or select * from foo where (x,y) in ((1,2),(1,2),(1,2),(1,2),(1,2),(1,2),(1,2));

never gets optimized by planner, etc ?

I would guess that optimizing silly-written queries was always a low-priority task...

IMHO this is good topic for -hackers list.. and probably not so hard to implement :)

BTW, test on CVS HEAD:

CREATE TABLE atest(id integer primary key);
insert into atest select x from generate_series(1,100000) x(x);
ANALYZE atest;
EXPLAIN ANALYZE SELECT * FROM atest where id in (1,2,3,3,3,3,3,3,3,5,5,5,5,5,5,5,5,5,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
EXPLAIN ANALYZE SELECT * FROM atest where id in (1,2,3,5);

shows that second query is 2.5 times faster than the first ( 0.170 ms / 0.070 ms).

Is it just not worth optimizing from pg side? I am sure, it would make
sense to actually reorder these values, so that index/whatnot could
pick it up faster.

Just another one of those, 'why' (not) questions from my side.

thanks.

--
GJ

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

-- 
Filip Rembiałkowski