X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B056E533BB for ; Mon, 4 Apr 2005 17:09:37 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 74446-05 for ; Mon, 4 Apr 2005 16:09:36 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.193]) by svr1.postgresql.org (Postfix) with ESMTP id 73B56529E1 for ; Mon, 4 Apr 2005 17:09:35 +0100 (BST) Received: by rproxy.gmail.com with SMTP id a41so1239120rng for ; Mon, 04 Apr 2005 09:09:35 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=bmkLlaRHj6ydrj4IH5tOid8ZtS/OIl037dSrac6HwBjpPldC7MpkxSkeuxPGjrSr91XzAOP6cex0HNV615eatpI/5cmuqArADXmRA9GPY1bOL+pXPgLKK5FGmglzrZihde851IiLwh7fAJTQrubNP89/MwwJnVqI1PGm+k6MkOQ= Received: by 10.38.151.1 with SMTP id y1mr5408462rnd; Mon, 04 Apr 2005 09:09:35 -0700 (PDT) Received: by 10.38.207.41 with HTTP; Mon, 4 Apr 2005 09:09:35 -0700 (PDT) Message-ID: <59d991c405040409094e7bbd17@mail.gmail.com> Date: Mon, 4 Apr 2005 12:09:35 -0400 From: Christopher Petrilli Reply-To: Christopher Petrilli To: Tom Lane Subject: Re: Sustained inserts per sec ... ? Cc: jd@commandprompt.com, "Marc G. Fournier" , pgsql-performance@postgresql.org In-Reply-To: <26454.1112629979@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit References: <20050401140421.P18194@ganymede.hub.org> <59d991c4050401113857086517@mail.gmail.com> <28353.1112388128@sss.pgh.pa.us> <59d991c4050401124638c4ab5d@mail.gmail.com> <1112388818.3487.34.camel@jd2.commandprompt.com> <59d991c4050401125956d6069d@mail.gmail.com> <59d991c40504040648457808e9@mail.gmail.com> <26454.1112629979@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.279 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/53 X-Sequence-Number: 11450 On Apr 4, 2005 11:52 AM, Tom Lane wrote: > Christopher Petrilli writes: > > The table has: > > * 21 columns (nothing too strange) > > * No OIDS > > * 5 indexes, including the primary key on a string > > Could we see the *exact* SQL definitions of the table and indexes? > Also some sample data would be interesting. I'm wondering for example > about the incidence of duplicate index keys. Of course, this is a bit cleansed, since it's an internal project, but only the column names are changed: CREATE TABLE foos ( foo_id VARCHAR(32), s_ts INTEGER NOT NULL, c_ts INTEGER NOT NULL, bar_id INTEGER NOT NULL, proto INTEGER NOT NULL, src_ip INT8 NOT NULL, dst_ip INT8 NOT NULL, src_port INTEGER, dst_port INTEGER, nated INTEGER NOT NULL, src_nat_ip INT8, dst_nat_ip INT8, src_nat_port INTEGER, dst_nat_port INTEGER, foo_class INTEGER NOT NULL, foo_type INTEGER NOT NULL, src_bar INTEGER NOT NULL, dst_bar INTEGER NOT NULL, user_name VARCHAR(255), info TEXT ) WITHOUT OIDS; ALTER TABLE foos ADD CONSTRAINT foos_foo_id_pk UNIQUE (foo_id); CREATE INDEX foos_c_ts_idx ON foos(conduit_ts); CREATE INDEX foos_src_ip_idx ON foos(src_ip); CREATE INDEX foos_dst_ip_idx ON foos(dst_ip); CREATE INDEX foos_foo_class_idx ON foos(foo_class); CREATE INDEX foos_foo_type_idx ON foos(foo_type); CREATE TABLE foos001 ( ) INHERITS (foos) WITHOUT OIDS; ALTER TABLE foos001 ADD CONSTRAINT foos001_foo_id_pk UNIQUE (foo_id); CREATE INDEX foos001_c_ts_idx ON foos001(conduit_ts); CREATE INDEX foos001_src_ip_idx ON foos001(src_ip); CREATE INDEX foos001_dst_ip_idx ON foos001(dst_ip); CREATE INDEX foos001_foo_class_idx ON foos001(foo_class); CREATE INDEX foos001_foo_type_idx ON foos001(foo_type); That continues on, but you get the idea... So, as you asked about data content, specifically regarding indices, here's what the "simulator" creates: foo_id - 32 character UID (generated by the UUID function in mxTools, which looks like '00beef19420053c64f3f01aeb0b4a2a5', and varies in the upper components more than the lower. *_ts - UNIX epoch timestamps, sequential. There's a long story behind not using DATETIME format, but if that's the big issue, it can be dealt with. *_ip - Randomly selected 32-bit integers from a pre-generated list containing about 500 different numbers ranging from 3232235500 to 3232236031. This is unfortunately, not too atypical from the "real world". *_class - Randomly selected 1-100 (again, not atypical, although normal distribution would be less random) *_type - Randomly selected 1-10000 (not atypical, and more random than in real world) Hopefully this helps? Chris -- | Christopher Petrilli | petrilli@gmail.com