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)

• Gerard H. Pille: Table and index DDL would be helpful.
• Colin 't Hart: You do realise that Postgres uses the OS filesystem cache extensively?
• cdalxndr: @Colin'tHart I see 850M cached but my index is only 38MB. Also the total buffer count seems high. Do the buffers read number include rows read from actual table?
• mustaccio: Though this particual index may be relatively small, your query has to read seven other indexes and a table, which all compete for your puny 256 MB of shared buffers.

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?

• cdalxndr: I have a GIN index on lexeme, size 151M. Also my lexeme column has weights.
• jjanes: It might be faster to use the lexeme index than to do what it is currently doing, but that is not certain. If you want to dig into that, you should probably post a new question focusing on that topic. You can probably get rid of the joins on offer, stock, and stock_template, as none of them seem to be relevant to the performance issue and just complicate things, but please show the text of the rest of the query.
• cdalxndr: updated question with simplified query & plan
• cdalxndr: Did a simple test and the lexeme index is used when I do a simple query with @@ without any joins. Don't know why the planner decides not to use it in my original query.
• jjanes: @cdalxndr How fast was it? How many rows did it expect to find? How many did it actually find? Presumably the planner didn't use it because it thought it would be slower to do so.