varlena
varlena
PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

28-Jul-2003 Issue: 36

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see www.PostgreSQL.org.

Little query tuning hints
[GENERAL] Optimisation, index use question [long] 13-Jul-2003

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
you won't be using the functional index. Use the same function you have in your index.
	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;
Qualify and order by to taste.

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;
Separating out the parts of the query we have:
	UPDATE prod.t_results SET expdate=(...something...);
The (...something...) is a select like this:
	(SELECT e.termdate
	 FROM work.termdate e, prod.t_results r
	 WHERE e.docid = r.docid);
First you can see that the update is happening with no qualification. The WHERE clause only references the second copy of prod.t_results. This should result in an explosion of updates, almost certainly not updates of the values you wanted.

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;
Broken out this could look like this:
	update prod.t_results set expdata=(...something...)
and the (...something...) would be
	select e.termdate from work.termdate
	where prod.t_results.docid=e.docid
The difference is that prod.t_results.docid references the table you are updating.

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


Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Top
Google
Search General Bits & varlena.com Search WWW