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

please EXPLAIN ANALYZE

Pavel

2010/1/5 Milan Zamazal <pdm(at)brailcom(dot)org>:
>>>>>> "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
>
>