Search Postgresql Archives

Lots of read activity on index only scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



This is triggered by Hans-Jürgen Schönig's blog post on B-Tree vs. BRIN
indexes here: https://www.cybertec-postgresql.com/en/btree-vs-brin-2-options-for-indexing-in-postgresql-data-warehouses/

He creates a rather large table with a sorted and an unsorted column
plus two indexes:

CREATE TABLE t_demo (id_sorted int8, id_random int8);
INSERT INTO t_demo
       SELECT id, hashtext(id::text)
       FROM generate_series(1, 2000000000) AS id;
create index on t_demo (id_sorted);
create index on t_demo (id_random);

(Actually, his table is even larger, but I didn't have enough free space
on my test server and didn't want to extend the file system just for
this test. Anyway. That's large enough that the setup takes longer than
a cup of coffee, so maybe you want to go even lower.)

The table is now about 82 GB, and the indexes 42 GB and 40 GB,
respectively.

Then select about 10E6 values from each index:

wds=> select count(*) from t_demo where id_sorted between 10000000 and 20000000;
╔══════════╗
║  count   ║
╟──────────╢
║ 10000001 ║
╚══════════╝
(1 row)

Time: 349.186 ms

wds=> select count(*) from t_demo where id_random between 10000000 and 31475500;
╔══════════╗
║  count   ║
╟──────────╢
║ 10000028 ║
╚══════════╝
(1 row)

Time: 1296.489 ms (00:01.296)

The second is a bit slower but (on this server with lots of RAM) not
that much. However, if you look at the EXPLAIN output, the difference is
striking (wide output, sorry):

wds=> explain (analyze, buffers) select count(*) from t_demo where id_sorted between 10000000 and 20000000;
╔════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                                                                   QUERY PLAN                                                                                   ║
╟────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╢
║ Finalize Aggregate  (cost=203279.97..203279.98 rows=1 width=8) (actual time=545.388..559.523 rows=1 loops=1)                                                                   ║
║   Buffers: shared hit=27336                                                                                                                                                    ║
║   ->  Gather  (cost=203279.76..203279.97 rows=2 width=8) (actual time=545.201..559.504 rows=3 loops=1)                                                                         ║
║         Workers Planned: 2                                                                                                                                                     ║
║         Workers Launched: 2                                                                                                                                                    ║
║         Buffers: shared hit=27336                                                                                                                                              ║
║         ->  Partial Aggregate  (cost=202279.76..202279.77 rows=1 width=8) (actual time=508.272..508.274 rows=1 loops=3)                                                        ║
║               Buffers: shared hit=27336                                                                                                                                        ║
║               ->  Parallel Index Only Scan using t_demo_id_sorted_idx on t_demo  (cost=0.58..191374.26 rows=4362197 width=0) (actual time=0.075..352.469 rows=3333334 loops=3) ║
║                     Index Cond: ((id_sorted >= 10000000) AND (id_sorted <= 20000000))                                                                                          ║
║                     Heap Fetches: 0                                                                                                                                            ║
║                     Buffers: shared hit=27336                                                                                                                                  ║
║ Planning:                                                                                                                                                                      ║
║   Buffers: shared hit=5                                                                                                                                                        ║
║ Planning Time: 0.255 ms                                                                                                                                                        ║
║ JIT:                                                                                                                                                                           ║
║   Functions: 11                                                                                                                                                                ║
║   Options: Inlining false, Optimization false, Expressions true, Deforming true                                                                                                ║
║   Timing: Generation 1.217 ms, Inlining 0.000 ms, Optimization 0.762 ms, Emission 12.777 ms, Total 14.756 ms                                                                   ║
║ Execution Time: 560.422 ms                                                                                                                                                     ║
╚════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

wds=> explain (analyze, buffers) select count(*) from t_demo where id_random between 10000000 and 31475500;
╔═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                                                                   QUERY PLAN                                                                                    ║
╟─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╢
║ Finalize Aggregate  (cost=173323.64..173323.65 rows=1 width=8) (actual time=1513.163..1520.166 rows=1 loops=1)                                                                  ║
║   Buffers: shared hit=9995216                                                                                                                                                   ║
║   ->  Gather  (cost=173323.42..173323.63 rows=2 width=8) (actual time=1513.017..1520.154 rows=3 loops=1)                                                                        ║
║         Workers Planned: 2                                                                                                                                                      ║
║         Workers Launched: 2                                                                                                                                                     ║
║         Buffers: shared hit=9995216                                                                                                                                             ║
║         ->  Partial Aggregate  (cost=172323.42..172323.43 rows=1 width=8) (actual time=1492.693..1492.694 rows=1 loops=3)                                                       ║
║               Buffers: shared hit=9995216                                                                                                                                       ║
║               ->  Parallel Index Only Scan using t_demo_id_random_idx on t_demo  (cost=0.58..162927.68 rows=3758297 width=0) (actual time=0.058..1338.110 rows=3333343 loops=3) ║
║                     Index Cond: ((id_random >= 10000000) AND (id_random <= 31475500))                                                                                           ║
║                     Heap Fetches: 0                                                                                                                                             ║
║                     Buffers: shared hit=9995216                                                                                                                                 ║
║ Planning Time: 0.186 ms                                                                                                                                                         ║
║ JIT:                                                                                                                                                                            ║
║   Functions: 11                                                                                                                                                                 ║
║   Options: Inlining false, Optimization false, Expressions true, Deforming true                                                                                                 ║
║   Timing: Generation 1.204 ms, Inlining 0.000 ms, Optimization 0.634 ms, Emission 11.510 ms, Total 13.348 ms                                                                    ║
║ Execution Time: 1521.068 ms                                                                                                                                                     ║
╚═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Both do a parallel index only scan. Both perform 0 heap fetches.
But one reads 27336 buffers (or about 22 bytes per index entry, which
sounds reasonable) while the other reads 9995216 buffers (or almost one
full buffer per row). Why? The entries should be dense in the index in
both cases and since it's an index only scan (and explain says there
were 0 heap fetches) I would not expect extra accesses. Where do these
buffer reads come from?

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@xxxxxx         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment: signature.asc
Description: PGP signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux