SQL: PostgreSQL specific extensions

• Query debugging
  • EXPLAIN

template1=# explain select * from a,b,c where a.i=b.i and b.i=c.i;
                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=62.31..109.50 rows=375 width=12)
   Hash Cond: ("outer".i = "inner".i)
   ->  Seq Scan on a  (cost=0.00..20.00 rows=1000 width=4)
   ->  Hash  (cost=62.12..62.12 rows=75 width=8)
         ->  Hash Join  (cost=1.19..62.12 rows=75 width=8)
               Hash Cond: ("outer".i = "inner".i)
               ->  Seq Scan on b  (cost=0.00..20.00 rows=1000 width=4)
               ->  Hash  (cost=1.15..1.15 rows=15 width=4)
                     ->  Seq Scan on c  (cost=0.00..1.15 rows=15 width=4)
(9 rows)

• Asynchronous notification
  • LISTEN, NOTIFY
  
  
• Performance
  • PREPARE, EXECUTE
  • Prepare frequently used/expensive plans
  • Parameters

Page 29