Hello
please, send explain result
postgres=# explain analyze declare x cursor for select * from foo;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..34.00 rows=2400 width=4) (actual
time=0.010..0.012 rows=2 loops=1)
Total runtime: 0.033 ms
(2 rows)
regards
Pavel Stehule
2010/1/5 Milan Zamazal <pdm@brailcom.org>:
> My problem is that retrieving sorted data from large tables is sometimes
> very slow in PostgreSQL (8.4.1, FWIW).
>
> I typically retrieve the data using cursors, to display them in UI:
>
> BEGIN;
> DECLARE ... SELECT ... ORDER BY ...;
> FETCH ...;
> ...
>
> On a newly created table of about 10 million rows the FETCH command
> takes about one minute by default, with additional delay during the
> contingent following COMMIT command. This is because PostgreSQL uses
> sequence scan on the table even when there is an index on the ORDER BY
> column. When I can force PostgreSQL to perform index scan (e.g. by
> setting one of the options enable_seqscan or enable_sort to off), FETCH
> response is immediate.
>
> PostgreSQL manual explains motivation for sequence scans of large tables
> and I can understand the motivation. Nevertheless such behavior leads
> to unacceptably poor performance in my particular case. It is important
> to get first resulting rows quickly, to display them to the user without
> delay.
>
> My questions are:
>
> - What is your experience with using ORDER BY + indexes on large tables?
>
> - Is there a way to convince PostgreSQL to use index scans automatically
> in cases where it is much more efficient? I tried using ANALYZE,
> VACUUM and SET STATISTICS, but without success.
>
> - Is it a good idea to set enable_seqscan or enable_sort to "off"
> globally in my case? Or to set them to "off" just before working with
> large tables? My databases contain short and long tables, often
> connected through REFERENCES or joined into views and many of shorter
> tables serve as codebooks. Can setting one of the parameters to off
> have clearly negative impacts?
>
> - Is there a recommended way to keep indexes in good shape so that the
> performance of initial rows retrievals remains good? The large tables
> are typically append-only tables with a SERIAL primary key.
>
> Thanks for any tips.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>