Thread

1. Creating indexes?

   Bjørn T Johansen <btj@havleik.no> — 2010-04-22T21:36:51Z

   What is the best approach for PostgreSQL when creating indexes? E.g I have two fields in a table that I want indexed, is it best to create one index
   combining the two fields or creating one for each field? If I create one for each field, will the search when using both fields be slower that a
   combined index?
   
   
   Regards,
   
   BTJ
   
   -- 
   -----------------------------------------------------------------------------------------------
   Bjørn T Johansen
   
   btj@havleik.no
   -----------------------------------------------------------------------------------------------
   Someone wrote:
   "I understand that if you play a Windows CD backwards you hear strange Satanic messages"
   To which someone replied:
   "It's even worse than that; play it forwards and it installs Windows"
   -----------------------------------------------------------------------------------------------
   
   

2. Re: Creating indexes?

   Greg Smith <greg@2ndquadrant.com> — 2010-04-22T21:53:02Z

   Bjørn T Johansen wrote:
   > What is the best approach for PostgreSQL when creating indexes? E.g I have two fields in a table that I want indexed, is it best to create one index
   > combining the two fields or creating one for each field? If I create one for each field, will the search when using both fields be slower that a
   > combined index?
   >   
   
   http://www.postgresql.org/docs/current/static/indexes-bitmap-scans.html 
   covers the basis here.  There is some amount of additional overhead in 
   keeping around and using two indexes on a single field as opposed to one 
   two-field index, particularly in terms of disk space used.  However, the 
   result is enormously more flexible.  Having two single-column indexes 
   can satisfy all sorts of queries that the combined index wouldn't be a 
   help on, and the overhead of combining the index results together is low.
   
   -- 
   Greg Smith  2ndQuadrant US  Baltimore, MD
   PostgreSQL Training, Services and Support
   greg@2ndQuadrant.com   www.2ndQuadrant.us
   
   
   

3. Re: Creating indexes?

   Martin Gainty <mgainty@hotmail.com> — 2010-04-22T23:44:00Z

   concatenated indexes deliver faster results as concatenated indexes do not require the resultsets to be merged 
   provided the more selective column of the concatenated index is first and least selective column of the concatenated index is last
   http://webglobalnet.net/support/index.php?topic=864.0
   
   HTH
   Martin Gainty 
   “I shall take all the troubles of the past, all the
   disappointments, all the headaches, and I shall pack them in a bag and
   toss them in the East River.”  - Trygve Lie
   ______________________________________________ 
   Note de déni et de confidentialité
    Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
   
   
   
   
   > Date: Thu, 22 Apr 2010 23:36:51 +0200
   > From: btj@havleik.no
   > To: pgsql-general@postgresql.org
   > Subject: [GENERAL] Creating indexes?
   > 
   > What is the best approach for PostgreSQL when creating indexes? E.g I have two fields in a table that I want indexed, is it best to create one index
   > combining the two fields or creating one for each field? If I create one for each field, will the search when using both fields be slower that a
   > combined index?
   > 
   > 
   > Regards,
   > 
   > BTJ
   > 
   > -- 
   > -----------------------------------------------------------------------------------------------
   > Bjørn T Johansen
   > 
   > btj@havleik.no
   > -----------------------------------------------------------------------------------------------
   > Someone wrote:
   > "I understand that if you play a Windows CD backwards you hear strange Satanic messages"
   > To which someone replied:
   > "It's even worse than that; play it forwards and it installs Windows"
   > -----------------------------------------------------------------------------------------------
   > 
   > -- 
   > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-general
    		 	   		  
   _________________________________________________________________
   Hotmail is redefining busy with tools for the New Busy. Get more from your inbox.
   http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2

4. Re: Creating indexes?

   Adrian von Bidder <avbidder@fortytwo.ch> — 2010-04-23T07:53:55Z

   On Thursday 22 April 2010 23.36:51 Bjørn T Johansen wrote:
   > E.g I have two fields in a table that I want indexed, is it best to
   > create one index combining the two fields or creating one for each
   > field?
   
   This depends on the queries you run against the table.  It's not possible to 
   give a general answer here.
   
   cheers
   -- vbi
   
   -- 
   How to overclock the board to the attachment from Windows?
   
   You should telnet from the floppy disk and from the tools menu inside
   Netscape you either never have to log from the POP3 miditower, or can't
   debug a clock of a OpenGL file of a software of a BIOS in order to
   explore the editor.