CODEWITHSUNDEEP
postgresql
Dmitry
Dmitry

Reputation: 166

Postgres slow bitmap heap scan

I have tables messages phones with around 6M rows. And this query perfomance is very poor

SELECT t1.id, t2.number, t1.name, t1.gender
        FROM messages t1
        INNER JOIN phones t2 ON t2.id = t1.parent_id
        INNER JOIN regions t6 ON t6.id = t1.region_id
        WHERE t2.number IS NOT NULL AND t1.entity AND NOT t2.type AND t1.region_id = 50
        ORDER BY t1.id LIMIT 100

EXPLAIN ANALYZE result: http://explain.depesz.com/s/Pd6D

Btree indexes on all colums in where condition. Primary keys on all id colums, foreign keys in messages table on parent_id and region_id as well. Vacuum on all tables runned too.

But over 15sec on just 100 rows is too slow. What is wrong?

Postgres 9.3, ubuntu 13.10, cpu 2x 2.5Ghz, 4gb ram, pg config http://pastebin.com/mPVH1YJi

Upvotes: 3

Views: 879

Answers (1)

Kenaniah
Kenaniah

Reputation: 5201

This completely depends on your read vs. write load, but one solution may be to create composite indexes for the most common / general cases.

For example, BTREE(parent_id, region_id) to turn that heap scan into an index scan would be huge. Since you have dynamic queries, there might be a few other combinations of composite indexes you might need for other queries, but I would recommend using only two columns in your composite indexes for now (as each query is different). Note that BTREE(parent_id, region_id) can also be scanned when only parent_id is needed, so there is no need to carry a BTREE(parent_id) index as well.

Upvotes: 1

Related Questions