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

>>>>> "FR" == Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com> writes:

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

FR> IMHO, no, no and yes.

Why (especially the "yes" part)? Any details and/or pointers?

FR> 1. get rid of cursors, unless you have a strong need for them
FR> (eg. seeking back and forth and updating).

Cursors are very convenient for me, because they allow easy browsing
data in the user interface (fetching limited sets of rows while seeking
forward and backward) and they prevent contingent seeking and other
troubles when concurrent updates happen.

FR> 2. switch to "chunked" processing, like this:

FR> SELECT * FROM bigtable ORDER by idxcol LIMIT 1000;
FR> (process the records)
FR> SELECT * FROM bigtable WHERE idxcol > [last idxcol from previous fetch]
FR> ORDER by idxcol LIMIT 1000;
FR> ... and so on.

Not counting the convenience of cursors, this wouldn't work as the
values in idxcol needn't be unique.

Thanks,
Milan Zamazal