Postgres where query optimization is
There is a table in our database menus
that has 515502 rows. It has a column status
that is of type smallint
.
Currently, a simple counter query takes 700ms for a set of documents having a value status
like 3
.
explain analyze select count(id) from menus where status = 2;
Aggregate (cost=72973.71..72973.72 rows=1 width=4) (actual time=692.564..692.565 rows=1 loops=1)
-> Bitmap Heap Scan on menus (cost=2510.63..72638.80 rows=133962 width=4) (actual time=28.179..623.077 rows=135429 loops=1)
Recheck Cond: (status = 2)
Rows Removed by Index Recheck: 199654
-> Bitmap Index Scan on menus_status (cost=0.00..2477.14 rows=133962 width=0) (actual time=26.211..26.211 rows=135429 loops=1)
Index Cond: (status = 2)
Total runtime: 692.705 ms
(7 rows)
Some rows have a column value of 1 for which the query is very fast.
explain analyze select count(id) from menus where status = 4;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=7198.73..7198.74 rows=1 width=4) (actual time=24.926..24.926 rows=1 loops=1)
-> Bitmap Heap Scan on menus (cost=40.53..7193.53 rows=2079 width=4) (actual time=1.461..23.418 rows=2220 loops=1)
Recheck Cond: (status = 4)
-> Bitmap Index Scan on menus_status (cost=0.00..40.02 rows=2079 width=0) (actual time=0.858..0.858 rows=2220 loops=1)
Index Cond: (status = 4)
Total runtime: 25.089 ms
(6 rows)
I've noticed that the most general index btree
is the best indexing strategy for simple equality based queries. And gin
, and hash
were slower than btree
.
Any tips for making queries count
faster for any filter using an index?
I understand this is an entry-level question, so I apologize in advance for any mistakes I might have made.
source to share
Perhaps your table has more rows that have status = 2 than those that have status = 4, so the total table access time is longer for the second case. So there are too many lines for status = 2 to look at, so the bitmap goes into lossy mode to scan the bitmap heap and needs to be checked again after the operation. So, there are two things to consider: either your result is too large (but you can't do anything about it without reorganizing your tables, say with splitting), or your work_mem parameter is too small to keep the intermittent result. Try to increase its value if you can.
source to share