Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cyM0w-0002aX-5a for pgsql-hackers@arkaria.postgresql.org; Wed, 12 Apr 2017 17:25:06 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cyM0v-0001X2-Oe for pgsql-hackers@arkaria.postgresql.org; Wed, 12 Apr 2017 17:25:05 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cyLzM-0004qG-Sv for pgsql-hackers@postgresql.org; Wed, 12 Apr 2017 17:23:29 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by makus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cyLzJ-0001aO-3n for pgsql-hackers@postgresql.org; Wed, 12 Apr 2017 17:23:27 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 1CCC421C295B; Wed, 12 Apr 2017 20:23:23 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru Subject: Re: index-only count(*) for indexes supporting bitmap scans DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1492017802; bh=qL+VaicKaEi2DdENF7qMlLyMeT0vxyle4khILNg5T2Y=; h=To:References:Cc:From:Date:In-Reply-To; b=BDTOGvy0wVnP4+Qn1HfmIWeN2gsfV2aUEByWepgbufuGtr7b66U8cVw2MbU3Zbiab vvFVRywcrKrE8oEaGQikvcErmGu4sQkCCxTjQPn/0g1DF7RLtrOZ2QAcsXEDjGn9GU sn5k/Y7xvKecOu9wBM+NLIUsn+RylGC1yR4P3o7M= To: Alexander Korotkov References: <239a8955-c0fc-f506-026d-c837e86c827b@postgrespro.ru> Cc: PostgreSQL-development From: Alexander Kuzmenkov Message-ID: <251401bb-6f53-b957-4128-578ac22e8acf@postgrespro.ru> Date: Wed, 12 Apr 2017 20:23:22 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A43FA25601CF5033D3530375" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is a multi-part message in MIME format. --------------A43FA25601CF5033D3530375 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 12.04.2017 12:29, Alexander Korotkov wrote: > That's a cool feature for FTS users! Please, register this patch to > the next commitfest. I've added this to the 2017-07 commitfest: https://commitfest.postgresql.org/14/1117/ > Also, what is planning overhead of this patch? That's worth > testing too. > The planning overhead is about 0.1 - 0.07 ms on the samples from my first letter. > Another thing catch my eye. The estimated number of rows in Bitmap > Count node is the same as in Bitmap Index Scan node. Should it be 1 > because it always returns single row? You're right, I'll fix this in the next version of the patch. > Does this limitation cause a performance drawback? When bitmap > index scan returns all rechecks, alternative to Bitmap Count is > still Aggregate + Bitmap Heap Scan. Thus, I think Bitmap Count > would have the same performance or even slightly faster. That's > worth testing. > Bitmap heap scan can indeed be faster, because it prefetches heap pages, and can be run in parallel. When the table data is not cached, the difference is not big on my machine. It could probably be significant if I used a storage that supported parallel reads. When the data is cached in memory, the parallel bitmap heap scan can be significantly faster. We could use the standard bitmap heap scan node with some tweaks, as discussed in the other subthread, to avoid this regression. Here are some test queries: --- not cached ------------------------------------------------------------------------------------------------------------------- test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' ); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Bitmap Count on pglist (cost=542.65..1087.68 rows=54503 width=8) (actual time=30264.174..30264.177 rows=1 loops=1) Recheck Cond: (fts @@ to_tsquery('tom & lane'::text)) Rows Removed by Index Recheck: 270853 Heap Fetches: 66138 Heap Blocks: exact=39854 lossy=66138 -> Bitmap Index Scan on idx_pglist_fts (cost=0.00..529.02 rows=54503 width=0) (actual time=525.341..525.341 rows=222813 loops=1) Index Cond: (fts @@ to_tsquery('tom & lane'::text)) Planning time: 128.238 ms Execution time: 30264.299 ms (9 rows) test1=# set enable_bitmapcount to off; SET test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' ); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=119989.73..119989.74 rows=1 width=8) (actual time=31699.829..31699.830 rows=1 loops=1) -> Gather (cost=119989.52..119989.73 rows=2 width=8) (actual time=31698.699..31699.819 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=118989.52..118989.53 rows=1 width=8) (actual time=31689.289..31689.290 rows=1 loops=3) -> Parallel Bitmap Heap Scan on pglist (cost=542.65..118932.74 rows=22710 width=0) (actual time=608.532..31634.692 rows=74271 loops=3) Recheck Cond: (fts @@ to_tsquery('tom & lane'::text)) Rows Removed by Index Recheck: 90284 Heap Blocks: exact=13242 lossy=21960 -> Bitmap Index Scan on idx_pglist_fts (cost=0.00..529.02 rows=54503 width=0) (actual time=552.136..552.136 rows=222813 loops=1) Index Cond: (fts @@ to_tsquery('tom & lane'::text)) Planning time: 160.055 ms Execution time: 31724.468 ms (13 rows) ----- cached ------------------------------------------------------------------------------------------------------------------------------------- test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' ); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Finalize Aggregate (cost=119989.73..119989.74 rows=1 width=8) (actual time=1250.973..1250.973 rows=1 loops=1) -> Gather (cost=119989.52..119989.73 rows=2 width=8) (actual time=1250.588..1250.964 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=118989.52..118989.53 rows=1 width=8) (actual time=1246.144..1246.144 rows=1 loops=3) -> Parallel Bitmap Heap Scan on pglist (cost=542.65..118932.74 rows=22710 width=0) (actual time=82.781..1237.585 rows=74271 loops=3) Recheck Cond: (fts @@ to_tsquery('tom & lane'::text)) Rows Removed by Index Recheck: 90284 Heap Blocks: exact=13221 lossy=22217 -> Bitmap Index Scan on idx_pglist_fts (cost=0.00..529.02 rows=54503 width=0) (actual time=78.366..78.366 rows=222813 loops=1) Index Cond: (fts @@ to_tsquery('tom & lane'::text)) Planning time: 0.722 ms Execution time: 1256.028 ms (13 rows) test1=# set enable_bitmapcount to on; SET test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' ); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Bitmap Count on pglist (cost=542.65..1087.68 rows=54503 width=8) (actual time=2745.740..2745.742 rows=1 loops=1) Recheck Cond: (fts @@ to_tsquery('tom & lane'::text)) Rows Removed by Index Recheck: 270853 Heap Fetches: 66138 Heap Blocks: exact=39854 lossy=66138 -> Bitmap Index Scan on idx_pglist_fts (cost=0.00..529.02 rows=54503 width=0) (actual time=85.572..85.572 rows=222813 loops=1) Index Cond: (fts @@ to_tsquery('tom & lane'::text)) Planning time: 0.701 ms Execution time: 2745.800 ms (9 rows) -- Alexander Kuzmenkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------A43FA25601CF5033D3530375 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 12.04.2017 12:29, Alexander Korotkov wrote:

