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
)