Database Reference
In-Depth Information
and also see how many rows are processed, and how many blocks of data accessed by
comparing the output of the following query before and after the query is run on idle
system (the pg_stat* views are global, and collect info from all parallel queries):
select
s.relid, s.schemaname, s.relname,
seq_scan,seq_tup_read,
idx_scan,idx_tup_fetch,
heap_blks_read,heap_blks_hit,
idx_blks_read,idx_blks_hit,
toast_blks_read,toast_blks_hit
from pg_stat_user_tables s
join pg_statio_user_tables sio on s.relid = sio.relid
where s.schemaname = 'public' and s.relname = 't'
For example, if you want to get the three latest rows in a one million-row table, then run
the following:
SELECT * FROM EVENTS ORDER BY ID DESC LIMIT 3;
You can either read through just three rows using an index on the serial id column, or you
could be doing a sequential scan of all rows followed by a sort, as shown in the following code
snippet, depending on whether you have a usable index on the field on which you want to get
top three rows:
mydb=# create table events(id serial);
NOTICE: CREATE TABLE will create implicit sequence "events_id_seq"
for serial column "events.id"
CREATE TABLE
mydb=# insert into events select generate_series(1,1000000);
INSERT 0 1000000
mydb=# explain analyse SELECT * FROM EVENTS ORDER BY ID DESC LIMIT 3;
QUERY PLAN
----------------------------------------------------------------------
Limit (cost=25500.67..25500.68 rows=3 width=4) \
(actual time=3143.493..3143.502 rows=3 loops=1)
-> Sort (cost=25500.67..27853.87 rows=941280 width=4)
(actual time=3143.488..3143.490 rows=3 loops=1)
Sort Key: id
Sort Method: top-N heapsort Memory: 17kB
-> Seq Scan on events
(cost=0.00..13334.80 rows=941280 width=4)
(actual time=0.105..1534.418 rows=1000000
loops=1)
Total runtime: 3143.584 ms
(6 rows)
 
Search WWH ::




Custom Search