Re: Large tables, ORDER BY and sequence/index scans - Mailing list pgsql-general

>>>>> "PS" == Pavel Stehule <pavel.stehule@gmail.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