Re: Large tables, ORDER BY and sequence/index scans

>>>>> "PS" == Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:

PS> please, send explain result

For ~ 10 million rows table:

explain declare c cursor for select * from foo2 order by value;
QUERY PLAN
----------------------------------------------------------------------
Sort (cost=1829429.20..1854429.20 rows=9999999 width=10)
Sort Key: value
-> Seq Scan on foo2 (cost=0.00..154049.99 rows=9999999 width=10)
(3 rows)

For ~ 1 million rows table, containing the first million rows from foo2
(`value' column contains random integer data):

explain declare c cursor for select * from foo order by value;
QUERY PLAN
-----------------------------------------------------------------------------------
Index Scan using foo_value_idx on foo (cost=0.00..47604.02 rows=999999 width=10)
(1 row)

When seqscan is disabled for the 10 million rows table:

set enable_seqscan = off;
explain declare c cursor for select * from foo2 order by value;
QUERY PLAN
-----------------------------------------------------------------------------------------
Index Scan using foo2_value_idx on foo2 (cost=0.00..32220140.73 rows=9999999 width=10)
(1 row)

Regards,
Milan Zamazal