2011/6/8 <anthony.shipman@xxxxxxxxxxxxx>: > 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.Shipman@xxxxxxxxxxxxx  | between pay days. > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
