Thread: 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"