Why index not cached?

Question

After running the same query twice I expected data to be cached, but I see data is still read from disk:

->  Index Scan using product_pkey on product  (cost=0.42..3.51 rows=1 width=4) (actual time=0.132..0.132 rows=0 loops=25713)
                           Index Cond: (id = product_property_default.product)
                           Filter: ((lexeme @@ '''laptop'' | ''laptop-ul'''::tsquery) AND ((language)::oid = '20657'::oid))
                           Rows Removed by Filter: 1
                           Buffers: shared hit=152936 read=6111
                           I/O Timings: read=2602.604

Full plan: https://explain.dalibo.com/plan/oJUn

Query:

explain (analyze, buffers)
select distinct "product"."id"
from "product"
    inner join product_property on product_property.product = product.id
where "product"."lexeme" @@ plainto_tsquery('ro'::regconfig, 'laptop')
    and "product_property"."meaning" = 'B'
    and "product_property"."first" in (1.7179869184E10)
    and "product"."language" = 'ro'::regconfig;

My index size is 38MB:

\di+ product_pkey
                            List of relations
 Schema |     Name     | Type  |  Owner   |  Table  | Size  | Description
--------+--------------+-------+----------+---------+-------+-------------
 pse    | product_pkey | index | postgres | product | 38 MB |

Why total buffers count (x8KB) is larger than my index that is only 38MB?

Does it include database fetched rows?

Table size 755MB:

\dt+ product
                     List of relations
 Schema |  Name   | Type  |  Owner   |  Size  | Description
--------+---------+-------+----------+--------+-------------
 pse    | product | table | postgres | 755 MB |

Config:

• work_mem=64MB
• effective_cache_size=512MB
• shared_buffers=256MB

My database docker container uses low memory. From docker stats:

NAME                CPU %               MEM USAGE / LIMIT   MEM %               NET I/O             BLOCK I/O
db                  0.32%               75.36MiB / 512MiB   14.72%              359MB / 406MB       64.5GB / 14GB

Note that there are other docker services running on the same machine.

free -h:

              total        used        free      shared  buff/cache   available
Mem:           3.9G        2.9G        143M        274M        850M        488M
Swap:          4.0G        1.7G        2.3G

Using Postgres 12.4 (docker image postgres:12.4-alpine)

Answer 1

Your index scan is not an index-only scan. It has to hit the table for every row it finds in the index. And it looks like "lexeme" is large and has been TOASTed, so it has to read multiple multiple pages to reassemble the full value. All this disk access is shown in one node, so it is not obvious how much is for the index, and how much is for the table driven from the index.

Do you have a FTS index on lexeme, and it is just not getting used? Or is the index missing?