X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4E00DD809C for ; Sun, 28 Aug 2005 17:48:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 86573-01 for ; Sun, 28 Aug 2005 20:48:47 +0000 (GMT) Received: from Apachihuilliztli.mtu.ru (apachihuilliztli.mtu.ru [195.34.32.124]) by svr1.postgresql.org (Postfix) with ESMTP id 22A3AD8100 for ; Sun, 28 Aug 2005 17:48:45 -0300 (ADT) Received: from umail.ru (umail.mtu.ru [195.34.32.101]) by Apachihuilliztli.mtu.ru (Postfix) with ESMTP id A985820A626 for ; Mon, 29 Aug 2005 00:48:47 +0400 (MSD) (envelope-from ilia@obnovlenie.ru) Received: from [85.140.125.21] (HELO ilia) by umail.ru (CommuniGate Pro SMTP 4.2b6) with ESMTP id 558491990 for pgsql-performance@postgresql.org; Mon, 29 Aug 2005 00:48:47 +0400 From: "Ilia Kantor" To: Subject: Bitmap scan when it is not needed Date: Mon, 29 Aug 2005 00:48:40 +0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001E_01C5AC33.6672B430" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcWsEd8vVV8k6GFwS/uc4O6Py5kyLw== Message-ID: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.156 required=5 tests=[AWL=0.105, FORGED_RCVD_HELO=0.05, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200508/488 X-Sequence-Number: 14235 This is a multi-part message in MIME format. ------=_NextPart_000_001E_01C5AC33.6672B430 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit explain analyze select * from objects_hier where tg && array[0] and id <10000; Bitmap Heap Scan on objects_hier (cost=4.79..8.80 rows=1 width=337) (actual time=0.110..0.110 rows=0 loops=1) Recheck Cond: ((tg && '{0}'::integer[]) AND (id < 10000)) -> BitmapAnd (cost=4.79..4.79 rows=1 width=0) (actual time=0.106..0.106 rows=0 loops=1) -> Bitmap Index Scan on gistbla2 (cost=0.00..2.10 rows=30 width=0) (actual time=0.042..0.042 rows=0 loops=1) Index Cond: (tg && '{0}'::integer[]) -> Bitmap Index Scan on ohid (cost=0.00..2.44 rows=1240 width=0) (actual time=0.058..0.058 rows=1255 loops=1) Index Cond: (id < 10000) I see, Bitmap is going to AND my indexes.. It read one with less number of rows estimated the first (right).. It found 0 records at gistbla2 index. Then why is it reading ohid ? Maybe a quickfix is possible for cases when 0 records is found to stop reading other AND elements.. ------=_NextPart_000_001E_01C5AC33.6672B430 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

explain analyze select * from objects_hier = where tg && array[0] and id <10000;

 

Bitmap Heap Scan on objects_hier  (cost=3D4.79..8.80 rows=3D1 width=3D337) (actual time=3D0.110..0.110 = rows=3D0 loops=3D1)

   Recheck Cond: ((tg && '{0}'::integer[]) AND (id < 10000))

   ->  BitmapAnd  (cost=3D4.79..4.79 rows=3D1 width=3D0) (actual time=3D0.106..0.106 = rows=3D0 loops=3D1)

        = ; ->  Bitmap Index Scan on gistbla2  (cost=3D0.00..2.10 = rows=3D30 width=3D0) (actual time=3D0.042..0.042 rows=3D0 = loops=3D1)

        = ;       Index Cond: (tg && = '{0}'::integer[])

        = ; ->  Bitmap Index Scan on ohid  (cost=3D0.00..2.44 = rows=3D1240 width=3D0) (actual time=3D0.058..0.058 rows=3D1255 = loops=3D1)

        = ;       Index Cond: (id < 10000)

 

I see, Bitmap is going to AND my indexes.. It = read one with less number of rows estimated the first = (right)..

It found 0 records at gistbla2 = index…

 

Then why is it reading ohid ?  =

 

Maybe a quickfix is possible for cases when 0 = records is found to stop reading other AND = elements..

 

------=_NextPart_000_001E_01C5AC33.6672B430--