2011/10/31 Shigeru Hanada <shigeru.han...@gmail.com>: > (2011/10/30 11:34), Shigeru Hanada wrote: >> 2011/10/30 Tom Lane<t...@sss.pgh.pa.us>: >>> I think we have to. Even if we estimate that a given scan will return >>> only a few rows, what happens if we're wrong? We don't want to blow out >>> memory on the local server by retrieving gigabytes in one go. >> >> Oh, I overlooked the possibility of wrong estimation. Old PostgreSQL uses >> 1000 as default estimation, so big table which has not been analyzed may >> crashes the backend. >> >> To ensure the data retrieving safe, we need to get actual amount of result, >> maybe by executing SELECT COUNT(*) in planning phase. It sounds too heavy >> to do for every scan, and it still lacks actual width. >> >> One possible idea is to change default value of min_cursur_rows option to 0 >> so that pgsql_fdw uses CURSOR by default, but it seems not enough. I'll >> drop simple SELECT mode from first version of pgsql_fdw for safety. > > I removed simple SELECT mode from pgsql_fdw, and consequently also > removed min_cursor_rows FDW option. This fix avoids possible memory > exhaustion due to wrong estimation gotten from remote side. > > Once libpq has had capability to retrieve arbitrary number of rows from > remote portal at a time without server-side cursor in future, then we > will be able to revive simple SELECT. Then it's enough safe even if we > don't have actual data size, but (maybe) faster than cursor mode because > we can reduce # of SQL commands. Though of course proof of performance > advantage should be shown before such development.
If you need a less SQL commands, then you can increase fetch_count parameter - default 1000 is maybe too small, maybe 10000 lines as default (not more). For more complex queries can be interesting to set a cursor_tuple_fraction Pavel > > -- > Shigeru Hanada > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
