Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 264249F98A4 for ; Fri, 10 Aug 2007 16:34:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 91865-06 for ; Fri, 10 Aug 2007 16:34:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id D823E9F978D for ; Fri, 10 Aug 2007 16:33:44 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Fri, 10 Aug 2007 14:33:43 -0500 Message-Id: <46BC7737.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Fri, 10 Aug 2007 14:33:28 -0500 From: "Kevin Grittner" To: Subject: Bitmap Index Scan optimization opportunity Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/111 X-Sequence-Number: 26241 These query times are the "fully cached" times for both, from doing a = previous run of the same query. (The first one took 193.772 ms on its = first run; I don't have a good "uncached" timing for the second one at = this point.) =20 It seems like the first query could move the searchName filter to the = Bitmap Index Scan phase, and save 97.5% of the page retrievals in the = Bitmap Heap Scan. =20 -Kevin =20 =20 cc=3D> explain analyze select * from "Warrant" where "soundex" =3D 'S530' = and "searchName" like '%,G%' and "countyNo" =3D 40; QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------------------- Bitmap Heap Scan on "Warrant" (cost=3D55.37..1202.35 rows=3D841 = width=3D123) (actual time=3D2.625..8.602 rows=3D112 loops=3D1) Recheck Cond: (((soundex)::text =3D 'S530'::text) AND (("countyNo")::sma= llint =3D 40)) Filter: (("searchName")::text ~~ '%,G%'::text) -> Bitmap Index Scan on "Warrant_WarrantSoundex" (cost=3D0.00..55.16 = rows=3D4240 width=3D0) (actual time=3D1.911..1.911 rows=3D4492 loops=3D1) Index Cond: (((soundex)::text =3D 'S530'::text) AND (("countyNo"):= :smallint =3D 40)) Total runtime: 8.739 ms (6 rows) cc=3D> explain analyze select * from "Warrant" where "soundex" =3D 'S530' = and "searchName" like 'SMITH,G%' and "countyNo" =3D 40; = QUERY PLAN = =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------- Index Scan using "Warrant_WarrantName" on "Warrant" (cost=3D0.00..1.28 = rows=3D1 width=3D123) (actual time=3D0.099..0.397 rows=3D112 loops=3D1) Index Cond: ((("searchName")::text >=3D 'SMITH,G'::character varying) = AND (("searchName")::text < 'SMITH,H'::character varying) AND (("countyNo")= ::smallint =3D 40)) Filter: (((soundex)::text =3D 'S530'::text) AND (("searchName")::text = ~~ 'SMITH,G%'::text)) Total runtime: 0.510 ms (4 rows) cc=3D> \d "Warrant" Table "public.Warrant" Column | Type | Modifiers ----------------+-----------------+----------- warrantSeqNo | "WarrantSeqNoT" | not null countyNo | "CountyNoT" | not null caseNo | "CaseNoT" | not null nameL | "LastNameT" | not null partyNo | "PartyNoT" | not null searchName | "SearchNameT" | not null soundex | "SoundexT" | not null authSeqNo | "HistSeqNoT" | dateAuthorized | "DateT" | dateDisposed | "DateT" | dateIssued | "DateT" | dispoMethod | "EventTypeT" | dispSeqNo | "HistSeqNoT" | histSeqNo | "HistSeqNoT" | nameF | "FirstNameT" | nameM | "MiddleNameT" | stayDate | "DateT" | stayTime | "TimeT" | suffix | "NameSuffixT" | warrantDob | "DateT" | Indexes: "Warrant_pkey" PRIMARY KEY, btree ("warrantSeqNo", "countyNo") "Warrant_HistSeqNo" UNIQUE, btree ("caseNo", "histSeqNo", "countyNo", = "warrantSeqNo") "Warrant_AuthSeqNo" btree ("caseNo", "authSeqNo", "countyNo") "Warrant_CaseNo" btree ("caseNo", "partyNo", "countyNo") "Warrant_DispSeqNo" btree ("caseNo", "dispSeqNo", "countyNo") "Warrant_WarrantName" btree ("searchName", "countyNo") "Warrant_WarrantSoundex" btree (soundex, "searchName", "countyNo") cc=3D> select version(); version ---------------------------------------------------------------------------= ---------- PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 = (SuSE Linux) (1 row)