![]() ![]() Sorting seems to be the most time consuming task here. The query also used the output of random() as the sort key to order results. > Seq Scan on ts_test (cost=9.79 rows=808863 width=36) (actual time=0.276.1001.109 rows=1000000 loops=1)Īs EXPLAIN ANALYZE points out, selecting 10 out of 1M rows took nearly 2 seconds. Let’s look into EXPLAIN ANALYZE output of this query above: random=# EXPLAIN ANALYZE SELECT * FROM ts_test ORDER BY random() LIMIT 10 Therefore this method is not preferred for tables with large number of rows because of performance reasons. ![]() Let’s create ts_test table and insert 1M rows into it: CREATE TABLE ts_test (Ĭonsidering the following SQL statement for selecting 10 random rows: SELECT * FROM ts_test ORDER BY random() LIMIT 10 Ĭauses PostgreSQL to perform a full table scan and also ordering. 1- Order by random()įor testing purposes we need to create a table and put some data inside of it. Let’s compare the traditional ways of getting random rows from a table with the new ways provided by TABLESAMPLE.īefore the TABLESAMPLE clause, there were 3 commonly used methods for randomly selecting rows from a table. Getting Random Rows from a Database Table There are awesome blog posts about selecting random rows, you can start reading the following blog posts to gain a deep understanding of this topic. How people were selecting random rows before TABLESAMPLE, what are the pros and cons of the other methods and what we gained with TABLESAMPLE? In this blog post we’ll talk about alternative ways of getting random rows. For further reading about TABLESAMPLE you can check the previous blog post. TABLESAMPLE is a SQL SELECT clause and it provides two sampling methods which are SYSTEM and BERNOULLI. With the help of TABLESAMPLE we can easily retrieve random rows from a table. PostgreSQL’s TABLESAMPLE brings a few more advantages compared to other traditional ways for getting random tuples. PostgreSQL 9 Cookbook – Chinese Edition.PostgreSQL Server Programming Cookbook – 2nd Edition.PostgreSQL 9 Administration Cookbook – 3rd Edition.PostgreSQL High Availability Cookbook – 2nd Edition. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |