Re: Poor performance on seq scan

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