Advanced Rails - Building Industrial-Strength Web Apps in Record Time

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.48..199.59 rows=7 width=12)

-> Bitmap Heap Scan on cluster_ancestors (cost=6.22..198.86 rows=62 width=12)

Recheck Cond: ((listing_id = 16466) OR (listing_id = 18320) OR

(listing_id = 17948))

-> BitmapOr (cost=6.22..6.22 rows=62 width=0)

-> Bitmap Index Scan on cluster_ancestors_listing_id_and_level_idx

(cost=0.00..2.07 rows=21 width=0)

Index Cond: (listing_id = 16466)

-> Bitmap Index Scan on cluster_ancestors_listing_id_and_level_idx

(cost=0.00..2.07 rows=21 width=0)

Index Cond: (listing_id = 18320)

-> Bitmap Index Scan on cluster_ancestors_listing_id_and_level_idx

(cost=0.00..2.07 rows=21 width=0)

Index Cond: (listing_id = 17948)

(10 rows)

The different indentation levels form a tree showing how the data is retrieved from
the table and aggregated into a result. Each operation shows a cost estimate, which is
roughly related to the number of disk blocks that must be accessed for that opera-
tion. The first cost number is the estimated cost to calculate the first row; the second
number is the cost to calculate all of the rows. Each step also lists the estimated num-
ber of rows returned from that step, as well as the average width, in bytes, of each
row. The preceding tree represents the following execution plan (in chronological
order):

1. Thecluster_ancestors_listing_id_and_levelindex is scanned for each of the
   threelisting_idvalues in theWHEREclause. This is an inexpensive scan because
   it is restricted based on a subset of the index. The query planner estimates that
   each scan will return 21 rows.
   The index scan is a bitmap scan, so instead of returning the rows themselves,
   this step just generates a list of the matching rows to be retrieved later. The
   width is listed as 0 bytes per row because the actual rows are not returned.


2. The BitmapOr step takes the bitwiseORof the three bitmaps returned from the
   lower steps, effectively returning their union. It returns a bitmap itself, so the width
   is still 0.


3. The Bitmap Heap Scan uses the bitmap to retrieve the actual rows from the
   table. The number of rows stays the same, but the width is now 12.


4. A HashAggregate performs the grouping and processes the aggregate functions—
   themin( ) andcount( ) functions in theSELECT clause.