Subject: Re: index-only count(*) for indexes supporting bitmap scans
To: Tom Lane <tgl@sss.pgh.pa.us>
References: <239a8955-c0fc-f506-026d-c837e86c827b@postgrespro.ru>
 <87ziflvryn.fsf@news-spur.riddles.org.uk> <8437.1491998661@sss.pgh.pa.us>
 <ca192322-29fd-9e60-3766-fe5e69d1f9af@postgrespro.ru>
 <31289.1492007070@sss.pgh.pa.us>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
From: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
Message-ID: <2b547a5d-83ad-5d9d-923e-4b39b8bf0fe1@postgrespro.ru>
Date: Wed, 12 Apr 2017 20:10:36 +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: <31289.1492007070@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 12.04.2017 17:24, Tom Lane wrote:
> TBH, I'm not sure you need to do any of that work.  Have you got evidence
> that the planner will fail to choose the right plan regardless? I'm
> particularly unconvinced that choose_bitmap_and is a critical problem,
> because once you're into having to AND multiple indexes, you're talking
> about an expensive query anyhow.
The most expensive part would probably be accessing the heap in the 
bitmap heap scan. It may be worth trying to choose an index path that 
checks all the restriction and therefore allows us to skip this heap 
access. This path might not be the cheapest one, though. The standard 
AND selection procedure would have discarded it based on cost.
I've seen this happen on the regression database. Somehow I can't seem 
to reproduce it on my earlier full-text search example.

An example:

regression=# explain select count(*) from tenk1 where hundred < 90 and 
thousand < 31;
                                         QUERY PLAN
-------------------------------------------------------------------------------------------
  Bitmap Count on tenk1  (cost=182.69..185.56 rows=1 width=8)
    Recheck Cond: ((thousand < 31) AND (hundred < 90))
    ->  BitmapAnd  (cost=182.69..182.69 rows=287 width=0)
          ->  Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..6.68 
rows=319 width=0)
                Index Cond: (thousand < 31)
          ->  Bitmap Index Scan on tenk1_hundred (cost=0.00..175.62 
rows=8978 width=0)
                Index Cond: (hundred < 90)
(7 rows)

regression=# set enable_bitmapcount to off;
SET
regression=# explain select count(*) from tenk1 where hundred < 90 and 
thousand < 31;
                                         QUERY PLAN
-------------------------------------------------------------------------------------------
  Aggregate  (cost=375.34..375.35 rows=1 width=8)
    ->  Bitmap Heap Scan on tenk1  (cost=6.75..374.62 rows=287 width=0)
          Recheck Cond: (thousand < 31)
          Filter: (hundred < 90)
          ->  Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..6.68 
rows=319 width=0)
                Index Cond: (thousand < 31)
(6 rows)

-- 

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers