Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 6D10679D58 for ; Thu, 12 Jul 2012 11:39:55 -0300 (ADT) Received: from mail-pb0-f46.google.com ([209.85.160.46]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SpKYW-0007aa-El for pgsql-performance@postgresql.org; Thu, 12 Jul 2012 14:39:54 +0000 Received: by pbbrp8 with SMTP id rp8so3822120pbb.19 for ; Thu, 12 Jul 2012 07:39:34 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:x-gm-message-state; bh=AVdIm389dQ+kDu4AARzVlSBPDRvs95/vo9TQmh6a11w=; b=Te2B2XiNXvHlMSc7Px+KxHuo/NRBeA26z05fZ9QdXYfTA3lrODGsRr4VQAP2FVvT2n 6ZK2z0BHX3/k1SeYPIrzVC8h3pX4zhnSFjdp/SeKgwG465iw9ite9uQu4A+lpDbmvQ3c Y/xSTPxEUfNPS5DdcRK2Pt+FzwX0/gaNzG+hA0UkNG8lTyoP+9boAdpuTj0P3ZT0VT/P ARxCyZcK9R+zntEoWvB5Otms3SJyLEN7QW9ZS2IjCamWNWSHSbTuxuYjhyvofmWu6kM8 iZo0egsrJwmIeKXj+wk2DcQ+TnWdluGySupd14gyZmO9ORMJzgaa4o10ct1p707Wo/x9 SWBg== Received: by 10.68.202.136 with SMTP id ki8mr6082120pbc.65.1342103973967; Thu, 12 Jul 2012 07:39:33 -0700 (PDT) Received: from ayaki.localdomain (124-169-84-247.dyn.iinet.net.au. [124.169.84.247]) by mx.google.com with ESMTPS id qd2sm159276pbb.29.2012.07.12.07.39.31 (version=SSLv3 cipher=OTHER); Thu, 12 Jul 2012 07:39:33 -0700 (PDT) Message-ID: <4FFEE1A0.7060500@ringerc.id.au> Date: Thu, 12 Jul 2012 22:39:28 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:13.0) Gecko/20120605 Thunderbird/13.0 MIME-Version: 1.0 To: Yan Chunlu CC: Albe Laurenz , "pgsql-performance@postgresql.org" Subject: Re: how could select id=xx so slow? References: <4FFB848D.7080900@ringerc.id.au> <4FFB9781.60601@ringerc.id.au> <4FFE17CF.1050601@ringerc.id.au> <4FFE346A.7030402@ringerc.id.au> <4FFE751D.3050105@ringerc.id.au> In-Reply-To: Content-Type: multipart/alternative; boundary="------------050803070108010907020508" X-Gm-Message-State: ALoCoQm4gx4g1x91Zj2AIVns5mbj4IaPbpB7cbLQdvEEQt0sTWe+QVpXiVVLfNkNaRxm3ORlBASu X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/155 X-Sequence-Number: 47336 This is a multi-part message in MIME format. --------------050803070108010907020508 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 07/12/2012 08:48 PM, Yan Chunlu wrote: > > > explain analyze INSERT INTO vote_content ( thing1_id, thing2_id, name, > date) VALUES (1,1, E'1', '2012-07-12T12:34:29.926863+00:00'::timestamptz) > > QUERY PLAN > ------------------------------------------------------------------------------------------ > Insert (cost=0.00..0.01 rows=1 width=0) (actual time=79.610..79.610 > rows=0 loops=1) > -> Result (cost=0.00..0.01 rows=1 width=0) (actual > time=0.058..0.060 rows=1 loops=1) > Total runtime: 79.656 ms > > it is a table with *50 million* rows, so not sure if it is too > large... I have attached the schema below: You have eight indexes on that table according to the schema you showed. Three of them cover three columns. Those indexes are going to be expensive to update; frankly I'm amazed it's that FAST to update them when they're that big. Use pg_size_pretty(pg_relation_size('index_name')) to get the index sizes and compare to the pg_relation_size of the table. It might be informative. You may see some insert performance benefits with a non-100% fill factor on the indexes, but with possible performance costs to index scans. -- Craig Ringer --------------050803070108010907020508 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 07/12/2012 08:48 PM, Yan Chunlu wrote:


explain analyze INSERT INTO vote_content ( thing1_id, thing2_id, name, date) VALUES (1,1, E'1', '2012-07-12T12:34:29.926863+00:00'::timestamptz)

                                       QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Insert  (cost=0.00..0.01 rows=1 width=0) (actual time=79.610..79.610 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.058..0.060 rows=1 loops=1)
 Total runtime: 79.656 ms

it is a table with 50 million rows, so not sure if it is too large... I have attached the schema below:

You have eight indexes on that table according to the schema you showed. Three of them cover three columns. Those indexes are going to be expensive to update; frankly I'm amazed it's that FAST to update them when they're that big.

Use pg_size_pretty(pg_relation_size('index_name')) to get the index sizes and compare to the pg_relation_size of the table. It might be informative.

You may see some insert performance benefits with a non-100% fill factor on the indexes, but with possible performance costs to index scans.

--
Craig Ringer

--------------050803070108010907020508--