Bitmap Heap Scan takes a lot of time
Hello,
I have a big table (around 3 600 000 entries) which stores which user
has seen which subjects in a forum.
This query is executed every time a user connects to the forum:
SELECT flg_rid
FROM prj_frm_flg
WHERE flg_mid=3 AND NOT flg_fav AND NOT flg_notif AND NOT flg_post
ORDER BY flg_rid DESC
OFFSET 999
This query very often takes a lot of time to do its job (40-60s).
I have a primary key on flg_mid and flg_sid (simultaneously) and
indexes on flg_notif, flg_post and flg_fav.
This is the result of an EXPLAIN:
"Limit (cost=453.48..453.48 rows=1 width=4)"
" -> Sort (cost=453.25..453.48 rows=94 width=4)"
" Sort Key: flg_rid"
" -> Bitmap Heap Scan on prj_frm_flg (cost=5.55..450.17
rows=94 width=4)"
" Recheck Cond: (flg_mid = 3)"
" Filter: ((NOT flg_fav) AND (NOT flg_notif) AND (NOT
flg_post))"
" -> Bitmap Index Scan on prj_frm_flg_pkey
(cost=0.00..5.53 rows=117 width=0)"
" Index Cond: (flg_mid = 3)"
I suppose that the problem comes from the Bitmap Heap Scan which costs
a lot, but I can't be totally sure.
Any idea on where I should be investigating ?
Thanks a lot.
Mat.
<mateo21@siteduzero.com> writes:
This is the result of an EXPLAIN:
...
I suppose that the problem comes from the Bitmap Heap Scan which costs
a lot, but I can't be totally sure.Any idea on where I should be investigating ?
Try posting an EXPLAIN ANALYZE which will actually run the query and include
information to compare against the estimates.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!
On 23 avr, 14:12, st...@enterprisedb.com (Gregory Stark) wrote:
<mate...@siteduzero.com> writes:
This is the result of an EXPLAIN:
...
I suppose that the problem comes from the Bitmap Heap Scan which costs
a lot, but I can't be totally sure.Any idea on where I should be investigating ?
Try posting an EXPLAIN ANALYZE which will actually run the query and include
information to compare against the estimates.--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!--
Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
Thanks for your answer. Here is the EXPLAIN ANALYZE of this query:
Limit (cost=392.43..392.43 rows=1 width=4) (actual
time=689.016..689.017 rows=1 loops=1)"
-> Sort (cost=392.22..392.43 rows=83 width=4) (actual
time=688.552..688.811 rows=1000 loops=1)"
Sort Key: flg_rid"
-> Index Scan using prj_frm_flg_pkey on prj_frm_flg
(cost=0.00..389.58 rows=83 width=4) (actual time=0.186..687.578
rows=1000 loops=1)"
Index Cond: (flg_mid = 3)"
Filter: ((NOT flg_fav) AND (NOT flg_notif) AND (NOT
flg_post))"
Total runtime: 689.092 ms"