pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Index Slowing Insert >50x
2+ messages / 2 participants
[nested] [flat]

* Index Slowing Insert >50x
@ 2005-01-31 21:14  Trevor Ball <TBall@eppcon.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Trevor Ball @ 2005-01-31 21:14 UTC (permalink / raw)
  To: pgsql-performance

I have a data collector function in a PostGreSQL 7.4 DB running on Linux
that inserts approximately 10000 records into a table every fifteen
minutes. The table has two macaddr columns, one varchar(50) column, two
timestamptz columns, five interval columns, one float8 column, and one
int4 column. I have one multi-column B-tree index on the two macaddr
columns, the varchar(50), and one of the timestamptz columns, in that
order.

The 10000-record insert takes approximately 2 minutes, which I thought
seemed awfully slow, so I tried removing the index, and sure enough,
without the index the insert took less than two seconds. I repeated the
inserts many times (with and without the index) and there's very little
other activity on this server, so I'm confident of these results.

There are approximately 10000 fixed combinations of the first three
indexed columns, and the fourth is the current time, so essentially what
the function is doing is inserting a set of values for each of those
10000 fixed combinations for every fifteen minute period. I can see how
this might be a worst-case scenario for an index, because the inserted
rows are alone and evenly spaced through the index. Even so, it doesn't
seem reasonable to me that an index would slow an insert more than
50-fold, regardless of hardware or the nature of the index. Am I wrong?
Can anybody suggest why this would be happening and what I might be able
to do about it? In production the table will have several million
records, and the index is necessary for data retrieval from this table
to be feasible, so leaving the index off is not an option.

Thanks in advance,
Trevor Ball

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Index Slowing Insert >50x
@ 2005-02-01 05:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Trevor Ball <TBall@eppcon.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2005-02-01 05:29 UTC (permalink / raw)
  To: Trevor Ball <TBall@eppcon.com>; +Cc: pgsql-performance

"Trevor Ball" <TBall@eppcon.com> writes:
> ... it doesn't
> seem reasonable to me that an index would slow an insert more than
> 50-fold, regardless of hardware or the nature of the index.

Seems pretty slow to me too.  Can you provide a self-contained test
case?

One possibility is that depending on your platform and locale setting,
varchar comparisons can be a whole lot slower than a normal person would
consider sane.  If you're not using C locale, you might try C locale and
see if it helps.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-02-01 05:29 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-31 21:14 Index Slowing Insert >50x Trevor Ball <TBall@eppcon.com>
2005-02-01 05:29 ` Tom Lane <tgl@sss.pgh.pa.us>

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