Optimizing views in pgSQL

During upgrades to IMSMA v6 (especially in Afghanistan), it has been noted that although the translation of MySQL queries/views into pgSQL queries/views (for details see here) is relatively easy, sometimes views have to be rewritten due to poor performance. This is due to the fact that in each database system the query optimizer works a bit differently, and therefore a query that is fast in one system, might be very slow in another system. Generally, the query optimizer has to make the following decisions:

• Scan method: e.g. sequential scan, (bitmap) index scan
• Join method: e.g. nested loop, hash join, merge join
• Join order

In PostgreSQL, the query execution plan can be viewed using EXPLAIN. For example, the following statement details the query execution plan for a query joining HAZARD, LINK and HAZREDUC:

EXPLAIN
SELECT *
FROM hazard
JOIN link ON (hazard.hazard_guid = link.to_guid)
JOIN hazreduc ON (link.from_guid = hazreduc.hazreduc_guid);

This produces the following output:

Hash Join  (cost=82524.41..518756.82 rows=87716 width=8756)
  Hash Cond: ((link.from_guid)::text = (hazreduc.hazreduc_guid)::text)
  ->  Hash Join  (cost=7586.82..148077.88 rows=224849 width=4125)
        Hash Cond: ((link.to_guid)::text = (hazard.hazard_guid)::text)
        ->  Seq Scan on link  (cost=0.00..38279.60 rows=1028260 width=335)
        ->  Hash  (cost=1702.03..1702.03 rows=12303 width=3790)"
              ->  Seq Scan on hazard  (cost=0.00..1702.03 rows=12303 width=3790)
  ->  Hash  (cost=7386.93..7386.93 rows=116293 width=4631)
        ->  Seq Scan on hazreduc  (cost=0.00..7386.93 rows=116293 width=4631)

In some cases, particularly when many tables are joined together, the query optimizer might not choose the best execution plan. In such a case, it can be tried to rewrite the query differently in order to make it faster. Examples and some guiding principles are outlined on his page.

Examples

Template:NavBox Hub