Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 1986F9FB731 for ; Tue, 3 Apr 2007 17:45:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 45762-01 for ; Tue, 3 Apr 2007 17:45:22 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.247]) by postgresql.org (Postfix) with ESMTP id 25A389FB68A for ; Tue, 3 Apr 2007 17:45:25 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so1843548ana for ; Tue, 03 Apr 2007 13:45:23 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=IMdeV/f0W6/h3HGczBkjGdMH7NTIfRipAGr5TfpboBTQi7woJR8R0VWhEd6R+64GMb6cH95TOphTYnnYcrsHTPt3pB6ABoQTbYTSYGYlqD9YZlyjvx3tEb82lNa/6vkuAMvGcjYk+zFHlK45Y1cqn+etiE4FU1SswzTgnQ4+ZmI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Mi+axYiKdVzQ7+lEq0CotMfu1QCTvyoTMMa2diuNPe8+2+esh52m8ICGOt/zAEtErMWX7nCmhm4pSEXb5gV1d58z5U09BwayiHlrAKangrLwe9S+xsl9Edf7CBOwftMg7/F/WDKB/YVD+W+YDy4UukpkpefDN103KLW9YtTYak8= Received: by 10.100.133.9 with SMTP id g9mr4798575and.1175633123211; Tue, 03 Apr 2007 13:45:23 -0700 (PDT) Received: by 10.100.247.7 with HTTP; Tue, 3 Apr 2007 13:45:23 -0700 (PDT) Message-ID: Date: Tue, 3 Apr 2007 16:45:23 -0400 From: "Alex Deucher" To: "Merlin Moncure" Subject: Re: postgres 7.4 vs 8.x redux: query plans Cc: "Tom Lane" , "PostgreSQL Performance" In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <17931.1175578083@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/37 X-Sequence-Number: 23914 On 4/3/07, Alex Deucher wrote: > On 4/3/07, Alex Deucher wrote: > > On 4/3/07, Merlin Moncure wrote: > > > On 4/3/07, Alex Deucher wrote: > > > (('{AB6698130,AB7076908,AB6499382,AB6438888,AB6385893,AB6378237,AB7146973,AB7127138,AB7124531,AB7124513,AB7123427,AB7121183,AB7121036,AB7110101,AB7100321,AB7089845,AB7088750,AB7031384,AB7021188,AB7006144,AB6988331,AB6973865,AB6966775,AB6935066,AB6931779,AB6923412,AB6902405,AB6892488,AB6886288,AB6880467,AB6874269,AB6871439,AB6868615,AB6819495,AB6807740,AB6799138,AB6796038,AB6769347,AB6732987,AB6722076,AB6718130,AB6717543,AB6714564,AB6701821,AB6667761,AB6666630,AB6655069,AB6648287,AB6643969,AB6636412}'::character > > > > varying[])::text[])) > > > > -> Bitmap Index Scan on t1_pkey (cost=0.00..216.69 rows=50 > > > > width=0) (actual time=198.188..198.188 rows=50 loops=1) > > > > Index Cond: ((num)::text = ANY > > > > > > bitmap scan: > > > * did you run analyze? > > > > yes. > > > > > * is effective_cache_size set properly? > > > > It should be. I based it on the output of `free`. It's set to 988232. > > The system has 8 GB of ram. > > > > > * if nothing else works, try disable bitmap scan and running query. > > > > I'll give that a try and post the results. > > > > Turning off bitmapscan ends up doing a sequential scan. Turning off > both bitmapscan and seqscan results in a bitmap heap scan. It doesn't > seem to want to use the index at all. Any ideas? > Here are some new query plans: db=# set enable_bitmapscan = 0; SET db=# EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 from t1 where num in ('AB7089845','AB7044044','AB6873406','AB6862832','AB6819495','AB6708597','AB6671991','AB6549872','AB6421947','AB6295753','AB6289624','AB6151788','AB5837918','AB5822713','AB5795628','AB5784823','AB5784821','AB5686690','AB5661775','AB5448834','AB5388364','AB5364097','AB5323555','AB5282594','AB5237773','AB5204489','AB5187317','AB5171933','AB4876942','AB4825258','AB4823674','AB4787291','AB4760770','AB4665795','AB4404890','AB4213700','AB4202246','AB4164081','AB4048489','AB4040744','AB4015258','AB4011789','AB3997762'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Seq Scan on t1 (cost=0.00..2058004.69 rows=43 width=157) (actual time=31227.514..39911.029 rows=43 loops=1) Filter: ((num)::text = ANY (('{AB7089845,AB7044044,AB6873406,AB6862832,AB6819495,AB6708597,AB6671991,AB6549872,AB6421947,AB6295753,AB6289624,AB6151788,AB5837918,AB5822713,AB5795628,AB5784823,AB5784821,AB5686690,AB5661775,AB5448834,AB5388364,AB5364097,AB5323555,AB5282594,AB5237773,AB5204489,AB5187317,AB5171933,AB4876942,AB4825258,AB4823674,AB4787291,AB4760770,AB4665795,AB4404890,AB4213700,AB4202246,AB4164081,AB4048489,AB4040744,AB4015258,AB4011789,AB3997762}'::character varying[])::text[])) Total runtime: 39911.192 ms (3 rows) db=# set enable_seqscan = 0; SET db=# show enable_bitmapscan; enable_bitmapscan ------------------- off (1 row) db=# show enable_seqscan ; enable_seqscan ---------------- off (1 row) db=# EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 from t1 where num in ('AB6698130','AB7076908','AB6499382','AB6438888','AB6385893','AB6378237','AB7146973','AB7127138','AB7124531','AB7124513','AB7123427','AB7121183','AB7121036','AB7110101','AB7100321','AB7089845','AB7088750','AB7031384','AB7021188','AB7006144','AB6988331','AB6973865','AB6966775','AB6935066','AB6931779','AB6923412','AB6902405','AB6892488','AB6886288','AB6880467','AB6874269','AB6871439','AB6868615','AB6819495','AB6807740','AB6799138','AB6796038','AB6769347','AB6732987','AB6722076','AB6718130','AB6717543','AB6714564','AB6701821','AB6667761','AB6666630','AB6655069','AB6648287','AB6643969','AB6636412'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on t1 (cost=100000216.70..100000418.95 rows=50 width=157) (actual time=236.200..236.999 rows=50 loops=1) Recheck Cond: ((num)::text = ANY (('{AB6698130,AB7076908,AB6499382,AB6438888,AB6385893,AB6378237,AB7146973,AB7127138,AB7124531,AB7124513,AB7123427,AB7121183,AB7121036,AB7110101,AB7100321,AB7089845,AB7088750,AB7031384,AB7021188,AB7006144,AB6988331,AB6973865,AB6966775,AB6935066,AB6931779,AB6923412,AB6902405,AB6892488,AB6886288,AB6880467,AB6874269,AB6871439,AB6868615,AB6819495,AB6807740,AB6799138,AB6796038,AB6769347,AB6732987,AB6722076,AB6718130,AB6717543,AB6714564,AB6701821,AB6667761,AB6666630,AB6655069,AB6648287,AB6643969,AB6636412}'::character varying[])::text[])) -> Bitmap Index Scan on t1_pkey (cost=0.00..216.69 rows=50 width=0) (actual time=236.163..236.163 rows=50 loops=1) Index Cond: ((num)::text = ANY (('{AB6698130,AB7076908,AB6499382,AB6438888,AB6385893,AB6378237,AB7146973,AB7127138,AB7124531,AB7124513,AB7123427,AB7121183,AB7121036,AB7110101,AB7100321,AB7089845,AB7088750,AB7031384,AB7021188,AB7006144,AB6988331,AB6973865,AB6966775,AB6935066,AB6931779,AB6923412,AB6902405,AB6892488,AB6886288,AB6880467,AB6874269,AB6871439,AB6868615,AB6819495,AB6807740,AB6799138,AB6796038,AB6769347,AB6732987,AB6722076,AB6718130,AB6717543,AB6714564,AB6701821,AB6667761,AB6666630,AB6655069,AB6648287,AB6643969,AB6636412}'::character varying[])::text[])) Total runtime: 237.121 ms (5 rows)