Re: TABLESAMPLE patch
От | Petr Jelinek |
---|---|
Тема | Re: TABLESAMPLE patch |
Дата | |
Msg-id | 55282B46.5010506@2ndquadrant.com обсуждение исходный текст |
Ответ на | Re: TABLESAMPLE patch (Peter Eisentraut <peter_e@gmx.net>) |
Ответы |
Re: TABLESAMPLE patch
(Tomas Vondra <tomas.vondra@2ndquadrant.com>)
|
Список | 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
В списке pgsql-hackers по дате отправления: