Re: TABLESAMPLE patch - Mailing list pgsql-hackers

On 10/04/15 21:26, Peter Eisentraut wrote:
> On 4/9/15 8:58 PM, Petr Jelinek wrote:
>> Well, you can have two approaches to this, either allow some specific
>> set of keywords that can be used to specify limit, or you let sampling
>> methods interpret parameters, I believe the latter is more flexible.
>> There is nothing stopping somebody writing sampling method which takes
>> limit as number of rows, or anything else.
>>
>> Also for example for BERNOULLI to work correctly you'd need to convert
>> the number of rows to fraction of table anyway (and that's exactly what
>> the one database which has this feature does internally) and then it's
>> no different than passing (SELECT 100/reltuples*number_of_rows FROM
>> tablename) as a parameter.
>
> What is your intended use case for this feature?  I know that "give me
> 100 random rows from this table quickly" is a common use case, but
> that's kind of cumbersome if you need to apply formulas like that.  I'm
> not sure what the use of a percentage is.  Presumably, the main use of
> this features is on large tables.  But then you might not even know how
> large it really is, and even saying 0.1% might be more than you wanted
> to handle.
>

My main intended use-case is analytics on very big tables. The 
percentages of population vs confidence levels are pretty well mapped 
there and you can get quite big speedups if you are fine with getting 
results with slightly smaller confidence (ie you care about ballpark 
figures).

But this was not really my point, the BERNOULLI just does not work well 
with row-limit by definition, it applies probability on each individual 
row and while you can get probability from percentage very easily (just 
divide by 100), to get it for specific target number of rows you have to 
know total number of source rows and that's not something we can do very 
accurately so then you won't get 500 rows but approximately 500 rows.

In any case for "give me 500 somewhat random rows from table quickly" 
you want probably SYSTEM sampling anyway as it will be orders of 
magnitude faster on big tables and yes even 0.1% might be more than you 
wanted in that case. I am not against having row limit input for methods 
which can work with it like SYSTEM but then that's easily doable by 
adding separate sampling method which accepts rows (even if sampling 
algorithm itself is same). In current approach all you'd have to do is 
write different init function for the sampling method and register it 
under new name (yes it won't be named SYSTEM but for example 
SYSTEM_ROWLIMIT then).

--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services