Content-Type: text/plain; charset=iso-8859-1
Mime-Version: 1.0 (Mac OS X Mail 6.2 \(1499\))
Subject: Re: Insert performance for large transaction with multiple COPY FROM
From: Horst Dehmer <horst.dehmer@gmail.com>
In-Reply-To: 
 <CAMkU=1ykVF4Ry6guyhFDsHr-JJhtwC35+fsPfxUQMri-7bw_+g@mail.gmail.com>
Date: Fri, 18 Jan 2013 23:15:15 +0000
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <273CF8C2-159B-44FF-B382-61A76541F435@gmail.com>
References: <78A65A52-C49D-4CA4-9A63-1F5F120BC7E7@gmail.com>
	<CAMkU=1zx2MXdT+za1PokYc5SZEe4j=qaHG8GuG=13UOQ3oRScQ@mail.gmail.com>
	<B1146DE5-749D-4845-AB47-BAA86482AC40@gmail.com>
	<CAMkU=1ykVF4Ry6guyhFDsHr-JJhtwC35+fsPfxUQMri-7bw_+g@mail.gmail.com>
To: Jeff Janes <jeff.janes@gmail.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hey Jeff (and others)!

First of all: Thanks for your detailed explanations and guide lines.


On 17.01.2013, at 18:12, Jeff Janes <jeff.janes@gmail.com> wrote:

> So the theory is that the presence of idx_4 is causing the trigger to
> pick a poor plan (i.e. one using idx_4) while its absence removes that
> temptation?

Yes. And auto_explain confirms this for the first record (obj_item_loc_obj_=
item_id_idx =3D idx_4 from last my last mail):

2013-01-18 22:50:21 CET LOG:  duration: 0.021 ms  plan:
	Query Text: SELECT * FROM obj_item_loc WHERE obj_item_loc.obj_item_id =3D =
NEW.obj_item_id AND obj_item_loc.loc_id =3D NEW.loc_id AND obj_item_loc.obj=
_item_loc_ix =3D NEW.obj_item_loc_ix
	Index Scan using obj_item_loc_obj_item_id_idx on obj_item_loc  (cost=3D0.0=
0..8.27 rows=3D1 width=3D382)
	  Index Cond: (obj_item_id =3D $15)
	  Filter: ((loc_id =3D $16) AND (obj_item_loc_ix =3D $17))
2013-01-18 22:50:21 CET CONTEXT:  SQL statement "SELECT * FROM obj_item_loc=
 WHERE obj_item_loc.obj_item_id =3D NEW.obj_item_id AND obj_item_loc.loc_id=
 =3D NEW.loc_id AND obj_item_loc.obj_item_loc_ix =3D NEW.obj_item_loc_ix"
	PL/pgSQL function obj_item_loc_before_insert() line 5 at SQL statement
	COPY obj_item_loc, line 1: "10903011224100007276	10903010224100015110	1090=
0024100000029720	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\..."

and for one of the last records:

2013-01-18 22:53:20 CET LOG:  duration: 16.088 ms  plan:
	Query Text: SELECT * FROM obj_item_loc WHERE obj_item_loc.obj_item_id =3D =
NEW.obj_item_id AND obj_item_loc.loc_id =3D NEW.loc_id AND obj_item_loc.obj=
_item_loc_ix =3D NEW.obj_item_loc_ix
	Index Scan using obj_item_loc_obj_item_id_idx on obj_item_loc  (cost=3D0.0=
0..8.27 rows=3D1 width=3D382)
	  Index Cond: (obj_item_id =3D $15)
	  Filter: ((loc_id =3D $16) AND (obj_item_loc_ix =3D $17))

I see a linear increase of the duration from 0.0x ms to over 16 ms (apart f=
rom a few nasty outliers with about 22 ms). Although even at the end there =
are still a few durations < 0.03 but mostly 15 ms and above.

> True disk reads are much more expensive, but given how few reads you
> have relative to hits, I now think that in aggregate the hits are more
> of a concern than the reads are.  In other words, you seem to be CPU
> bound, not IO bound.

Yes, definitely CPU bound, as top shows 99+% CPU utilization.

> Even more so I think (but not with much confidence) that most of your
> "reads" are actually coming from the OS cache and not from the disk.
> PG cannot distinguish true disk reads from OS cache reads.
>=20
> When was the last time you reset the stats?  That is, are your
> reported numbers accumulated over several loads, with some having idx4
> and some not?

I set up a fresh database before each test run. So the stats should be clea=
n.

> More formally, use use auto_explain and set
> auto_explain.log_nested_statements to true.  I haven't verified this
> works with triggers, just going by the description I think it should.

Nice tip! Works for triggers as well.

> Your use case is a little unusual.  If you are bulk loading into an
> initially empty table, usually you would remove the trigger and add it
> after the load (with some kind of bulk operation to make up for
> whatever it was the trigger would have been doing).  On the other
> hand, if you are bulk loading into a "live" table and so can't drop
> the trigger, then the live table should have good-enough preexisting
> statistics to make the trigger choose a good plan.

My case is indeed unusual as for the whole model of 276 tables there will n=
ever be an update nor a delete on any row.
The model is rather short-lived, from a few hours to a few months. COPY FRO=
M/TO are the only ways to get data into the database and back out. And in b=
etween there is lots of graph traversal and calculation of convex hulls. Bu=
t the lengthy transaction are by far not the common case.

Having said that, I'm no longer sure if a RDBMS is the right tool for the b=
ackend. Maybe indexing and storing with a plain full text search engine is.=
 Dunno...

Thanks again!

--
Horst

--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance