From ilia@obnovlenie.ru Sun Aug 28 20:48:51 2005 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-- From tgl@sss.pgh.pa.us Sun Aug 28 22:49:10 2005 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 A733BD6ECD for ; Sun, 28 Aug 2005 19:49:10 -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 16343-03 for ; Sun, 28 Aug 2005 22:49:08 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 346E8D6EA0 for ; Sun, 28 Aug 2005 19:49:07 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j7SMn7MZ028245; Sun, 28 Aug 2005 18:49:08 -0400 (EDT) To: "Ilia Kantor" Cc: pgsql-performance@postgresql.org Subject: Re: Bitmap scan when it is not needed In-reply-to: References: Comments: In-reply-to "Ilia Kantor" message dated "Mon, 29 Aug 2005 00:48:40 +0400" Date: Sun, 28 Aug 2005 18:49:07 -0400 Message-ID: <28244.1125269347@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200508/492 X-Sequence-Number: 14239 "Ilia Kantor" writes: > Maybe a quickfix is possible for cases when 0 records is found to stop > reading other AND elements.. Not sure how useful this will be in practice (since the planner tends not to bother ANDing unselective indexes at all), but it's easy enough to do ... so I did it. regards, tom lane