Database Reference
In-Depth Information
mydb=# create index events_id_ndx on events(id);
CREATE INDEX
mydb=# explain analyse SELECT * FROM EVENTS ORDER BY ID DESC LIMIT 3;
QUERY PLAN
----------------------------------------------------------------------
Limit (cost=0.00..0.08 rows=3 width=4) (actual time=0.295..0.311
rows=3 loops=1)
-> Index Scan Backward using events_id_ndx on events
(cost=0.00..27717.34 rows=1000000 width=4) (actual time=0.289..0.295
rows=3 loops=1)
Total runtime: 0.364 ms
(3 rows)
This produces 10,000 times difference in query run time, even when all the data is in memory.
Too little of the data fits in the memory
If not enough of the data fits in shared buffers, lots of re-reading of the same data happens.
This manifests as a big change in any of the fields heap_blks_read , idx_blks_read ,
toast_blks_read in the pg_stat* view before and after the query is run.
It is somewhat normal to have a big difference before and after the first run of the query, as
some data may just not be accessed recently, but if you run the query immediately the second
time and any of the *_blks_read fields still changes a lot, you have this problem for sure.
If your shared_buffers is tuned properly, and you can't rewrite the query to do less block
I/O, you probably have to get a beefier computer.
The query returns too much data
Sometimes lazy programmers write a query that returns a lot more rows than needed. This
usually goes unnoticed when the data volumes are small, but can quickly become problematic
once more data appears in the database. For example, you have a picture database and an
application showing a list of pictures. If you are showing only 10 pictures at a time, you should
not request more than 10 from the database (or maybe 11 if you want to display the next link).
For thousands of pictures it makes sense to have a separate count(*) query for determining
the total number of pictures, and not select all pictures and count them in client. For high
performance websites, you would want to replace even the count query with a separately
maintained count in some table to further reduce work done at display time.
See also the recipe Reducing the number of rows returned .
 
Search WWH ::




Custom Search