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)
Список
Дерево обсуждения
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 Tom Lane <tgl@sss.pgh.pa.us>
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@gmail.com>
Re: 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>
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 по дате отправления
От: Phoenix Kiula
Дата:
От: Merlin Moncure
Дата:
