2011/6/8 <anthony.ship...@symstream.com>: > On Wednesday 08 June 2011 17:39, Claudio Freire wrote: >> Of course optimally executing a plan with limit is a lot different >> than one without. > > I imagined that limit just cuts out a slice of the query results. > If it can find 80000 rows in 0.5 seconds then I would have thought that > returning just the first 100 of them should be just as easy. > >> >> Just... why are you sorting by diag_id? >> >> I believe you would be better off sorting by timestamp than diag_id, >> but I don't know what the query is supposed to do. > > The timestamp is only almost monotonic. I need to scan the table in slices and > I use limit and offset to select the slice. > > I've forced the query order with some pgsql like: > > declare > query character varying; > rec record; > begin > -- PG 8.3 doesn't have the 'using' syntax nor 'return query execute' > > execute 'create temporary table tt on commit drop as ' || > 'select diag_id from tdiag ' || v_where; > > query = 'select * from tdiag where diag_id in (select * from tt) ' || > 'order by diag_id ' || v_limit || ' ' || v_offset; > > for rec in execute query loop > return next rec; > end loop; > end;
if you use FOR statement, there should be a problem in using a implicit cursor - try to set a GUC cursor_tuple_fraction to 1.0. Regards Pavel Stehule > > -- > Anthony Shipman | Life is the interval > anthony.ship...@symstream.com | between pay days. > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
