From monalee@sungraceinfotech.co.in Wed Nov 13 00:25:13 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VgOGf-0001vf-Ff for pgsql-performance@arkaria.postgresql.org; Wed, 13 Nov 2013 00:25:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VgOGe-0003aE-Tb for pgsql-performance@arkaria.postgresql.org; Wed, 13 Nov 2013 00:25:12 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VfPVL-00055G-3w for pgsql-performance@postgresql.org; Sun, 10 Nov 2013 07:32:19 +0000 Received: from sam.nabble.com ([216.139.236.26]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VfPVH-0007VE-8p for pgsql-performance@postgresql.org; Sun, 10 Nov 2013 07:32:18 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1VfPVG-0001ul-90 for pgsql-performance@postgresql.org; Sat, 09 Nov 2013 23:32:14 -0800 Date: Sat, 9 Nov 2013 23:32:14 -0800 (PST) From: monalee_dba To: pgsql-performance@postgresql.org Message-ID: <1384068734255-5777632.post@n5.nabble.com> Subject: BitMap Heap Scan & BitMap Index Scan MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_103289_32422776.1384068734257" X-Pg-Spam-Score: 1.1 (+) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org ------=_Part_103289_32422776.1384068734257 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I would like to know, What is BitMap Heap Scan & BitMap Index Scan?When I use EXPLAIN for query, which has LEFT JOIN with 4 different table thensome time query planner uses Bitmap Heap Scan and some time Bitmap Index Scan? -- View this message in context: http://postgresql.1045698.n5.nabble.com/BitMap-Heap-Scan-BitMap-Index-Scan-tp5777632.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. ------=_Part_103289_32422776.1384068734257 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I would like to know, What is BitMap Heap Scan & BitMap Index Scan? When I use EXPLAIN for query, which has LEFT JOIN with 4 different table then some time query planner uses Bitmap Heap Scan and some time Bitmap Index Scan?

View this message in context: BitMap Heap Scan & BitMap Index Scan
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
------=_Part_103289_32422776.1384068734257-- From michael.paquier@gmail.com Wed Nov 13 01:25:39 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VgPD8-0004Uu-Nq for pgsql-performance@arkaria.postgresql.org; Wed, 13 Nov 2013 01:25:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VgPD8-0008W8-6k for pgsql-performance@arkaria.postgresql.org; Wed, 13 Nov 2013 01:25:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VgPD5-0008Vz-UD for pgsql-performance@postgresql.org; Wed, 13 Nov 2013 01:25:36 +0000 Received: from mail-wi0-x22f.google.com ([2a00:1450:400c:c05::22f]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VgPD1-0007K5-O3 for pgsql-performance@postgresql.org; Wed, 13 Nov 2013 01:25:35 +0000 Received: by mail-wi0-f175.google.com with SMTP id hm11so4544969wib.2 for ; Tue, 12 Nov 2013 17:25:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=LrWptQ+K9AzN+2nqojZ4WBlmDEB4tL5UNH6obbspvDc=; b=eL8DP6FfxgAXK3IY05wpk7AST726bSez1O/cf47kIkpG8GCME5vuGNm0TqS30f78pD 3ID++EjwDY9LJ/MgcIZQMLpEmE01x6eCtKtWiM+IlT8lxDpQB2CfvUKA0IZtpxqec+Au nxMAlw9yAiqPr7/HaBklSykpMNxtea4Elb5aMYS75w2MS0e2EuNowUoFOIuEtsC/hQP0 n3gFPH48IlPwBpZBC5b0YdzQkEnnM1bYYUuPNz/3re4myc/2+HQu+ZIyWsH4jhTTieTh gyYE0wPV1Q/LKlrH4RyzCIAyJtQYAIkBbRN/vvaTEnn6hEPmHIKifwiZ5AZHlexz5spO fTVQ== MIME-Version: 1.0 X-Received: by 10.180.99.73 with SMTP id eo9mr11582115wib.50.1384305930212; Tue, 12 Nov 2013 17:25:30 -0800 (PST) Received: by 10.180.10.195 with HTTP; Tue, 12 Nov 2013 17:25:30 -0800 (PST) In-Reply-To: <1384068734255-5777632.post@n5.nabble.com> References: <1384068734255-5777632.post@n5.nabble.com> Date: Wed, 13 Nov 2013 10:25:30 +0900 Message-ID: Subject: Re: BitMap Heap Scan & BitMap Index Scan From: Michael Paquier To: monalee_dba Cc: PostgreSQL mailing lists Content-Type: text/plain; charset=ISO-8859-1 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org http://www.postgresql.org/docs/9.3/static/using-explain.html On Sun, Nov 10, 2013 at 4:32 PM, monalee_dba wrote: > I would like to know, What is BitMap Heap Scan & BitMap Index Scan? When I > use EXPLAIN for query, which has LEFT JOIN with 4 different table then some > time query planner uses Bitmap Heap Scan and some time Bitmap Index Scan? The way to go here would be to have a look at the documentation first: http://www.postgresql.org/docs/9.3/static/using-explain.html Then, AFAIK, Bitmap Heap Scan (upper level) is always coupled with Bitmap Index Scan (lower level) so there are always in at least 2 nodes, at least because you could have multiple Bitmap Index Scan nodes. The lower node Bitmap Index Scan creates a bitmap of the pages of the relation to track pages that might contain tuples satisfying the index condition (1 bit per page, so a relation with 1 million pages would have roughly 119kB). Then the bitmap is passed to the upper node called "Bitmap Index Scan", that reads the pages in a more sequential fashion. Regards, -- Michael -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance From torsten.foertsch@gmx.net Wed Nov 13 09:41:27 2013 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VgWwx-0006qK-Kf for pgsql-performance@arkaria.postgresql.org; Wed, 13 Nov 2013 09:41:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VgWwx-00059u-3a for pgsql-performance@arkaria.postgresql.org; Wed, 13 Nov 2013 09:41:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VgWwv-00058I-Dp for pgsql-performance@postgresql.org; Wed, 13 Nov 2013 09:41:25 +0000 Received: from mout.gmx.net ([212.227.15.15]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VgWwo-0007jo-K0 for pgsql-performance@postgresql.org; Wed, 13 Nov 2013 09:41:25 +0000 Received: from calvados.home ([91.89.70.247]) by mail.gmx.com (mrgmx101) with ESMTPSA (Nemesis) id 0MIQlv-1Vj0BX2pLz-004GEW for ; Wed, 13 Nov 2013 10:41:16 +0100 Message-ID: <5283486A.7000209@gmx.net> Date: Wed, 13 Nov 2013 10:37:46 +0100 From: =?ISO-8859-1?Q?Torsten_F=F6rtsch?= User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.1.0 MIME-Version: 1.0 To: monalee_dba , pgsql-performance@postgresql.org Subject: Re: BitMap Heap Scan & BitMap Index Scan References: <1384068734255-5777632.post@n5.nabble.com> In-Reply-To: <1384068734255-5777632.post@n5.nabble.com> X-Enigmail-Version: 1.6 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Provags-ID: V03:K0:JhK/1ovjdYWnREdSs5C3VffMYcKDEnhgfFtQEOoAoPdI7a/nx3i UkoXuTfN52JzpETlAGOubcEYwIP5mt3mnTK2w3L0aPAEhAfi8se/BHqucbJCk3+yLBnpy1Q fBhFyI3g3qh2Su2RT2BANQf2kcFXq+uUaXQZxg9UUN6LHMF3EAwIVjVXcgMfo5hipkJ3gfb rECbsu/EX9WLN9ubI2Kyw== X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 10/11/13 08:32, monalee_dba wrote: > I would like to know, What is BitMap Heap Scan & BitMap Index Scan? When > I use EXPLAIN for query, which has LEFT JOIN with 4 different table then > some time query planner uses Bitmap Heap Scan and some time Bitmap Index > Scan? Check out this great presentation: http://momjian.us/main/writings/pgsql/optimizer.pdf The way I understand it is this (Correct me if I am wrong). The bitmap index scan uses an index to build a bitmap where each bit corresponds to a data buffer (8k). Since a buffer can contain multiple tuples and not all of them must match the condition another run over the heap pages is needed to find the matching tuples. This is the bitmap heap scan. It iterates over the table data buffers found in the bitmap index scan and selects only those tuples that match the filter (hence the recheck thing you see in explain) and visibility conditions. Torsten -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance