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, 
1000000) AS x;
SELECT
test=# create index idx_aaaaa on t_test (x) ;
CREATE INDEX
test=# ANALYZE ;
ANALYZE
test=# explain analyze select * from t_test order by x;
                                                             QUERY PLAN         
                                                    
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_aaaaa on t_test  (cost=0.00..30408.36 rows=1000000 
width=8) (actual time=0.057..311.832 rows=1000000 loops=1)
 Total runtime: 392.943 ms
(2 rows)

we know that we get sorted output from the index and thus we do the index 
traversal here ...
if you add a condition to the sorting you will naturally get a sort in postgres 
because y is clearly now known to be sorted.

test=# explain analyze select * from t_test order by x, y;
                                                       QUERY PLAN               
                                        
------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=141431.84..143931.84 rows=1000000 width=8) (actual 
time=1086.014..1271.257 rows=1000000 loops=1)
   Sort Key: x, y
   Sort Method:  external sort  Disk: 17608kB
   ->  Seq Scan on t_test  (cost=0.00..14425.00 rows=1000000 width=8) (actual 
time=0.024..143.474 rows=1000000 loops=1)
 Total runtime: 1351.848 ms
(5 rows)


same with limit ...


test=# explain analyze select * from t_test order by x, y limit 20;
                                                          QUERY PLAN            
                                              
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=41034.64..41034.69 rows=20 width=8) (actual time=317.939..317.943 
rows=20 loops=1)
   ->  Sort  (cost=41034.64..43534.64 rows=1000000 width=8) (actual 
time=317.934..317.936 rows=20 loops=1)
         Sort Key: x, y
         Sort Method:  top-N heapsort  Memory: 26kB
         ->  Seq Scan on t_test  (cost=0.00..14425.00 rows=1000000 width=8) 
(actual time=0.019..144.109 rows=1000000 loops=1)
 Total runtime: 317.995 ms
(6 rows)

now, the problem is: i cannot easily create additional indexes as i have too 
many possible "second" conditions here.
what makes it even more funny: i don't have enough space  to do the resort of 
the entire thing (X TB).
so, a more expensive index traversal is my only option.

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 for this.

        many thanks,

                hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de


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

Reply via email to