Re: Bug? Small samples in TABLESAMPLE SYSTEM returns zero rows

On 08/06/2015 01:10 PM, Simon Riggs wrote:
> Given, user-stated probability of accessing a block of P and N total
> blocks, there are a few ways to implement block sampling.
>
> 1. Test P for each block individually. This gives a range of possible
> results, with 0 blocks being possible outcome, though decreasing in
> probability as P increases for fixed N. This is the same way BERNOULLI
> works, we just do it for blocks rather than rows.
>
> 2. We calculate P/N at start of scan and deliver this number blocks by
> random selection from N available blocks.
>
> At present we do (1), exactly as documented. (2) is slightly harder
> since we'd need to track which blocks have been selected already so we
> can use a random selection with no replacement algorithm. On a table
> with uneven distribution of rows this would still return a variable
> sample size, so it didn't seem worth changing.

Aha, thanks!

So, seems like this is just a doc issue? That is, we just need to
document that using SYSTEM on very small sample sizes may return
unexpected numbers of results ... and maybe also how the algorithm
actually works.

I agree that implementing (2) makes more sense as an additional
algorithm for someone to write in the future.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com