On 17.10.2013 20:56, akp geek wrote: > got the output from depesz and this the top on the system. thanks for > the help .. >From depesz? That site works differently - you enter the explain plan into the form, it does some formatting/highlighting and you're supposed to submit the link to that page. E.g. http://explain.depesz.com/s/JwTB is for your query. However it seems you've done some anonymization on the query, e.g. replacing the table/index/function names and string literals with some random words. Anyway, the plan shows the estimates are reasonably accurate, but the index scan on romeo_four consumes ~99% of the query duration. Not sure if it can be improved, because we don't know the original query and the anonymization made it rather annoying to deal with. > > Regards > > load averages: 30.3, 28.8, 27.8; up > 680+00:51:09 > 18:24:44 > 156 processes: 123 sleeping, 33 on cpu > CPU states: 49.5% idle, 50.0% user, 0.4% kernel, 0.0% iowait, 0.0% swap > Memory: 64G phys mem, 19G free mem, 63G total swap, 63G free swap > > PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND > 7965 postgres 1 20 0 3178M 3171M cpu/7 78:21 1.58% postgres > 20638 postgres 1 20 0 3181M 3175M cpu/28 187:01 1.58% postgres > 22819 postgres 1 30 0 3181M 3175M cpu/33 264:06 1.57% postgres > 3789 postgres 1 20 0 3183M 3176M cpu/9 626:11 1.57% postgres > 10375 postgres 1 30 0 3182M 3175M cpu/50 293:55 1.57% postgres > 27335 postgres 1 30 0 3175M 3169M cpu/29 27:27 1.57% postgres > 7959 postgres 1 30 0 3178M 3171M cpu/41 104:07 1.57% postgres > 8156 postgres 1 30 0 3180M 3173M cpu/43 124:18 1.56% postgres > 20640 postgres 1 0 0 3182M 3176M cpu/58 207:49 1.56% postgres > 10584 postgres 1 30 0 3179M 3173M cpu/35 76:32 1.56% postgres > 13984 postgres 1 30 0 3181M 3174M cpu/30 207:04 1.56% postgres > 3575 postgres 1 20 0 3283M 3261M cpu/19 1:48 1.56% postgres > 7964 postgres 1 40 0 3177M 3170M cpu/62 82:56 1.56% postgres > 1299 postgres 1 40 0 3166M 3161M cpu/52 5:11 1.56% postgres > 27692 postgres 1 0 0 3181M 3174M cpu/46 260:58 1.56% postgres Once again, this top output is incomplete - all it lists are postgres processes (and it's unclear what they're doing), but there are certainly some other processes running. Moreover, those 15 processes account for only ~25% of CPU, but there's almost 50% CPU utilized. So where's the rest? Tomas -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
