pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Craig Ringer <ringerc@ringerc.id.au>
To: Yan Chunlu <springrider@gmail.com>
Cc: Albe Laurenz <laurenz.albe@wien.gv.at>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: how could select id=xx so slow?
Date: Thu, 12 Jul 2012 22:39:28 +0800
Message-ID: <4FFEE1A0.7060500@ringerc.id.au> (raw)
In-Reply-To: <CAOA66tGPS0crmWXZ6tr6Y1-HvNOmuGV3Jfu8kg2U9yhdg2gPeQ@mail.gmail.com>
References: <CAOA66tEJLDOt4JpJ0RwOEsgizfQPWkCHd+SgT7kbiHix_io18A@mail.gmail.com>
	<D960CB61B694CF459DCFB4B0128514C2081BF021@exadv11.host.magwien.gv.at>
	<CAOA66tF8dAzAS9wG381bhp+AiC2LrzcF7MDmOxvgW0p07rMNtw@mail.gmail.com>
	<4FFB848D.7080900@ringerc.id.au>
	<CAOA66tEe4m8OQQ+26AKxrreEF458R-983UVMARZ6zeqEkZxwCQ@mail.gmail.com>
	<4FFB9781.60601@ringerc.id.au>
	<CAOA66tEiZ+OrU6mRutVq6Vksa6tRsgQYvB4erSNUpWx7t8LksQ@mail.gmail.com>
	<D960CB61B694CF459DCFB4B0128514C2082464D4@exadv11.host.magwien.gv.at>
	<CAOA66tE9PwbQ07+g9=HhbVwqXosy=Tb=M51NwSpoAmh5LvnVnw@mail.gmail.com>
	<4FFE17CF.1050601@ringerc.id.au>
	<CAOA66tF1M+S=m-7mU=TV91_hkxdcb1T8vwSwb-KRFOrdgBqWwA@mail.gmail.com>
	<4FFE346A.7030402@ringerc.id.au>
	<CAOA66tGY7K=3aPagXUsmFu+3YXn+5gmM7HvxvoPz7YSk-=6EWA@mail.gmail.com>
	<4FFE751D.3050105@ringerc.id.au>
	<CAOA66tGPS0crmWXZ6tr6Y1-HvNOmuGV3Jfu8kg2U9yhdg2gPeQ@mail.gmail.com>

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

view thread (25+ messages)  latest in thread

Message-ID: <4FFEE1A0.7060500@ringerc.id.au>
Permalink:  ../4FFEE1A0.7060500@ringerc.id.au/
Also on:    postgresql.org/message-id/4FFEE1A0.7060500@ringerc.id.au

 · 

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: ringerc@ringerc.id.au, springrider@gmail.com, laurenz.albe@wien.gv.at
  Subject: Re: how could select id=xx so slow?
  In-Reply-To: <4FFEE1A0.7060500@ringerc.id.au>

* 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