X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id E19379FA4D7 for ; Tue, 12 Sep 2006 14:13:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 26973-08 for ; Tue, 12 Sep 2006 17:12:40 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp.enternet.hu (smtp.enternet.hu [62.112.192.21]) by postgresql.org (Postfix) with ESMTP id 6398B9FB1DB for ; Tue, 12 Sep 2006 14:12:40 -0300 (ADT) Received: from [83.216.41.252] (helo=[172.16.0.43]) by smtp.enternet.hu with esmtpa (Exim 4) id 1GNBoK-000HQz-1p; Tue, 12 Sep 2006 19:12:36 +0200 Message-ID: <4506EA84.4070200@designaproduct.biz> Date: Tue, 12 Sep 2006 19:12:36 +0200 From: Laszlo Nagy User-Agent: Thunderbird 1.5.0.5 (Windows/20060719) MIME-Version: 1.0 To: Tom Lane , pgsql-performance@postgresql.org Subject: Re: Poor performance on seq scan References: <450684EC.4090304@designaproduct.biz> <17034.1158079926@sss.pgh.pa.us> In-Reply-To: <17034.1158079926@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/95 X-Sequence-Number: 20781 Tom Lane wrote: > Why is that showing 85+ percent *system* CPU time?? I could believe a > lot of idle CPU if the query is I/O bound, or a lot of user time if PG > was being a hog about doing the ~~ comparisons (not too unlikely BTW). > I'm sorry, this was really confusing. I don't know what it was - probably a background system process, started from cron (?). I retried the same query and I got this: zeusd1=> explain analyze select id,name from product where name like '%Mug%'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Seq Scan on product (cost=0.00..206891.34 rows=36487 width=40) (actual time=17.188..44585.176 rows=91399 loops=1) Filter: (name ~~ '%Mug%'::text) Total runtime: 44631.150 ms (3 rows) tty ad4 ad6 cpu tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id 0 62 115.25 143 16.06 116.03 143 16.17 3 0 9 3 85 0 62 122.11 144 17.12 121.78 144 17.07 6 0 3 2 89 0 62 126.18 158 19.45 125.86 157 19.28 5 0 11 6 79 0 62 126.41 131 16.13 127.52 132 16.39 5 0 9 6 80 0 62 127.80 159 19.81 126.89 158 19.55 5 0 9 0 86 0 62 125.29 165 20.15 126.26 165 20.30 5 0 14 2 80 0 62 127.22 164 20.32 126.74 165 20.37 5 0 9 0 86 0 62 121.34 150 17.75 120.76 149 17.54 1 0 13 3 82 0 62 121.40 143 16.92 120.33 144 16.89 5 0 11 3 82 0 62 127.38 154 19.12 127.17 154 19.09 8 0 8 5 80 0 62 126.88 129 15.95 127.00 128 15.84 5 0 9 5 82 0 62 118.48 121 13.97 119.28 121 14.06 6 0 17 3 74 0 62 127.23 146 18.10 126.79 146 18.04 9 0 20 2 70 0 62 127.27 153 18.98 128.00 154 19.21 5 0 17 0 79 0 62 127.02 130 16.09 126.28 130 16.00 10 0 16 3 70 0 62 123.17 125 15.00 122.40 125 14.91 5 0 14 2 80 0 62 112.37 130 14.24 112.62 130 14.27 0 0 14 3 83 0 62 115.83 138 15.58 113.97 138 15.33 3 0 18 0 79 A bit better transfer rate, but nothing serious. Regards, Laszlo