pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Laszlo Nagy <gandalf@designaproduct.biz>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org
Subject: Re: Poor performance on seq scan
Date: Tue, 12 Sep 2006 19:12:36 +0200
Message-ID: <4506EA84.4070200@designaproduct.biz> (raw )
In-Reply-To: <17034.1158079926@sss.pgh.pa.us >
References: <450684EC.4090304@designaproduct.biz >
<17034.1158079926@sss.pgh.pa.us >
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
view thread (26+ messages) latest in thread
Message-ID: <4506EA84.4070200@designaproduct.biz>
Permalink: ../4506EA84.4070200@designaproduct.biz/
Also on: postgresql.org/message-id/4506EA84.4070200@designaproduct.biz
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gandalf@designaproduct.biz, tgl@sss.pgh.pa.us
Subject: Re: Poor performance on seq scan
In-Reply-To: <4506EA84.4070200@designaproduct.biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox