We, like other Postgres DBAs worldwide, have been waiting for the 9.2 release for some time, specifically for the index-only scan feature, which will help reduce I/O by preventing unnecessary access to heap data if you only need data from the index.
Besides 9.2 is still in development, it is possible to download a version for testing at https://www.postgresql.org/download/snapshots/ . It’s important to note that it doesn’t add new behaviours, but improves the way that indexes are used.
How can we test this feature? We created a ‘big’ table starting with 10 million+ records with random values.
Extract a few elements, using a where clause.
Use aggregations.
Use partitioning plus index only scans.
When is this feature most useful?:
When you select only the columns that are specified in the index definition, including those which are at the condition part of the query.
If a vacuum was executed previously. Thus happens because the scan can skip the “heap fetch” if the TID references a heap [table] page on which all tuples are known visible to everybody (src/backend/executor/nodeIndexonlyscan.c).
So, the main table is:
CREATE TABLE lot_of_values AS SELECT i, clock_timestamp() t1, random() r1, random() r2, random() r3, clock_timestamp() + (round(random()*1000)::text || ‘ days’)::interval d1 from generate_series(1,10000000) i(i);
ALTER TABLE lot_of_values ADD PRIMARY KEY(i);
CREATE INDEX CONCURRENTLY ON lot_of_values (d1);
Something interesting: due to some improvements in write performance, we realized that 9.2 demonstrated better timing compared with 9.1.3 (~200k in 9.1, ~170k ms on 9.2). The index creation was slightly better on 9.2.
The table will contain data like this:
stuff=# \x
Expanded display is on.
stuff=# select * from lot_of_values limit 1;
-[ RECORD 1 ]———————
i | 1
t1 | 2012-04-18 08:37:14.426624+00
r1 | 0.571268450468779
r2 | 0.222371176816523
r3 | 0.72282966086641
d1 | 2012-08-17 08:37:14.426713+00
Ok, let’s start with some examples. As we previously explained, we need to specify columns that are only in the index. You can’t use columns from 2 different indexes. The next example is a clear fail:
stuff=# explain select i, d1 from lot_of_values where round(r1*100) < 10;
QUERY PLAN
———————————————
Seq Scan on lot_of_values (cost=0.00..263496.00 rows=3333333 width=12)
Filter: (round((r1 * 100::double precision)) < 10::double precision)
(2 rows)
stuff=# set enable_seqscan=off;
SET
stuff=# explain select i, d1 from lot_of_values where round(r1*100) < 10;
QUERY PLAN
———————————————
Seq Scan on lot_of_values (cost=10000000000.00..10000263496.00 rows=3333333 width=12)
Filter: (round((r1 * 100::double precision)) < 10::double precision) (2 rows) We don’t have indexes at r1 and it isn’t part of the index! The next example is another fail, using a column that is defined in another index or directly not defined in any index: stuff=# explain select i, d1 from lot_of_values where i between 12345 and 23456; QUERY PLAN ——————————————— Index Scan using lot_of_values_pkey on lot_of_values (cost=0.00..450.83 rows=11590 width=12) Index Cond: ((i >= 12345) AND (i <= 23456)) (2 rows) The next example is the correct case: stuff=# explain select i from lot_of_values where i between 12345 and 23456; QUERY PLAN ——————————————— Index Only Scan using lot_of_values_pkey on lot_of_values (cost=0.00..450.83 rows=11590 width=4) Index Cond: ((i >= 12345) AND (i <= 23456)) (2 rows) Also, we can try with a non-pk index: stuff=# explain select min(d1), max(d1) from lot_of_values ; QUERY PLAN ——————————————— Result (cost=6.93..6.94 rows=1 width=0) InitPlan 1 (returns $0) -> Limit (cost=0.00..3.46 rows=1 width=8)
-> Index Only Scan using lot_of_values_d1_idx on lot_of_values (cost=0.00..34634365.96 rows=10000000 width=8)
Index Cond: (d1 IS NOT NULL)
InitPlan 2 (returns $1)
-> Limit (cost=0.00..3.46 rows=1 width=8)
-> Index Only Scan Backward using lot_of_values_d1_idx on lot_of_values (cost=0.00..34634365.96 rows=10000000 width=8)
Index Cond: (d1 IS NOT NULL)
(9 rows) stuff=# explain select min(i), max(i), avg(i) from lot_of_values where i between 1234 and 2345;
QUERY PLAN
———————————————
Aggregate (cost=66.90..66.91 rows=1 width=4)
-> Index Only Scan using lot_of_values_pkey on lot_of_values (cost=0.00..58.21 rows=1159 width=4)
Index Cond: ((i >= 1234) AND (i <= 2345)) (3 rows) The aggregation cases are special. Index-only scans are not useful for count(*) without condition, because the index scan needs to check the visibility of the tuple, which makes it expensive. So, if you need to count the entire table, a sequential scan must be perfomed. Just for testing purposes, we’ll try to “turn off” the seqscan node, to force an Index-only scan: stuff=# explain (analyze true, costs true, buffers true, timing true, verbose true) select count(i) from lot_of_values; QUERY PLAN ——————————————— Aggregate (cost=213496.00..213496.01 rows=1 width=4) (actual time=57865.943..57865.946 rows=1 loops=1) Output: count(i) Buffers: shared hit=2380 read=86116 -> Seq Scan on public.lot_of_values (cost=0.00..188496.00 rows=10000000 width=4) (actual time=0.667..30219.806 rows=10000000 loops=1)
Output: i, t1, r1, r2, r3, d1
Buffers: shared hit=2380 read=86116
Total runtime: 57866.166 ms
(7 rows) stuff=# set enable_seqscan=off;
SET
stuff=# explain (analyze true, costs true, buffers true, timing true, verbose true) select count(i) from lot_of_values;
QUERY PLAN
———————————————
Aggregate (cost=351292.03..351292.04 rows=1 width=4) (actual time=64094.544..64094.547 rows=1 loops=1)
Output: count(i)
Buffers: shared read=110380
-> Index Only Scan using lot_of_values_pkey on public.lot_of_values (cost=0.00..326292.03 rows=10000000 width=4) (actual time=38.773..35825.761 rows=10000000 loops=1)
Output: i
Heap Fetches: 10000000
Buffers: shared read=110380
Total runtime: 64094.777 ms
(8 rows)
After a Vacuum, the plan changed and the cost drops to 262793.04.
For partitioning, as we expected, this works as well (in this example, we’ll use another table called ‘persons’ with ‘dni’ as PK column):
coches=# explain (analyze true, costs true, buffers true, timing true, verbose true) select dni from persons where dni between 2100111 and 2110222;
Result (cost=0.00..168.62 rows=22 width=8) (actual time=61.468..61.468 rows=0 loops=1)
Output: persons.dni
Buffers: shared hit=43 read=1
-> Append (cost=0.00..168.62 rows=22 width=8) (actual time=61.442..61.442 rows=0 loops=1)
Buffers: shared hit=43 read=1
-> Seq Scan onpersons (cost=0.00..0.00 rows=1 width=8) (actual time=0.156..0.156 rows=0 loops=1)
Output:persona.dni
Filter: (((persona.dni)::bigint >= 2100111) AND ((persona.dni)::bigint <= 2110222)) -> Index Only Scan using persons_200_pkey on persons_200 persons (cost=0.00..8.38 rows=1 width=8) (actual time=0.405..0.405 rows=0 loops=1)
Output:persona.dni
Index Cond: ((persons.dni >= 2100111) AND (persons.dni <= 2110222))
Heap Fetches: 0
Buffers: shared hit=5
…. LOT OF PARTITIONS ….
Total runtime: 11.045 ms
(114 rows)
Conclusion: this feature adds one of the most exciting performance improvements in Postgres. We see improvements at as much as 30% so far, and look forward to seeing how this scales as 9.2 becomes production-ready.
No comments