- PostgreSQL 9.5 introduced the TABLESAMPLE clause into SQL. This allows you to run commands much faster by using a sample of a table's rows, giving an approximate answer. In certain cases, this can be just as useful as the most accurate answer:
postgres=# SELECT avg(id) FROM events;
avg
---------------------
500000.500
(1 row)
postgres=# SELECT avg(id) FROM events TABLESAMPLE system(1);
avg
---------------------
507434.635
(1 row)
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT avg(id) FROM events;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=16925.00..16925.01 rows=1 width=32) (actual time=204.841..204.841 rows=1 loops=1)
Buffers: shared hit=96 read=4329
-> Seq Scan on events (cost=0.00..14425.00 rows=1000000 width=4) (actual time=1.272..105.452 rows=1000000 loops=1)
Buffers: shared hit=96 read=4329
Planning time: 0.059 ms
Execution time: 204.912 ms
(6 rows)
postgres=# EXPLAIN (ANALYZE, BUFFERS)
SELECT avg(id) FROM events TABLESAMPLE system(1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Aggregate (cost=301.00..301.01 rows=1 width=32) (actual time=4.627..4.627 rows=1 loops=1)
Buffers: shared hit=1 read=46
-> Sample Scan on events (cost=0.00..276.00 rows=10000 width=4) (actual time=0.074..2.833 rows=10622 loops=1)
Sampling: system ('1'::real)
Buffers: shared hit=1 read=46
Planning time: 0.066 ms
Execution time: 4.702 ms
(7 rows)