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

2010/1/5 Milan Zamazal <pdm(at)brailcom(dot)org>:
>>>>>> "PS" == Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>
>    PS> please EXPLAIN ANALYZE Pavel
>
> I see, I'm sorry.  Here are the results:

Have you original values random_page_cost and seq_page_cost in postgres.conf?

it is strange.

Pavel

>
>  set enable_seqscan = on;
>  explain analyze declare c cursor for select * from foo2 order by value;
>                                                         QUERY PLAN
>  -------------------------------------------------------------------------------------------------------------------------
>   Sort  (cost=1829429.20..1854429.20 rows=9999999 width=10) (actual time=43432.727..49303.902 rows=9999999 loops=1)
>     Sort Key: value
>     Sort Method:  external merge  Disk: 204208kB
>     ->  Seq Scan on foo2  (cost=0.00..154049.99 rows=9999999 width=10) (actual time=0.058..1775.928 rows=9999999 loops=1)
>   Total runtime: 53693.012 ms
>  (5 rows)
>
>  set enable_seqscan = off;
>  explain analyze 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) (actual time=0.225..55055.583 rows=9999999 loops=1)
>   Total runtime: 56394.283 ms
>  (2 rows)
>
>