That's a cool feature for FTS users! Please, register this patch to the next commitfest.
I've added this to the 2017-07 commitfest: https://commitfest.postgresql.org/14/1117/

Also, what is planning overhead of this patch?  That's worth testing too.
The planning overhead is about 0.1 - 0.07 ms on the samples from my first letter.

Another thing catch my eye.  The estimated number of rows in Bitmap Count node is the same as in Bitmap Index Scan node.  Should it be 1 because it always returns single row?
You're right, I'll fix this in the next version of the patch.

Does this limitation cause a performance drawback?  When bitmap index scan returns all rechecks, alternative to Bitmap Count is still Aggregate + Bitmap Heap Scan.  Thus, I think Bitmap Count would have the same performance or even slightly faster.  That's worth testing.
Bitmap heap scan can indeed be faster, because it prefetches heap pages, and can be run in parallel. When the table data is not cached, the difference is not big on my machine. It could probably be significant if I used a storage that supported parallel reads. When the data is cached in memory, the parallel bitmap heap scan can be significantly faster. 
We could use the standard bitmap heap scan node with some tweaks, as discussed in the other subthread, to avoid this regression.

Here are some test queries:

--- not cached -------------------------------------------------------------------------------------------------------------------
test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' );
                                                              QUERY PLAN                                                             
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Count on pglist  (cost=542.65..1087.68 rows=54503 width=8) (actual time=30264.174..30264.177 rows=1 loops=1)
   Recheck Cond: (fts @@ to_tsquery('tom & lane'::text))
   Rows Removed by Index Recheck: 270853
   Heap Fetches: 66138
   Heap Blocks: exact=39854 lossy=66138
   ->  Bitmap Index Scan on idx_pglist_fts  (cost=0.00..529.02 rows=54503 width=0) (actual time=525.341..525.341 rows=222813 loops=1)
         Index Cond: (fts @@ to_tsquery('tom & lane'::text))
 Planning time: 128.238 ms
 Execution time: 30264.299 ms
