NoPublic, bureaucrat, administrator
1,068
edits
Changes
no edit summary
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 [[Difference_MySQL_PostGreSQL|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:
<pre>
EXPLAIN
SELECT *
FROM hazard
JOIN link ON (hazard.hazard_guid = link.to_guid)
JOIN hazreduc ON (link.from_guid = hazreduc.hazreduc_guid);
</pre>
This produces the following output:
<pre>
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)
</pre>
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 ==__NOEDITSECTION__
{{NavBox Hub}}
[[Category:NoPublic]]
[[Category:VIE]]