Re: [HACKERS] micro bucket sort ...

2010-08-13 Thread Hitoshi Harada
2010/8/12 PostgreSQL - Hans-Jürgen Schönig postg...@cybertec.at: as tom pointed out - this is not possible. there is no limit 20 in my case - i just used it to indicate that limiting does not make the index scan possible which it does in some other cases. I came up with this: explain analyze

Re: [HACKERS] micro bucket sort ...

2010-08-13 Thread Greg Stark
2010/8/11 Hans-Jürgen Schönig postg...@cybertec.at: now, the problem is: i cannot easily create additional indexes as i have too many possible second conditions here. Is it just me or is this description of the problem not very specific? Can you give more examples of your queries and explain

[HACKERS] micro bucket sort ...

2010-08-11 Thread Hans-Jürgen Schönig
hello all ... i am bugged with a small issue which is basically like this ... test=# create table t_test as select x, x % 5 as y from generate_series(1, 100) AS x; SELECT test=# create index idx_a on t_test (x) ; CREATE INDEX test=# ANALYZE ; ANALYZE test=# explain analyze select * from

Re: [HACKERS] micro bucket sort ...

2010-08-11 Thread Simon Riggs
On Wed, 2010-08-11 at 14:21 +0200, Hans-Jürgen Schönig wrote: my question is: is there already a concept out there to make this work or does anybody know of a patch out there addressing an issue like that? some idea is heavily appreciated. it seems our sort key infrastructure is not enough

Re: [HACKERS] micro bucket sort ...

2010-08-11 Thread Alvaro Herrera
Excerpts from Hans-Jürgen Schönig's message of mié ago 11 08:21:10 -0400 2010: same with limit ... test=# explain analyze select * from t_test order by x, y limit 20; But if you put the limit in a subquery which is ordered by the known-indexed condition, it is very fast: alvherre=#

Re: [HACKERS] micro bucket sort ...

2010-08-11 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Hans-Jürgen Schönig's message of mié ago 11 08:21:10 -0400 2010: test=# explain analyze select * from t_test order by x, y limit 20; But if you put the limit in a subquery which is ordered by the known-indexed condition, it

Re: [HACKERS] micro bucket sort ...

2010-08-11 Thread PostgreSQL - Hans-Jürgen Schönig
as tom pointed out - this is not possible. there is no limit 20 in my case - i just used it to indicate that limiting does not make the index scan possible which it does in some other cases. the partial sort thing simon pointed out is what is needed at this point. many thanks,