(9 rows)

test1=# set enable_bitmapcount to off;
SET
test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' );
                                                                       QUERY PLAN                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=119989.73..119989.74 rows=1 width=8) (actual time=31699.829..31699.830 rows=1 loops=1)
   ->  Gather  (cost=119989.52..119989.73 rows=2 width=8) (actual time=31698.699..31699.819 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=118989.52..118989.53 rows=1 width=8) (actual time=31689.289..31689.290 rows=1 loops=3)
               ->  Parallel Bitmap Heap Scan on pglist  (cost=542.65..118932.74 rows=22710 width=0) (actual time=608.532..31634.692 rows=74271 loops=3)
                     Recheck Cond: (fts @@ to_tsquery('tom & lane'::text))
                     Rows Removed by Index Recheck: 90284
                     Heap Blocks: exact=13242 lossy=21960
                     ->  Bitmap Index Scan on idx_pglist_fts  (cost=0.00..529.02 rows=54503 width=0) (actual time=552.136..552.136 rows=222813 loops=1)
                           Index Cond: (fts @@ to_tsquery('tom & lane'::text))
 Planning time: 160.055 ms
 Execution time: 31724.468 ms
(13 rows)


----- cached -------------------------------------------------------------------------------------------------------------------------------------
test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' );
                                                                      QUERY PLAN                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=119989.73..119989.74 rows=1 width=8) (actual time=1250.973..1250.973 rows=1 loops=1)
   ->  Gather  (cost=119989.52..119989.73 rows=2 width=8) (actual time=1250.588..1250.964 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=118989.52..118989.53 rows=1 width=8) (actual time=1246.144..1246.144 rows=1 loops=3)
               ->  Parallel Bitmap Heap Scan on pglist  (cost=542.65..118932.74 rows=22710 width=0) (actual time=82.781..1237.585 rows=74271 loops=3)
                     Recheck Cond: (fts @@ to_tsquery('tom & lane'::text))
                     Rows Removed by Index Recheck: 90284
                     Heap Blocks: exact=13221 lossy=22217
                     ->  Bitmap Index Scan on idx_pglist_fts  (cost=0.00..529.02 rows=54503 width=0) (actual time=78.366..78.366 rows=222813 loops=1)
                           Index Cond: (fts @@ to_tsquery('tom & lane'::text))
 Planning time: 0.722 ms
 Execution time: 1256.028 ms
(13 rows)

test1=# set enable_bitmapcount to on;
SET
test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' );
                                                             QUERY PLAN                                                            
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Count on pglist  (cost=542.65..1087.68 rows=54503 width=8) (actual time=2745.740..2745.742 rows=1 loops=1)
   Recheck Cond: (fts @@ to_tsquery('tom & lane'::text))
   Rows Removed by Index Recheck: 270853
   Heap Fetches: 66138
   Heap Blocks: exact=39854 lossy=66138
   ->  Bitmap Index Scan on idx_pglist_fts  (cost=0.00..529.02 rows=54503 width=0) (actual time=85.572..85.572 rows=222813 loops=1)
         Index Cond: (fts @@ to_tsquery('tom & lane'::text))
 Planning time: 0.701 ms
 Execution time: 2745.800 ms
(9 rows)

-- 
Alexander Kuzmenkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--------------A43FA25601CF5033D3530375--