On Tue, Oct 18, 2016 at 5:06 PM, Andy Colson <andy@xxxxxxxxxxxxxxx> wrote: > I wanted to report an awesome performance boost using tablesample. > In my stored function I was getting a random row using: > select one into x from ones order by random() limit 1; > When the table was smaller it worked fine, but the performance has slowly > gotten worse. This morning I was getting around 8 transactions a second. Which is not a surprise, as it has to at least read all the rows and generate a random() for each one and keep track of the minimum. > I just replaced it with: > select one into x from ones tablesample bernoulli(1) limit 1; This should be faster, but to me it seems it does a different thing. This seems to select each row of the table with probability 1% and return the first selected, i.e., something similar to select one into x from ones where random()>0.01 limit 1. Which has the ( diminishing with table size ) risk of selecting zero rows and is going to select one of the first 100 or so rows with high probability, unless I'm missing something. I say this because docs state ir returns a 'randomly chosen', sample, not a 'randomly ORDERED' one, and the straightforward implementation of sampling returns rows in the primitive scan order. I supose it could be easily tested by selecting bernouilli(100), but have not server access now to verify it. With a big table it seems: select one into x from ones where random()>0.01 order by random() limit 1 or select one into x from ones tablesample bernoulli(1) order by random() limit 1; Is more similar to what you originally did ( and the run time should possibly be something in between ). I would recomend you to execute the function and verify it does what you want ( as you say it's fast, I would try selecting a several thousands and eyeballing the result, if it does what I fear the grouping should be obvious ). Maybe you do not mind it, in which case it's ok, but a one minute run should let you know wahat you are exactly doing. Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
