Re: where (x,y,z) in ((x1,y1, z1), (x1,y1, z1), (x1,y1, z1), (x2,y2, z2)) (not) optimized
| От | Filip Rembiałkowski |
|---|---|
| Тема | Re: where (x,y,z) in ((x1,y1, z1), (x1,y1, z1), (x1,y1, z1), (x2,y2, z2)) (not) optimized |
| Дата | |
| Msg-id | 92869e660901260558n6c915d39y1575725189a89e5d@mail.gmail.com обсуждение исходный текст |
| Ответ на | where (x,y,z) in ((x1,y1, z1), (x1,y1, z1), (x1,y1, z1), (x2,y2, z2)) (not) optimized (Grzegorz Jaśkiewicz <gryzman@gmail.com>) |
| Ответы |
Re: where (x,y,z) in ((x1,y1, z1), (x1,y1, z1), (x1,y1,
z1), (x2,y2, z2)) (not) optimized
|
| Список | pgsql-general |
2009/1/26 Grzegorz Jaśkiewicz <gryzman@gmail.com>
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).
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Filip Rembiałkowski
В списке pgsql-general по дате отправления: