Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 1AA1C2E004E for ; Thu, 22 May 2008 09:32:51 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 33861-07 for ; Thu, 22 May 2008 09:32:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.182]) by developer.postgresql.org (Postfix) with ESMTP id 56CD12E005E for ; Thu, 22 May 2008 09:32:47 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id n4so27084wag.5 for ; Thu, 22 May 2008 05:32:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; bh=MB8EyqF4EyHiUaRDRN+d39hbWThfxmuKqkS/NU6VQIc=; b=AWMDoijeYVYd0kD1y5XzkNz+uMbiRB6DU3W5SXQqbv73uEwS47yZAx5dAJ97o6UjGlhAwU7+o4EELnw5sAhXdVDvgzxInX/Qm4C5Rlvc9FCI91arYlTvWvNJgv8voueumAWF3okxE69et6003d7ieVESnCqmjwc8W9EQhJFEkyI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=KC7SfFVgCbb8T/un0bC0GdYmJKSIIDwoIbW7D61qXKfUR9kQTmrGTfCClOvajyhq8q8TREB2dXwixUUMuqupEvvaeIa1f6b8FIlq37DJ/6t45DqyqoscG2NIkQ+UlxQeIYo8DzvEAwFTBR9JHTniHxcvB/+i04Syacz2QTocwLI= Received: by 10.114.254.1 with SMTP id b1mr12043193wai.132.1211459547416; Thu, 22 May 2008 05:32:27 -0700 (PDT) Received: by 10.114.155.9 with HTTP; Thu, 22 May 2008 05:32:27 -0700 (PDT) Message-ID: <1d4e0c10805220532v514c2bd5je4c6c20ceb485495@mail.gmail.com> Date: Thu, 22 May 2008 14:32:27 +0200 From: "Guillaume Smet" To: pgsql-performance Subject: Index creation time and distribution MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/303 X-Sequence-Number: 30271 Hi -performance, I experienced this morning a performance problem when we imported a dump in a 8.1 database. The table is 5 millions rows large and when the dump creates an index on a specific text column called clazz it takes 27 minutes while on the other columns, it only takes a couple of seconds: LOG: duration: 1636301.317 ms statement: CREATE INDEX index_journal_clazz ON journal USING btree (clazz); LOG: duration: 20613.009 ms statement: CREATE INDEX index_journal_date ON journal USING btree (date); LOG: duration: 10653.290 ms statement: CREATE INDEX index_journal_modifieur ON journal USING btree (modifieur); LOG: duration: 15031.579 ms statement: CREATE INDEX index_journal_objectid ON journal USING btree (objectid); The only weird thing about this column is that 4.7 millions of rows have the exact same value. A partial index excluding this value is really fast to create but, as the database is used via JDBC and prepared statements, this index is totally useless (the plan is created before the BIND so it can't use the partial index). FWIW we can't use ?protocolVersion=2 with this application so it's not an option. As part of the deployment process of this application, we often need to drop/create/restore the database and 25 minutes is really longer than we can afford. So my questions are: - is the index creation time so correlated with the distribution? I was quite surprised by this behaviour. The time is essentially CPU time. - if not, what can I check to diagnose this problem? - IIRC, 8.3 could allow me to use the partial index as the query should be planned after the BIND (plans are unnamed). Am I right? Thanks for any input. -- Guillaume