From: Alexander Kuzmenkov <a.kuzmenkov@postgrespro.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: index-only count(*) for indexes supporting bitmap scans
Date: Wed, 12 Apr 2017 20:10:36 +0300
Message-ID: <2b547a5d-83ad-5d9d-923e-4b39b8bf0fe1@postgrespro.ru> (raw)
In-Reply-To: <31289.1492007070@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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.kuzmenkov@postgrespro.ru, tgl@sss.pgh.pa.us, andrew@tao11.riddles.org.uk
Subject: Re: index-only count(*) for indexes supporting bitmap scans
In-Reply-To: <2b547a5d-83ad-5d9d-923e-4b39b8bf0fe1@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox