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@xxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general