Skip site navigation (1) Skip section navigation (2)

Re: Poor performance on seq scan

From: Laszlo Nagy <gandalf(at)designaproduct(dot)biz>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Poor performance on seq scan
Date: 2006-09-12 17:12:36
Message-ID: 4506EA84.4070200@designaproduct.biz (view raw or flat)
Thread:
Lists: pgsql-performance
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



In response to

Responses

pgsql-performance by date

Next:From: PiñeiroDate: 2006-09-12 18:28:28
Subject: Re: [Fwd: Re: Performance problem with Sarge compared
Previous:From: Laszlo NagyDate: 2006-09-12 17:01:32
Subject: tsearch2 question (was: Poor performance on seq scan)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group