pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steve Lane <slane@moyergroup.com>
To: pgsql-admin@postgresql.org
Subject: Index (re)-creation speed
Date: Thu, 05 Feb 2004 22:36:52 -0600
Message-ID: <BC487604.9873%slane@moyergroup.com> (raw)
In-Reply-To: <BC486AB3.9862%slane@moyergroup.com>

All:

Been looking at speed issues related to loading large data sets, and
experimenting with dropping and rebuilding indexes. I noticed some
interesting behavior on the table in question. Here's the table:

             Table "test_responses_2"
     Attribute     |       Type        | Modifier
-------------------+-------------------+----------
 id_response       | integer           |
 id_code           | integer           |
 id_survey         | integer           |
 id_topic          | integer           |
 id_item           | integer           |
 id_administration | integer           |
 response_int      | smallint          |
 response_txt      | character varying |
 rec_handle        | character varying |
Indices: id_administration_test_key,
         id_code_test_key,
         id_item_test_key,
         id_response_test_key,
         id_survey_test_key,
         id_topic_test_key


When I drop and rebuild the indexes, they take oddly varying amounts of time
to rebuild. I rebuilt them in the following order, with the following rough
times. I took a guess that the speed of the rebuild might be related to the
number of distinct values in the column -- this seems true in some cases but
not in others. Here are the times:

id_response       38 secs (86000 distinct)
id_topic          33 secs (6 distinct)
id_survey         13 secs (1 distinct)
id_code           39 secs (1444 distinct)
id_item           40 secs (65 distinct)
id_administration 13 secs (1 distinct)

Is there anything I should be learning from this?

-- sgl

=======================================================
Steve Lane

Vice President
The Moyer Group
14 North Peoria St Suite 2H
Chicago, IL 60607

Voice: (312) 433-2421       Email: slane@moyergroup.com
Fax:   (312) 850-3930       Web:   http://www.moyergroup.com
=======================================================




view thread (4+ messages)  latest in thread

Message-ID: <BC487604.9873%slane@moyergroup.com>
Permalink:  ../BC487604.9873%25slane@moyergroup.com/
Also on:    postgresql.org/message-id/BC487604.9873%slane@moyergroup.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: slane@moyergroup.com
  Subject: Re: Index (re)-creation speed
  In-Reply-To: <BC487604.9873%slane@moyergroup.com>
  References: <BC486AB3.9862%slane@moyergroup.com> <BC487604.9873%slane@moyergroup.com>

* 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