product_lexeme_idx
is a gin index on lexeme
column.
product_pkey
is a primary key on id
integer column.
Query that doesn't use lexeme idx:
select "product"."id"
from "product"
where (
"product"."lexeme" @@ plainto_tsquery('ro'::regconfig, 'laptop')
and "product"."language" = 'ro'::regconfig
and exists (
select 1 as "one"
from "product_property"
where (
"product_property"."product" = "product"."id"
and "product_property"."meaning" = 'B'
and "product_property"."first" = 1.7179869184E10
)
)
);
Plan: https://explain.dalibo.com/plan/CnI#plan
Total blocks needed: 62.799
Forcing the query to use lexeme index by a materialized CTE results in better performance:
with product as materialized(
select "product"."id"
from "product"
where "product"."lexeme" @@ plainto_tsquery('ro'::regconfig, 'laptop')
and "product"."language" = 'ro'::regconfig
)
select "product"."id"
from "product"
where (
exists (
select 1 as "one"
from "product_property"
where (
"product_property"."product" = "product"."id"
and "product_property"."meaning" = 'B'
and "product_property"."first" = 1.7179869184E10
)
)
);
Plan: https://explain.dalibo.com/plan/91q#plan
Total blocks needed: 15.209 (4x lower)
The product_property
table is partitioned in ~83 partitions by meaning
column.
Note that running analyze on product
and product_property
tables doesn't change the plan.
Sizes:
product
has 460MBproduct_pkey
has 9MB Indexproduct_lexeme_idx
has 79MB
Using windows docker with wsl2. Both cases are run with cold cache by running the following commands:
docker-compose stop db
wsl -u root
sync; echo 3 > /proc/sys/vm/drop_caches
exit
docker-compose up -d db
All database memory is limited to 1GB by using .wslconfig
file with memory=1GB
, also the docker container is limited to 1GB.
My requirement is to optimize disk access with this limited memory.
Postgres config:
- work_mem=64MB
- shared_buffers=256MB
- effective_cache_size=768MB
- random_page_cost=2 (running on a SSD)
Postgres 12.4