There are several small and easily forgotten hints for helping the optimizer use the indexes. Here are two commonly mentioned.

Use the right datatype!
Make sure your data types are equivalent to ensure your indexes are used. Use explicit casting if necessary. (Issue #36.2)

When the index is on a bigint, for example, make sure the expression contains a bigint value to compare with. And remember, unquoted numbers are assumed to be integers (int4).

	bigintcol = 5 --> might not use the index
	bigintcol = 5::bigint --> is more likely to use the index.

Use the right functional index expression!
If you have a function index on what_year(mytimestamp) and then you query using

	2003 = mytimestamp

	2003 = what_year(mytimestamp)

Contributors: elein at varlena.com Francois Suter dba at paragraf.ch Martijn van Oosterhout kleptog at svana.org

When do I cast? Casting Types 25-Jul-2003

When you are tuning queries, it is important to know what datatypes can be cast to what datatypes and in what context. See Previous item in this Issue #36.1 There are three levels of possible casts between types.

• Explicit -- Only cast when Explicitly directed with :: or function syntax
• Assignments -- Like Explicit plus Implicitly cast for assignments and creating target lists
• Implicit -- Like Assignment plus Implicitly cast for expressions.

Explicit means you must direct the system to do the casting for you. Implicit means that the system will cast for you, depending on the context.

To find out which types cast to which types and how, you can query the system catalog table pg_cast. This view will do it for you:

   create view showcasts as
   select t.typname as source, t1.typname as target, p.proname as function, 
   (select case when c.castcontext = 'e'
      then 'Must use Explicit Cast'
      else ( select case when c.castcontext = 'i'
            then 'Implicit cast for expressions and assignments'
            else 'Implicit cast only for assignments'
            end)
      end ) as casttype
   from pg_cast c, pg_type t, pg_type t1, pg_proc p
   where c.castsource = t.oid and
      c.casttarget = t1.oid and
      c.castfunc = p.oid;

Contributors: elein at varlena.com

Notes from LinuxTag by Cornelia Boenigk 14-Jul-2003

LinuxTag 2003, 10. to 13. July in Germany

This was the third LinuxTag I attended. The very first thing I noticed when we came to the exhibition hall was the large number of people waiting at the entrance to get their ticket. This was something of a surprise since all big IT-events and exhibitions in Germany were attracting fewer exhibitors and fewer visitors than the years before. The Open Source movement seems to go the other way round. The LinuxTag team's press release stated that there were about 40% more visitors than last year.

PostgreSQL was represented at the exhibition by Credativ, the company of Michael Meskes, as in the years before. PostgreSQL was visible at their booth. He had a poster with the blue elephant on the wall.

MySQL had its own booth (like the last two years). At the booth were the 'MySQL'-label, people with MySQL-shirts, and MySQL-developers. And there were more talks and workshops. The MySQL community in Germany is strong but the PostgreSQL community here is not.

We had tried to find some PostgreSQL people for the PHP booth but unfortunately nobody replied. The plan was 'PHP and friends' but it ended up as PHP+MySQL, PHP+Apache and so on. The PHP people would not mention PostgreSQL because there was nobody there who could answer the visitor's questions.

Bruce Momjian introduced PostgreSQL at the open congress. He spoke about PostgreSQL: Past, Present and Future. He gave a short overview about the history of PostgreSQL and then turned over to the difference between closed source and open source. He explained the development process and pointed out that PostgreSQL is doing 'feature-based' releases instead of 'time-based' releases which pushes the improvement for software quality. He then gave few examples of areas in which PostgreSQL is used and he made us understood that PostgreSQL can be used in any application ;-)

Although there were three featured talks at the same time about 120 people were listening his talk and afterwards Bruce had to answer a lot of questions.

The keynote from Jon 'Maddog' Hall was titled "Things they Still do not understand". He spoke about the differences between open source development and proprietary development. He pointed out the advantages of open source development and the difficulties to get the managers convinced to use such products. e.g how to respond their questions about support.

Ken Coar, Apache Software Foundation and IBM, spoke about Commercial Involvement in Open Software. He explained how IBM and Apache are cooperating and how the cooperation took place.

Contributors: Cornelia Boenigk poppcorn at cornelia-boenigk.de

Leaky Queries cause Cartesian Products [GENERAL] Wacky query plan, why? 25-Jul-2003

When using an UPDATE...FROM, it is not necessary (or even recommended) to add the target table in the FROM clause. The target table is already implicitly a member of the FROM clause, sort of; its columns are available to the WHERE clause (although you must reference it using the full name and not an alias). Adding the target table explicitly in the FROM clause will give you another "copy" of the table with which to build the results. Consider the following case:

	UPDATE prod.t_results SET expdate=e.termdate 
	FROM work.termdate e, prod.t_results r 
	WHERE e.docid=r.docid;

	UPDATE prod.t_results SET expdate=(...something...);

	(SELECT e.termdate
	 FROM work.termdate e, prod.t_results r
	 WHERE e.docid = r.docid);

The scope of the where clause is changed when you remove the extra copy of prod.t_results and refer to the table you are updating directly.

	UPDATE prod.t_results SET expdate=e.termdate
	FROM work.termdate e
	WHERE prod.t_results.docid = e.docid;

	update prod.t_results set expdata=(...something...)

	select e.termdate from work.termdate
	where prod.t_results.docid=e.docid

Contributors: Maksim Likharev mlikharev at aurigin.com, Stephan Szabo sszabo at megazone.bigpanda.com

Showing Triggers [GENERAL] list triggers - how? 27-Jul-2003

This is a nice little view which will list triggers in your database. This is a very helpful addition to your pg_catalog utilities because there is no psql utility to show triggers individually. Triggers are usually shown in relation to the tables they are attached to.

	create view showtriggers as
	select trg.tgname as trigger_name , tbl.relname as table_name,
	      p.proname as function_name,
	       case trg.tgtype & cast(2 as int2)
	         when 0 then 'AFTER'
	         else 'BEFORE'
	       end as trigger_type,
	       case trg.tgtype & cast(28 as int2)
	         when 16 then 'UPDATE'
	         when 8 then 'DELETE'
	         when 4 then 'INSERT'
	         when 20 then 'INSERT, UPDATE'
	         when 28 then 'INSERT, UPDATE, DELETE'
	         when 24 then 'UPDATE, DELETE'
	         when 12 then 'INSERT, DELETE'
	       end as trigger_event
	from pg_trigger trg, pg_class tbl, pg_proc p
	where trg.tgrelid = tbl.oid and trg.tgfoid = p.oid;

Contributors: Holger Marzen holger at marzen.de, Thomas Kellerer spam_eater at gmx.net