Yeah. I am looking for fastest possible method that Postgresql would use its internal data structure knowledge to walk through the timestamp index and resturns every "nth" row
On Mon, Jan 25, 2016 at 5:56 AM, Simon Riggs <si...@2ndquadrant.com> wrote: > On 25 January 2016 at 09:44, Matija Lesar <matija.le...@gmail.com> wrote: > > >> you can accomplish this with row_number() >> <http://www.postgresql.org/docs/8.4/static/functions-window.html#FUNCTIONS-WINDOW-TABLE> >> : >> >> WITH data_cte as ( >> SELECT >> id, >> clock_timestamp() as ctimestamp >> FROM generate_series(1,1000) as id >> ) >> SELECT >> * >> FROM >> (SELECT >> id, >> ctimestamp, >> row_number() OVER (ORDER BY ctimestamp) as rownum >> FROM data_cte >> ) as data_withrownumbers >> WHERE >> rownum%100=1; >> > > You can, but its not very fast. > > -- > Simon Riggs http://www.2ndQuadrant.com/ > <http://www.2ndquadrant.com/> > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
