1

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

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)

4
  • I've increased the statistics and used an ext stats to improve the aggregate from x15 to x2.5 (explain.dalibo.com/plan/B7d), but still nested loop is used with the same buffer count. How can I check ndistinct accuracy? Compare with select count distinct for that col?
    – cdalxndr
    Aug 7 '21 at 13:36
  • Total property 623k rows, product col n_distinct -0.155 ~ 99k actual 156k, meaning n_distinct 73 actual 125, first n_distinct 2215 actual 7400 (values with default up-to-date statistics).
    – cdalxndr
    Aug 7 '21 at 14:55
  • Are the ndistinct numbers you give for the entire table, or just for the partition actually being used?
    – jjanes
    Aug 8 '21 at 2:35
  • partition actually being used
    – cdalxndr
    Aug 8 '21 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.