Why lexeme index not used?

Question

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 460MB
• product_pkey has 9MB Index
• product_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

Answer 1

The number of rows coming out of the aggregate is underestimated by 15 fold. That makes the nested loop it drives look about 15 times cheaper than it is.

The main problem there is that it thinks the rows it finds with the specified "meaning" and "first" are going to come from the same small pool of "property", but they don't. Your changes to the stats have increased the costs of the actually slower plan by quite a bit, but still not enough to make the actually faster plan be preferred. If you could get the estimate spot on, then it should be enough, but I don't know how to go further in improving that.

Another thing you can do is make the @@ appear more expensive. As a superuser, you can run:

alter FUNCTION pg_catalog.ts_match_vq cost 1000;

This will make using @@ in the filter in the nested loop look much more expensive (while using an index over it should not change much). Which might be a fair thing to do, My theory is that detoasting the tsvector column to use it in @@ is generating a lot of slow random IO.

However, you might have other uses of @@ in your code that don't have this problem, and you can't change the costs for just one usage. Also, "1000" might not be the best cost to use. You might want to experiment with how low you can make it while still changing the query plan (assuming it works in the first place)