1414alink ="#0000ff ">
1515< H1 > Frequently Asked Questions (FAQ) for PostgreSQL</ H1 >
1616
17- < P > Last updated:Tue Feb 26 23:52:13 EST 2002</ P >
17+ < P > Last updated:Sun Mar 3 11:02:16 EST 2002</ P >
1818
1919< P > Current maintainer: Bruce Momjian (< A href =
2020 "mailto:pgman@candle.pha.pa.us "> pgman@candle.pha.pa.us</ A > )< BR >
@@ -72,7 +72,8 @@ <H2 align="center">Administrative Questions</H2>
7272 get< I > IpcMemoryCreate</ I > errors. Why?< BR >
7373< A href ="#3.4 "> 3.4</ A > ) When I try to start< I > postmaster</ I > , I
7474 get< I > IpcSemaphoreCreate</ I > errors. Why?< BR >
75- < A href ="#3.5 "> 3.5</ A > ) How do I control connections from other hosts?< BR >
75+ < A href ="#3.5 "> 3.5</ A > ) How do I control connections from other
76+ hosts?< BR >
7677< A href ="#3.6 "> 3.6</ A > ) How do I tune the database engine for
7778 better performance?< BR >
7879< A href ="#3.7 "> 3.7</ A > ) What debugging features are available?< BR >
@@ -116,9 +117,9 @@ <H2 align="center">Operational Questions</H2>
116117< SMALL > SERIAL</ SMALL > insert?< BR >
117118< A href ="#4.15.3 "> 4.15.3</ A > ) Don't< I > currval()</ I > and
118119< I > nextval()</ I > lead to a race condition with other users?< BR >
119- < A href ="#4.15.4 "> 4.15.4</ A > ) Why aren't my sequence numbers reused
120- on transaction abort? Why are there gaps in the numbering of my
121- sequence/SERIAL column?< BR >
120+ < A href ="#4.15.4 "> 4.15.4</ A > ) Why aren't my sequence numbers
121+ reused on transaction abort? Why are there gaps in the numbering of
122+ my sequence/SERIAL column?< BR >
122123< A href ="#4.16 "> 4.16</ A > ) What is an< SMALL > OID</ SMALL > ? What is a
123124< SMALL > TID</ SMALL > ?< BR >
124125< A href ="#4.17 "> 4.17</ A > ) What is the meaning of some of the terms
@@ -213,9 +214,9 @@ <H4><A name="1.2">1.2</A>) What is the copyright on
213214 UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
214215 SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.</ P >
215216
216- < P > The above is the BSD license, the classic open-source license. It
217- has no restrictions on how the source code may be used. it
218- and have no intention of changing it.</ P >
217+ < P > The above is the BSD license, the classic open-source license.
218+ It has no restrictions on how the source code may be used. We like
219+ it and have no intention of changing it.</ P >
219220
220221< H4 > < A name ="1.3 "> 1.3</ A > ) What Unix platforms does PostgreSQL run
221222 on?</ H4 >
@@ -326,9 +327,11 @@ <H4><A name="1.8">1.8</A>) What documentation is available?</H4>
326327 "http://www.PostgreSQL.org/docs/awbook.html "> http://www.PostgreSQL.org/docs/awbook.html</ A >
327328 and< A href =
328329 "http://www.commandprompt.com/ppbook/ "> http://www.commandprompt.com/ppbook/</ A > .
329- There is a list of PostgreSQL books available for purchase at< A href =
330+ There is a list of PostgreSQL books available for purchase at< A
331+ href =
330332 "http://www.postgresql.org/books/ "> http://www.postgresql.org/books/</ A > .
331- There is also a collection of PostgreSQL technical articles at< A href =
333+ There is also a collection of PostgreSQL technical articles at< A
334+ href =
332335 "http://techdocs.postgresql.org/ "> http://techdocs.postgresql.org/</ A > .</ P >
333336
334337< P > < I > psql</ I > has some nice \d commands to show information about
@@ -348,9 +351,9 @@ <H4><A name="1.10">1.10</A>) How can I learn
348351
349352< P > The PostgreSQL book at< A href =
350353 "http://www.PostgreSQL.org/docs/awbook.html "> http://www.PostgreSQL.org/docs/awbook.html</ A >
351- teaches< SMALL > SQL</ SMALL > . There is another PostgreSQL book at
352- < A href =" http://www.commandprompt.com/ppbook/ " >
353- http://www.commandprompt.com/ppbook.</ A >
354+ teaches< SMALL > SQL</ SMALL > . There is another PostgreSQL book at< A
355+ href =
356+ " http://www.commandprompt.com/ppbook/ " > http://www.commandprompt.com/ppbook.</ A >
354357 There is a nice tutorial at< A href =
355358 "http://www.intermedia.net/support/sql/sqltut.shtm "> http://www.intermedia.net/support/sql/sqltut.shtm,</ A >
356359 at< A href =
@@ -856,14 +859,14 @@ <H4><A name="4.5">4.5</A>) What is the maximum size for a row, a
856859< H4 > < A name ="4.6 "> 4.6</ A > ) How much database disk space is required
857860 to store data from a typical text file?</ H4 >
858861
859- < P > A PostgreSQL database may require up to five times the disk space
860- to store data from a text file.</ P >
862+ < P > A PostgreSQL database may require up to five times the disk
863+ space to store data from a text file.</ P >
861864
862865< P > As an example, consider a file of 100,000 lines with an integer
863- and text description on each line. Suppose the text string avergages
864- twenty bytes in length. The flat file would be 2.8 MB. The size
865- of the PostgreSQL database file containing this data can be
866- estimated as 6.4 MB:</ P >
866+ and text description on each line. Suppose the text string
867+ avergages twenty bytes in length. The flat file would be 2.8 MB.
868+ The size of the PostgreSQL database file containing this data can
869+ be estimated as 6.4 MB:</ P >
867870< PRE >
868871 36 bytes: each row header (approximate)
869872 24 bytes: one int field and one text filed
@@ -899,33 +902,33 @@ <H4><A name="4.7">4.7</A>) How do I find out what tables or indexes
899902
900903< H4 > < A name ="4.8 "> 4.8</ A > ) My queries are slow or don't make use of
901904 the indexes. Why?</ H4 >
902-
903- < P > PostgreSQL does not automatically maintain statistics.
904- V< SMALL > ACUUM</ SMALL > must be run to update the statistics. After
905- statistics are updated, the optimizer knows how many rows in the
906- table, and can better decide if it should use indexes. Note that
907- the optimizer does not use indexes in cases when the table is small
908- because a sequential scan would be faster.</ P >
909-
910- < P > For column-specific optimization statistics, use< SMALL > VACUUM
911- ANALYZE.</ SMALL > V< SMALL > ACUUM ANALYZE</ SMALL > is important for
912- complex multijoin queries, so the optimizer can estimate the number
913- of rows returned from each table, and choose the proper join order.
914- The backend does not keep track of column statistics on its own, so
915- < SMALL > VACUUM ANALYZE</ SMALL > must be run to collect them
916- periodically.</ P >
917-
918- < P > Indexes are usually not used for< SMALL > ORDER BY</ SMALL > or
919- joins. A sequential scan followed by an explicit sort is faster
920- than an indexscan of all tuples of a large table. This is because
921- random disk access is very slow.</ P >
905+ Indexes are not automatically used by every query. Indexes are only
906+ used if the table is larger than a minimum size, and the index
907+ selects only a small percentage of the rows in the table. This is
908+ because the random disk access caused by an index scan is sometimes
909+ slower than a straight read through the table, or sequential scan.
910+
911+ < P > To determine if an index should be used, PostgreSQL must have
912+ statistics about the table. These statistics are collected using
913+ < SMALL > VACUUM ANALYZE</ SMALL > , or simply< SMALL > ANALYZE</ SMALL > .
914+ Using statistics, the optimizer knows how many rows are in the
915+ table, and can better determine if indexes should be used.
916+ Statistics are also valuable in determining optimal join order and
917+ join methods. Statistics collection should be performed
918+ periodically as the contents of the table change.</ P >
919+
920+ < P > Indexes are normally not used for< SMALL > ORDER BY</ SMALL > or to
921+ perform joins. A sequential scan followed by an explicit sort is
922+ usually faster than an index scan of a large table.</ P >
923+ However,< SMALL > LIMIT</ SMALL > combined with< SMALL > ORDER BY</ SMALL >
924+ often will use an index because only a small portion of the table
925+ is returned.
922926
923927< P > When using wild-card operators such as< SMALL > LIKE</ SMALL > or
924928< I > ~</ I > , indexes can only be used if the beginning of the search
925- is anchored to the start of the string. So, to use indexes,
926- < SMALL > LIKE</ SMALL > searches should not begin with< I > %</ I > , and
927- < I > ~</ I > (regular expression searches) should start with
928- < I > ^</ I > .</ P >
929+ is anchored to the start of the string. Therefore, to use indexes,
930+ < SMALL > LIKE</ SMALL > patterns must not start with< I > %</ I > , and
931+ < I > ~</ I > (regular expression) patterns must start with< I > ^</ I > .</ P >
929932
930933< H4 > < A name ="4.9 "> 4.9</ A > ) How do I see how the query optimizer is
931934 evaluating my query?</ H4 >
@@ -1091,13 +1094,14 @@ <H4><A name="4.15.3">4.15.3</A>) Don't <I>currval()</I> and
10911094< P > No. Currval() returns the current value assigned by your
10921095 backend, not by all users.</ P >
10931096
1094- < H4 > < A name ="4.15.4 "> 4.15.4</ A > ) Why aren't my sequence numbers reused
1095- on transaction abort? Why are there gaps in the numbering of my
1096- sequence/SERIAL column?</ H4 >
1097+ < H4 > < A name ="4.15.4 "> 4.15.4</ A > ) Why aren't my sequence numbers
1098+ reused on transaction abort? Why are there gaps in the numbering of
1099+ my sequence/SERIAL column?</ H4 >
10971100
10981101< P > To improve concurrency, sequence values are given out to running
10991102 transactions as needed and are not locked until the transaction
1100- completes. This causes gaps in numbering from aborted transactions.
1103+ completes. This causes gaps in numbering from aborted
1104+ transactions.</ P >
11011105
11021106< H4 > < A name ="4.16 "> 4.16</ A > ) What is an< SMALL > OID</ SMALL > ? What is
11031107 a< SMALL > TID</ SMALL > ?</ H4 >