postgresql full text search query too slow

Question

There are two sql in a table, one of them is too slow, any one can tell me what's wrong and how can i optimize it?

There is their query explain blow: slow query explain

[---------edit---------]

new query explain

I have a gin index on field title and a b-tree index on field city; — YimingXie
– YimingXie, Commented Oct 27, 2017 at 7:59
For the future: please post execution plans as formatted text not screen shots. You can also upload them to explain.depesz.com if you think they are too big to be posted here. — user330315
– user330315, Commented Oct 27, 2017 at 11:47

Laurenz Albe · Accepted Answer · 2017-10-27 08:28:22Z

3

The bitmap index scan in the second query returns more rows because the condition is less selective.

As a consequence, the bitmap becomes too large to fit into work_mem and PostgreSQL continues by storing one bit per matching block rather than per matching row (those are the lossy entries mentioned).

So many more rows have to be checked during the bitmap heap scan, which causes the longer execution time.

Increase work_mem for better results.

answered Oct 27, 2017 at 8:28

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

YimingXie Over a year ago

Thank you, it worked! Do you know any other option can optimize the sql? I set work_mem=64M, it still need 300 ms...

YimingXie Over a year ago

I added a picture of new sql explain in question body, thanks~

Laurenz Albe Over a year ago

This is just how long it takes - I don't think it can be optimized any further. It has to crunch a lot of data.

YimingXie Over a year ago

OK，I really appriciate it.

Collectives™ on Stack Overflow

postgresql full text search query too slow

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related