There are several small and easily forgotten hints for helping the optimizer use the indexes. Here are two commonly mentioned.
Use the right datatype!
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!
2003 = mytimestampyou won't be using the functional index. Use the same function you have in your index.
2003 = what_year(mytimestamp)
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 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.
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.
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.docidThe difference is that prod.t_results.docid references the table you are updating.
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;
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