Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XBv07-0008Sz-LJ for pgsql-performance@arkaria.postgresql.org; Tue, 29 Jul 2014 00:10:43 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XBv07-0001sz-2J for pgsql-performance@arkaria.postgresql.org; Tue, 29 Jul 2014 00:10:43 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XBv06-0001st-2c for pgsql-performance@postgresql.org; Tue, 29 Jul 2014 00:10:42 +0000 Received: from mail-pd0-x235.google.com ([2607:f8b0:400e:c02::235]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XBv03-0004hz-DV for pgsql-performance@postgresql.org; Tue, 29 Jul 2014 00:10:40 +0000 Received: by mail-pd0-f181.google.com with SMTP id g10so10748194pdj.12 for ; Mon, 28 Jul 2014 17:10:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:message-id:date:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding; bh=8A43inIr6NJbCA5CW4C5OS4m24PVJ6FCpwPJudFmbxQ=; b=elqmjQ1O/MWLJud/ltM5IfPAl9BRrY64CHehyhnseZd4xhoVrT2yvXxU6bgNikVkVf 7qF7mYHHHdIuUd+iAWYvUgTWt5T+9ZMCGRIwS4qthGQ3CIoAQrgu8EhQWYVRsB5QQ6wg blLSAT3EvEE+Tmlv9Thx//GZpHAmCUnHAJ/MaJJDGXp+PSBMwB/D0RB2sEK88hZY2Y01 XdjydmYxOuHKr+oxFxSEskCp1xMTkBMl7qpD+hyqMt1vPBJz16iPpTWToyPSIduYFxQ3 pw45KBzpfKusXFMZvDAC6CeCGsKKlBkS4SfxUYy4zoTRRd9e4sYM+UEGhcEgL9uqQ7ab bZbQ== X-Received: by 10.66.174.199 with SMTP id bu7mr42332194pac.54.1406592637564; Mon, 28 Jul 2014 17:10:37 -0700 (PDT) Received: from [192.168.11.4] ([180.164.153.84]) by mx.google.com with ESMTPSA id am2sm18879027pbc.59.2014.07.28.17.10.35 for (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 28 Jul 2014 17:10:37 -0700 (PDT) From: Rural Hunter X-Google-Original-From: Rural Hunter Message-ID: <53D6E67B.1040003@gmail.com> Date: Tue, 29 Jul 2014 08:10:35 +0800 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) Gecko/20100101 Thunderbird/31.0 MIME-Version: 1.0 To: Jeff Janes CC: "pgsql-performance@postgresql.org" Subject: Re: Very slow planning performance on partition table References: <53CFB6BC.4050606@gmail.com> <25010.1406253203@sss.pgh.pa.us> <53D26875.2080604@gmail.com> <53D51E49.102@gmail.com> <24564.1406478487@sss.pgh.pa.us> In-Reply-To: Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit X-Pg-Spam-Score: -1.3 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org

在 2014/7/29 1:29, Jeff Janes 写道:

If it were waiting on a pg_locks lock, the semop should be coming from ProcSleep, not from LWLockAcquire, shouldn't it?

I'm guessing he has a lot of connections, and each connection is locking each partition in shared mode in rapid fire, generating spin-lock or cache-line contention.

Cheers,

Jeff

Yes. I have a lot of connections and they maybe coming together and doing the same update statement without partition key on the partition table.