Neil Conway gave a talk on the internals of the PostgreSQL Query Optimizer at linux.conf.au this month. He has made the slides available and encourages Corrections or suggestions for improvement. The material is in the public domain.

This is an excellent presentation of what the PostgreSQL optimizer is and how it works. This is the best English explanation I've seen or heard of the optimizer. Neil's text validates and enhances the understanding I have had with regards to the inner workings of the optimizer without adding to the confusion possible when describing a complex system.

Understanding the optimizer is important in order to tune queries. But the contradiction is that a very good optimizer, as ours is, should probably not be second guessed often. Yet we all want to do so. Neil only touches on techniques to give direction to the optimizer, but the focus of the presentation is to describe the optimizer.

Contributors: Neil Conway neilc at samurai.com, elein at varlena.com

Seeing the Table Definitions [GENERAL] DDL from psql console? 25-Apr-2005

You can use the options of pg_dump to see the specific Data Definition Language (DDL) used to define tables. The command pg_dump -s -t tablename databasename pg_dump -s -t detail_orders elein -- -- PostgreSQL database dump -- SET client_encoding = 'LATIN1'; SET check_function_bodies = false; SET client_min_messages = warning; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = true; -- -- Name: detail_order; Type: TABLE; -- Schema: public; Owner: elein; Tablespace: -- CREATE TABLE detail_order ( ono bigint NOT NULL, dno serial NOT NULL, ino integer NOT NULL, qty integer, tot_price numeric ); ALTER TABLE public.detail_order OWNER TO elein; -- -- Name: detail_order_pkey; Type: CONSTRAINT; -- Schema: public; Owner: elein; Tablespace: -- ALTER TABLE ONLY detail_order ADD CONSTRAINT detail_order_pkey PRIMARY KEY (ono, ino); ALTER INDEX public.detail_order_pkey OWNER TO elein; -- -- Name: noupd_detailkey; Type: TRIGGER; -- Schema: public; Owner: elein -- CREATE TRIGGER noupd_detailkey BEFORE UPDATE ON detail_order FOR EACH ROW EXECUTE PROCEDURE detail_keycheck(); -- -- Name: detail_order_ino_fkey; Type: FK CONSTRAINT; -- Schema: public; Owner: elein -- ALTER TABLE ONLY detail_order ADD CONSTRAINT detail_order_ino_fkey FOREIGN KEY (ino) REFERENCES inventory(ino); -- -- Name: detail_order_ono_fkey; Type: FK CONSTRAINT; -- Schema: public; Owner: elein -- ALTER TABLE ONLY detail_order ADD CONSTRAINT detail_order_ono_fkey FOREIGN KEY (ono) REFERENCES master_order(ono); -- -- PostgreSQL database dump complete --

The original question in this thread was how to show this DDL in psql. The answer, of course, is to execute a shell command from the psql prompt.

   db=>\! pg_dump -s -t detail_order elein

Contributors: John Browne jkbrowne at gmail.com, Thomas F.O'Connell tfo at sitening.com, Scott Marlowe smarlowe at g2switchworks.com, Michael Fuhr mike at fuhr.org, Bruce Momjian pgman at candle.pha.pa.us, Tom Lane tgl at sss.pgh.pa.us

Catching Errors from psql [SQL] Sending errors from psql to error file 12-Aug-2004

A pg_dump file used for upgrades sometimes can be difficult if it is big and when the schema and/or application changes at the same time. In these cases it is helpful to track the ERROR messages from psql in a separate file so that many errors can be sorted out and corrected at once.

To do this we use the appropriate shell commands. For example:

   zcat cli_postDataInserts.sql.gz |
      psql -d cli_post -U system --echo-queries -f - >trace.file 2>&1

In this command we unzip the dump file and pipe it into psql into the database cli_post with the user system. Catching only the error message without context is not really very helpful so we add the flag --echo-queries. The -f flag sends the output to a file, in this case standard out with is signified with a dash -. Standard out (>) is redirected to the file called trace.file and standard error (2>) is redirected to the file pointer for standard in (&1).

You can use variations of the standard error and standard output to catch different output from psql. Just remember than the ERROR statements go to standard out.

Contributors: Devin Whalen Oliver Elphick Richard Huxton