04007 (64%) |
DB: 0.01952 (31%) | 200 OK
Using this information, you can find the database-hungry actions in a real-world
environment. Then, you can break down the queries that comprise that action and
profile each one at the database. This will give you hints about how you might better
design your application or database structure to avoid the bottlenecks.
Examining the query plan
Before any SQL query is executed, it must be compiled and planned. The planning
process decides on the steps that are taken to answer the query. This includes the
selection of indexes and the series of scans, filters, merges, sorts, and other low-level
operations that take place to generate the requested data.
All major DBMSs provide powerful tools that show how the query planner has
decided to execute a query. In PostgreSQL, the EXPLAIN keyword shows the query
plan corresponding to the requested query. The EXPLAIN ANALYZE query syntax is a
variant that actually executes the query and returns actual cost values. Here is an
example of the first variant:
172 | Chapter 6: Performance
listings_development=> EXPLAIN SELECT min(listing_id) as listing_id,
count(listing_id) as cluster_size, cluster_id, level
FROM cluster_ancestors
WHERE listing_id IN (16466,18320,17948)
GROUP BY cluster_id, level;
QUERY PLAN
------------------------------------------------------------------------------------
HashAggregate (cost=199.
Pages:
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267