Обсуждение: 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, isit 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 bothfields 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" -----------------------------------------------------------------------------------------------
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, isit 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 bothfields 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
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. See how.
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é
> 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. See how.
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.