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 E808D52828 for ; Tue, 21 Jun 2005 13:44:09 -0300 (ADT) 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 52218-01 for ; Tue, 21 Jun 2005 16:44:02 +0000 (GMT) Received: from apate.telenet-ops.be (apate.telenet-ops.be [195.130.132.57]) by svr1.postgresql.org (Postfix) with ESMTP id F15295292B for ; Tue, 21 Jun 2005 13:44:01 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by apate.telenet-ops.be (Postfix) with SMTP id 436083807D for ; Tue, 21 Jun 2005 18:44:01 +0200 (CEST) Received: from [10.0.1.11] (d5152B1F3.access.telenet.be [81.82.177.243]) by apate.telenet-ops.be (Postfix) with ESMTP id 68BAA3820E for ; Tue, 21 Jun 2005 18:44:00 +0200 (CEST) Mime-Version: 1.0 (Apple Message framework v622) In-Reply-To: <42B83708.2050706@arbash-meinel.com> References: <098952ec5af9b040acb9b6b5af743abd@implements.be> <42B830B3.6060902@arbash-meinel.com> <821370985653dc1f4166ca634f642ce2@implements.be> <42B83708.2050706@arbash-meinel.com> Content-Type: multipart/mixed; boundary=Apple-Mail-141--668882002 Message-Id: <09f30bc29a91c94eecaf7f70aa0c8399@implements.be> From: Yves Vindevogel Subject: Re: Another question on indexes (drop and recreate) Date: Tue, 21 Jun 2005 18:43:58 +0200 To: pgsql-performance@postgresql.org X-Mailer: Apple Mail (2.622) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200506/400 X-Sequence-Number: 13037 --Apple-Mail-141--668882002 Content-Type: multipart/alternative; boundary=Apple-Mail-142--668882002 --Apple-Mail-142--668882002 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=ISO-8859-1; format=flowed I only add records, and most of the values are "random" Except the columns for dates, .... On 21 Jun 2005, at 17:49, John A Meinel wrote: > Yves Vindevogel wrote: > >> And, after let's say a week, would that index still be optimal or >> would it be a good idea to drop it in the weekend and recreate it. > > > It depends a little bit on the postgres version you are using. If you > are only ever adding to the table, and you are not updating it or > deleting from it, I think the index is always optimal. > Once you start deleting from it there are a few cases where older > versions would not properly re-use the empty entries, requiring a > REINDEX. (Deleting low numbers and always adding high numbers was one=20= > of > the cases) > > However, I believe that as long as you vacuum often enough, so that = the > system knows where the unused entries are, you don't ever have to drop > and re-create the index. > > John > =3D:-> > > Met vriendelijke groeten, Bien =E0 vous, Kind regards, Yves Vindevogel Implements --Apple-Mail-142--668882002 Content-Transfer-Encoding: quoted-printable Content-Type: text/enriched; charset=ISO-8859-1 I only add records, and most of the values are "random" Except the columns for dates, .... On 21 Jun 2005, at 17:49, John A Meinel wrote: Yves Vindevogel wrote: And, after let's say a week, would that index still be optimal or would it be a good idea to drop it in the weekend and recreate it. It depends a little bit on the postgres version you are using. If you are only ever adding to the table, and you are not updating it or deleting from it, I think the index is always optimal. Once you start deleting from it there are a few cases where older versions would not properly re-use the empty entries, requiring a REINDEX. (Deleting low numbers and always adding high numbers was one of the cases) However, I believe that as long as you vacuum often enough, so that the system knows where the unused entries are, you don't ever have to drop and re-create the index. John =3D:-> Met vriendelijke groeten, Bien =E0 vous, Kind regards, Yves Vindevogel Implements = --Apple-Mail-142--668882002-- --Apple-Mail-141--668882002 Content-Transfer-Encoding: base64 Content-Type: image/tiff; x-unix-mode=0666; name="Pasted Graphic 2.tiff" Content-Disposition: inline; filename="Pasted Graphic 2.tiff" TU0AKgAAFciAP6BP5/wWDQeEQmFQuGQ2HQ+FP5+v9rOh5P9IMBuP8zK9tP8fJNlP8QoNjP8FndhP 8MnyVjJHSMrqFnv9BL1vP9ett2v98PuJxChUOiUWGQOCUalUujOV4PZ/qBluR/mdVtJ/jVHst/ho 9MF/g08WABHJfv8AWa0WoAHFfWuzgg6sB/hY9ysUohjv8qqVqv9LMZxv9ouV40zEYmHUjFY2HPx+ RNtOl4Rhftt/phiRs6LC/iA5LewnJeWg46W22+02cAHOwa26bC0HPY7TZ7G2G9dv8EHFdP8Om9YP 8rKJov9UtFzv9Dzh/sRvu5/vZ9PzHdeF4zsUqgQRlOR5v9DMBwv8uqe/jZIVwNHu6B0/WAKHvWGr RAA1ra0G1c/vfgANrdraXrTQIAA6JW2TUrQNz/ja0oAjO4YAjSWS0DOWawjys4RkIk4Lj2sAWEUZ B/ieUJqH+PZdnAf5aG06R5nsfLtqW7UaoWZJwukwLyi4VRsn+F5HGYf8QJWBQ7JWAI5tYti2Dm1T bQHAq0De1Q2FwtA1Fof4AjVDIBDQV0vDQWK0DY/oADYWsrNQN81NOuDZtYOM6ycs4Cjq+Q9JWE5D pGJpOpqP5dMwW5sHUf53xlHCExupjqoIbh3Hu55vp6ch3Hof4wlMrAdEWYh/gmP1RtXK0DDi2rXy nVzXjpVs8NM1g1vuNU2gANEugGMBRwu4YADTNsvwyAA3v+0k6LRWLb2dBUpjk2I4NVOx/gYPC6BU QiwCgSsSlWZpxH+ahzsOaBzvCe7qoMpKlUghd1n2f5oHId5/lQaJ0n+RBf3GLpUGwf4iksrg0FVF ICzlXT9QBB0tWO/+FjlKVWtfijTNRBsvDbhrdP3NUAwY38KFfkc0PuNjRWLLz8ytiTUVdVlnjlA2 MWPAWRAAM8LAAMjhhuR5ir4ULjigT5pn+P5co2T6pn+YBup6dp6xoh6kHie95modNOFkah0H+Qhd pyKBNmcf68r2Bo9GGtdpwIAUotM1Q4QMNzUDZhuVZbLsvy7KkqZZCnAb4AFcQY1C3LW1Wb5lOa2t ZvUt78NNjcMN83wEOMDczvuTzXNQ3QEN01WRL2b8XBYEDuugQEFUYgkpIo5FjIJTmhfZkHG8J0nm fB/sifp/EQYLBioUKsBkRKVgWPLX7stA4VnaDa+tZlZel0j+gCMpWS8MRULQNeIDY/4zTIAIyFX0 GFybZnr+rZ8p2a1s6tUNfADXXI1Yb8jLX+MNQk+1Az7zWqwVa/FaJrHpvSLeAQOhdE/l7CSJwrAb BXDXH+PkyA/gUiPGaftASWW6JeDgnGAsCmLNvcYqlz5+FchnZMrqGbeE3JoYhAMAKbEvOGeigtaE CDZwJcgxtiML0uMgQYzllKt2GhtP/EaH6cgAm+fnCtVEVXFOKNU54AC1B/tyLfBx4YJBGwhAAG5m yUoGH/TgxlZ7nGXw3YdEpYTPWOvaSq/JVEX28rEDQsGKDoGPx+QYnE1URg2paZY4dy7EHTx9cc41 upqIfwkjSgJWyW0MgBdK9I1DGAAwnS8G8+4cGYyUWWbKMg/gSiOjQG+FMQ44qqlmgBNUSYYMohKW 2ULFZaR8NdMFucoz/w/ZcAAMzgH+j/AKGV8QAZkhmWNM0AJ/I5zCiJEGYjF1qoGmxI5MqwT7JVlG buLULJhPXlbK+WMt3HwHmIbF8xaA0n6mvPgN7EJhx9Vcax+hsX6wGZuldNDnTSgQDoloGohS6BYE 62gPQqyRhlFE2gIYkCRgdD4WcAUsnxvlYklKgUCn4upi29JkkpG/pbYgtKbr86TLOnbLCF066ZLP kGsI+7NY9rRjZHB+0ew4M5S0AKT4FA8l0BaIUk4XHkj/EYLwzAtVyj/GwRYf49B8D6H+OMdw9R/j DG8vcTgx1xhpFTBoHYilRgaDygQAobXCynVVSSVdRYTLJls5CcIaqX0zpw/Kms76c2DSnTsNNPaA TbpggqOSCg4MkQwcAPiBAzimJqI4Xg3R/itX0P8bY66xD7MiYkgg5h4vAF2NgdY/xNGCH+HQVMIQ WiCNVM2L6Ap4s3ShFixMubA2HnjcUulhab3GuIbGxVjLlxXZoawMKFgsCiaSOUd6nB6XbH+PofKN LtqcH7eMf5AyimQOtegf4/TIEFvYP8dyjTm2eV1KdmNgrlIKr/cOxF+LjmRldTZY88L/U6P/YtyF +b82PDAhkOQsyNkHu8jS76NB4YXH+PvDUG7v3lIFeu8mFbuj6q8PXE2HEaXqIEUEgxnh2T2YhD/B WBZcUunVjO59yMB3PsHc3BONLemxwaP8NwsTMEPvVeMieJKvXmHZk8f48h5GHyYP8e+V3g3tvMQs VY01FLCxjgTHk8b943yBTOVoJEiXJzPgae1zr+2HwYhnIuRzrjczwP8eY8zDmKy7l8NOYcx40zKW nNswizytg+kXHc/qS48x9o7QbNMhZ0yMdvPBG7wmNz/jB6WYs436uFmbHhbFrAFgjhl4Y/g5IvH+ 0EkYFw+NuAHqmoet9bWJwPnDHEfM55E0udjTNW7uZ+y9p6L+oNe3Bxtoa4lACzgQee2kQ5Jwo1RK QPQfK8xejaJ6FG6w/wEh1NZJ+L6ydoH+zfqTUK0dK7AzsY7Yem9jaA0Fu2wVf9eJObqW+TxuwbCS K4JEYVnh2O/vWQM5K+wtCehCCAP5YABybV035xCAJIJqnrTyFqB9H8e1Lu/OumM870MTp3MGn9J8 g5Zsxhk2ZURLfGmrBAEg5m/CISIf4jRekblaCgRoyS0BjWNZVw6uZ70hnsrkND/Fbn3kWWtmz1ID a5NxyLYJ1xujdI3iZTm9dkYy4/P9yFKIcFoDQsbtKW4nT2YbLrpr450V6zTGem7N+YzXVzDZ0PSl ddIb8GQVrlD9sQjy4FKHVy0ZDDeLHCPWuuD/68Y0Vg1LX8c0bQSBiAq9JrVv2pYwZkzq6mqxDi8y XJgBkjZGftMMdUgm5PKyCBnogBI72hYgZlgrD6WWgMzPZkxGdUzfIfIzsdb66PXr/Jxp+XwRGlOL GnuJm2b0l7vo/dJeDL4N7qwWN+qn5CqedNMATuzZmObaad1s4dBMl/6wmG/Pt1+oAAYk2hToiuQc 5FxyjyRoHqH0xYKE+Q8k+UIgH0KAH+HSHqXmGwHSIuEGF0I25eWE8A96/g2akwhIk8Puf25mpi3w /IeG/M8y0O10/WgM3cpUS8l0mS+ocOYgDQS0ASDUTOB6EaJWDkXyXwFyL+GqGkPKHQHMOkxIXmIO HAHAJyyuKgvGIIHgU2H+G0GuHKH+FwF6KwEAtAH+CUEcLOAiNC2QnKcOZSZCmO9ongNep2sA3Y2W nY/KwE9hDazc8w8Sug9mh6cAZ0/UAEC0FKH+AOCOEoJQBeD8H+A2A+DYYGBuD2H+D6DmFCH+F0Fq hDB+PKGcGaaSGsGmJyGOGGg0EuEgP0CqCOEOH+BIA+DQH+AgBSDrD+CAEcH+AICwFNA+pamupfDQ /G1E2asfDksJDesNBCv012x+5WDmQSseeiAaCGEiH+BEAmC0H+A8AcCyH+A4AXGqAqAOCsLqAQCw H+BMAuDIH+CJEWPMCpFKCaCEEASEBFESA2AaC2H+AoAOCqK6AVGqA8AYCvGcAiC9HmBlHZBglBDt F80LF7BNDdBHDg2U0JGI0lGEseWsAgB6EYH+BUAkC4H+BKAuDDGcArH+BCAnH+A8AeC6K7HjG7H4 AoANHtG1G4A1GxGkAfHkBAAjJOBCAqC/FMAuDAH+BOApJ8A0BYDy2aWtGEzI1G2dKRIUwDGDF9BP DooBISnVImB9IsBRJHI2AuDHK3HHI4DFK3K6BGArI8BAAlJ8A8AhJqAlJxJBK3LDLBK9LmBNKCH+ AwBZEZIGcjGM3zKVIRL7BEH8zVKfKojrKlMCQVIk2jKuH/KzH/I5K7MjLFMpMnI5HGBIAvG/I5J9 MvMrK5M/K7LrJ9LxL1DXL5KYp1L/MM80g2wABMzWAGjVDZIc/XDrIiNjKtKxK1MtNBN7MlN9HFJ5 M3J7NDONMnNHLvLzKNKnMS5dNPMAsGpgLKLoAQNswAH8EAGAXGB0EobQAwEEJOblOa1uzJIfNvF9 MXFVMbMfOPODOBOBMxM1K3M7PfPdNFLtNLOZOcfjIOgVPIfeAaD2VGBUEY6ECyFQL+awXWH+HCHa KgW+KoAqDsPu3Mt8rxMO+fOjPTNzMZN3MhPtN/M/PlOJPrPhPvHBPzOXL3KPKhP69Mv4SmYWWs4m QyDGFUGsH+GeHEOkHW204SIED6FmaSBqeZHmQ3Fikw7iPwZWkCSs40Yg0CwTQAxpPUAhPZN5RDS1 PjOHPpRRRFOTP1RZSo5W1skw6MDa6g5omYZW94AYDkN+BOD+NKCoE6SKHy1WA8EOL2Z8cuhGTU/f SlArAu95L2cmiO1xSrQ7PXQ/S/S1RJS9RFUlNBTDRXNPRavw3St25kTXBiQsADBfUFNO/fUC6K+A N+9fTI2SkOfG6YmY9MZWDEFS+0TInFUQSfPI1NQ9MdSzRPUnS5MzRLUdRPUqD02RRaeoseDdSaQn ScQi9G/kPu+e/o+E8UQUoGv/IXMKsOfqZu8O/eV2S89E7QZ7XG8w/edGQYxiTeoSB3FKBQAlRBV9 S3LnWDUjXpN7LCBOAnJ8AyBXKKV0Z7KPKO6i764rXI9wS8DJVq6TAw+hP5MFBJDi0Ofq0i86mQPv XMDRWlBi6QVy6iAYCUEuJIBDESBGA0DNI2AxLjRCAxS4BGAvH5I5J2BLZdZVV9ZtZqDKJIA+DUH+ AiB4ES2a4pDFDE+fVIkg84ZylyTUn7RdYjIZYhQy14fipY9SDTWhSjRg6UAEC+fZGWEfEMBCDbFM AtJ9MzI9LlM8BFZjJ5ZpNBMzLCBJLKH+A+A3ZSAiByEMmcC0FO7Q7eTVSW+ectBY9HQ2oFDTNXak Ns9fIa0hPPVUfqfq5i3OjohIAEC6fYAWB6ERFUBSDxLuAtZSA7JSA/JoH/LPH+BIAtK7bZG4BGAt JCApH+A+AhJPGnJOA0AfH+AiBIDoWuBzc6AICvD8PxUAWJWWj2sfWwsPP9cXWzKc/PGE0jPQvwiE 9kWsAeB+EVI2AaCpdQAbGjGnHkA4AbJPHhI0A2AZI0A6AdI1drG+A6AfG/HhGjfVI1fLfJGpfBGq BGAZHsAvKJA+j7eZencVNSlZGBelINchalStIqbTV7J5I8BDLtdrJPfLfSAZHkA2AXfPfXENJTfl JrK0BFdjMoBOArX7UsYhUxaledgRcZgVBLMTepVU17StSxXlMrZzbgAtLDgrdoAlGiBBK1dhZZLi AxK/OFORRVNNhbhu0PhhafgTW1gXilgbgRhzUbV/OOAwDPFMAxe/ZrPriXXnWJidP3hiuZgPiphk eGA8EUJGWEc3MNhtgdUXSvi5XxS1Z3Zhe/MzbfjNRRUrieVpeejtOg0OseDeNYQexAIEE4GcX2B4 EkRKAMDsNjXSTWcVVVjvi1jzh1WHM/j8AvkAAvkFlHkLjVjdjZF4pPk0nANKLKQIBCESL2D6F6XG XgvWqwHQMqEsGIRaL6RSAgD6bcQAbyNRBkLQDCWNhdNxkPj1V5h3i7N7lLlPlTmtUpjTTHak1tUE DHDyQMDWN+ggQIByEiJOEGOcGIG8Okq7CMXay2KMG8HaU4rcMGCIE8aSBcEWL2Ci4ccggbKXhxlD j3jPPdmxbdLnm3PxNJhZkO1CseseDUNKBIEMJWB8EqhCBqEuGgH+DiFiL+GXQcINAEIhl4McIkXc IICurWWuDulCNiDW85VVi3mplHmuH/j/oZM9odRTohkNNQnjYIc6QIAILmH+BgEOLOtabCUcIPpV qiIOHMKeOQGoJ6C+FaMwAuEJT474DYTfliJQrbgjmrj5OBoXkDobrTJ/LsArX+LQDI72nBlkJQLv C2FBpAEoGOMGGhqsvKH7nlqpqlnpsKKMHgJ+tgGQPKCmFIaSAwD8LOA4EGJWBiCdEFH1G/iPp0Av rXlRrboTdXLDdrI0BSBkD4H/n8JOAkD7o0EwhCECF+PKG6HceBsQRtsPtyOuq4/+22RcFYJGBkBK Ddp4AtI8A2AdHkA/H9uPZZrVp5lNp9OFMzK7iLJ9HhHlgrJ8BSA3FSEWECe+vNsVnkXdt4MVqnvR qiHmHkKgF+F0JqEMD8fECQB4D7uPI8AyAZGjubJ8BAAqClbqAng4AbfZdsKyBTuMDiDOE2H+FmFe JGHMU1vXwrvVwrwxCKagF3pADyDiFEH+CWCAEHJ+A3G4B+BmDmH+DQC7EEFGE4NKHYHUXvwxxqIj t3xtxyIaw0ImGwGus8HgUZx1yGavxxyJyPyRySKIKQFuFibQGOGEYFyVynypymKQCaB/xIDkDOE8 IQFSFEVGE+EsQIIWGEF4L+EwEeP6IOE4EoN2DOC0ErpCDLy6DYDAEyH+FiFU6EHyq6H+DqDUFAH+ G4GwOWIWw0IIEMD68HzsE0H+DgDIE4H+DaDD0cG8G4X2FgFS6EFGE2LAIWG0GsHMH+EoEWTaIOFn uEH+DKCzzkDjzoH+DJ1aH+FuFgbRsGIIEKD2fZyCrEIOGUGMI2E8EqNLz6XmDsDXEgDUC6EwH+DW C9zx1ZzkF4FuOOHUq0D8Dnb8DeDHwd0h0kEeEKQsKQCkCLFKDv2SIQD6DkFJENg2H/ygYFxkPCBu BQDkH+CwCXIsIOCiCJb2CQB0D+H+FEE0LoEYEGTOCgCGEKH/2uMrGfI8GEF7RyIWHwOoH+CEBptU EaEITOFCEyLOF0FoJr16aUDpb9fbH+GAF0RSz2KgCABjdACSB4ED3V3YH+C8CjbCFAEwLP006EG8 G2X35LbnK6B/5iH+GcGSs8FoFehCC0CYEavXAT5WRSDyDdxACKBtvwFwFkJqHEHAUVxeLACSB3HZ 2GNKEyEgP6EsEaNEKQCn3MH+DuDZEgIOEUEAZMDuDbxB7jFL5zbCDQC2Et2cC/zwIP8AH+BoBKDf 1iCxEEC4CfFgEqEYNFCeU4BWA2DSXIGgXGIWu+XmCQB35r2R0EEgEOQyIWEOD88GECDvVmCyCZIs DACpGaE0EkN+DP8GIQEGDyFUH/3LFL0Ue+HkHh18IMHPwoCqCRe54ILoCMBvEKC6Cl8CC18IIWFa FKL2DGCtEEIXE0MH2+H+EKD4fZ10fZ374Z7h7l7p7sIMEF9eRd6d1p1t4H4L/ohCDECsEkIQIAVC MiX+dTUoH++n0/X++4W/4hEHe7Xo/xQGTO/zQXEu/0Cd1S/0Yglg/3g73q/yWPUE/zsa1C/0IeVW /0GeFU/3G4Xa/0MfVY/1or2bQlbRUiiVpJpQ/y+U0jEYggzzOSkRUM/0KfJqfzop3+p1Aw3+53I7 n+UCJWak3m26H+OxYdX+Xiij6leVRY3+YKheaks1Yy3+VCPBEIe5zM5qOxbdICAADgEAAAMAAAAB ADgAAAEBAAMAAAABAEAAAAECAAMAAAAEAAAWdgEDAAMAAAABAAUAAAEGAAMAAAABAAIAAAERAAQA AAABAAAACAEVAAMAAAABAAQAAAEWAAQAAAABAAAAkgEXAAQAAAABAAAVwAEaAAUAAAABAAAWfgEb AAUAAAABAAAWhgEcAAMAAAABAAEAAAEoAAMAAAABAAIAAAFSAAMAAAABAAEAAAAAAAAACAAIAAgA CAAK/IAAACcQAAr8gAAAJxA= --Apple-Mail-141--668882002 Content-Type: multipart/alternative; boundary=Apple-Mail-143--668882001 --Apple-Mail-143--668882001 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi. --Apple-Mail-143--668882001 Content-Transfer-Encoding: 7bit Content-Type: text/enriched; charset=US-ASCII Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi. --Apple-Mail-143--668882001-- --Apple-Mail-141--668882002--