Re: TABLESAMPLE usage

[Tom Smith <tomsmith1989sk@xxxxxxxxx>]

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 <simon@xxxxxxxxxxxxxxx> wrote:

On 25 January 2016 at 09:44, Matija Lesar <matija.lesar@xxxxxxxxx> wrote:

 

you can accomplish this with row_number():

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/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services