[DOCS] Section "Routine Reindexing"
The section "Routine Reindexing" (21.2) has been reduced to: """ In some situations it is worthwhile to rebuild indexes periodically with the REINDEX command. (There is also contrib/reindexdb which can reindex an entire database.) However, PostgreSQL 7.4 has substantially reduced the need for this activity compared to earlier releases. """ Can someone add something that is of substantial value and less hand-wavy? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [DOCS] Section "Routine Reindexing"
Peter, > """ > In some situations it is worthwhile to rebuild indexes periodically with > the REINDEX command. (There is also contrib/reindexdb which can reindex an > entire database.) However, PostgreSQL 7.4 has substantially reduced the > need for this activity compared to earlier releases. > """ Is this for the main docs, or for the Annotated release notes? If for the main docs: PostgreSQL 7.4 manages deleted nodes from indexes on tables with many deletes and/or updates using the Free Space Map (FSM) as it does for the tables themselves (this is a new feature). If your max_fsm_pages setting in postgresql.conf is correctly set and you vacuum regularly, there should be no need for reindexing for most databases most of the time. If your settings are not correct or if your database usage pattern is one that FSM and vaccum cannot cope with, such as huge batch updates, then you will need to reindex some or all of your indexes on the updated fields periodically. You can determine this by watching for sluggish index response times and index growth out of proportion to the growth of the underlying table. In order to reindex an entire database, see the contrib/reindexdb module in the PostgreSQL source code. Please note that reindex exclusively locks each table while processing. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [DOCS] INNER JOINS in sql-select.html
At 5:56 PM -0500 10/30/03, Tom Lane wrote: Stefan Weiss <[EMAIL PROTECTED]> writes: From : > | A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same | as you get from listing the two items at the top level of FROM. CROSS | JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are | removed by qualification. I thought that by using the second form, you would be able to do 'explicit' joins, effectivly telling the planner in which order to join multiple tables (in case you have to join 10+ tables)? They are semantically equivalent, but not necessarily the same from a performance point of view. The potential performance issues are covered elsewhere; I think it would just obfuscate matters to try to include that topic here. You can imply the issue without obfuscating things. How about: A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same as you get from listing the two items at the top level of FROM. CROSS JOIN yields the same results as INNER JOIN ON (TRUE), that is, no rows are removed by qualification. -- The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [DOCS] INNER JOINS in sql-select.html
"Henry B. Hotz" <[EMAIL PROTECTED]> writes: > You can imply the issue without obfuscating things. How about: > A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same > as you get from listing the two items at the top level of FROM. > CROSS JOIN yields the same results as INNER JOIN ON (TRUE), that is, > no rows are removed by qualification. Okay, but that doesn't do the trick --- it implies that CROSS JOIN isn't equivalent to INNER JOIN ON (TRUE), when in fact they are equivalent, both as to result and performance characteristics. The issue at hand is that an explicit "a JOIN b" may not be equivalent to "FROM a, b". I reworded the passage as CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same result as you get from listing the two items at the top level of FROM, but restricted by the join condition (if any). CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. does that help? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [DOCS] INNER JOINS in sql-select.html
At 7:38 PM -0500 11/3/03, Tom Lane wrote: "Henry B. Hotz" <[EMAIL PROTECTED]> writes: You can imply the issue without obfuscating things. How about: A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same as you get from listing the two items at the top level of FROM. CROSS JOIN yields the same results as INNER JOIN ON (TRUE), that is, no rows are removed by qualification. Okay, but that doesn't do the trick --- it implies that CROSS JOIN isn't equivalent to INNER JOIN ON (TRUE), when in fact they are equivalent, both as to result and performance characteristics. The issue at hand is that an explicit "a JOIN b" may not be equivalent to "FROM a, b". I reworded the passage as CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same result as you get from listing the two items at the top level of FROM, but restricted by the join condition (if any). CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. does that help? 'sarright. I was just wordsmithing without worrying about the meaning. -- The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [DOCS] Annotated release notes
I'm hoping one of you an answer a question for me. I am accessing Service Center data via an Oracle ODBC driver for reporting and queries. How can I get the resolution field using these methods. Thanks, Catherine Carroll TSG Solutions Center Washington Mutual Bank 565 Lakeview Parkway Suite 250 Vernon Hills, IL 60061 847-549-3878 618-558-1678 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] Annotated release notes
Ms. Carroll, > I'm hoping one of you an answer a question for me. I am accessing > Service Center data via an Oracle ODBC driver for reporting and queries. > How can I get the resolution field using these methods. Howdy from one of your customers! I'm not certain how, exactly, you got the set of addresses to which you e-mailed. However, your response went to several PostgreSQL Database mailing lists. PostgreSQL is a different database than Oracle, and we cannot help you with Oracle problems. If, however, WAMU decides that you want to evaluate switching from Oracle to PostgreSQL, we'd be more than happy to help you. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
