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

2009/1/26 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Filip Rembiałkowski