Bitmap Index Scan optimization opportunity

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.)
 
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.
 
-Kevin
 
 
cc=> explain analyze select * from "Warrant" where "soundex" = 'S530' and "searchName" like '%,G%' and "countyNo" = 40;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on "Warrant"  (cost=55.37..1202.35 rows=841 width=123) (actual time=2.625..8.602 rows=112 loops=1)
   Recheck Cond: (((soundex)::text = 'S530'::text) AND (("countyNo")::smallint = 40))
   Filter: (("searchName")::text ~~ '%,G%'::text)
   ->  Bitmap Index Scan on "Warrant_WarrantSoundex"  (cost=0.00..55.16 rows=4240 width=0) (actual time=1.911..1.911 rows=4492 loops=1)
         Index Cond: (((soundex)::text = 'S530'::text) AND (("countyNo")::smallint = 40))
 Total runtime: 8.739 ms
(6 rows)

cc=> explain analyze select * from "Warrant" where "soundex" = 'S530' and "searchName" like 'SMITH,G%' and "countyNo" = 40;
                                                                             QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using "Warrant_WarrantName" on "Warrant"  (cost=0.00..1.28 rows=1 width=123) (actual time=0.099..0.397 rows=112 loops=1)
   Index Cond: ((("searchName")::text >= 'SMITH,G'::character varying) AND (("searchName")::text < 'SMITH,H'::character varying) AND (("countyNo")::smallint = 40))
   Filter: (((soundex)::text = 'S530'::text) AND (("searchName")::text ~~ 'SMITH,G%'::text))
 Total runtime: 0.510 ms
(4 rows)

cc=> \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=> select version();
                                       version
-------------------------------------------------------------------------------------
 PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux)
(1 row)



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux