Postgres Index example 01

PostgresIndex01.txt

Before:

EXPLAIN SELECT
    e.entry_category,
    tp.transaction_type,
    t.transaction_description,
    t.transaction_date,
    te.amount
FROM transaction t
inner join transaction_type tp on
tp.type_id = t.transaction_type
inner join transaction_entry te
on te.entry_id = t.transaction_id 
inner join entry_category e
on e.entry_id = te.entry_category
where t.transaction_date = '2015-09-02'

Nested Loop  (cost=38.24..70.63 rows=8 width=200)
  ->  Hash Join  (cost=38.09..68.94 rows=8 width=116)
        Hash Cond: (te.entry_id = t.transaction_id)
        ->  Seq Scan on transaction_entry te  (cost=0.00..25.10 rows=1510 width=24)
        ->  Hash  (cost=38.05..38.05 rows=3 width=100)
              ->  Nested Loop  (cost=0.15..38.05 rows=3 width=100)
                    ->  Seq Scan on transaction t  (cost=0.00..17.50 rows=3 width=100)
                          Filter: (transaction_date = '2015-09-02'::date)
                    ->  Index Scan using transaction_type_pkey on transaction_type tp  (cost=0.15..6.84 rows=1 width=8)
                          Index Cond: (type_id = t.transaction_type)
  ->  Index Scan using entry_category_pkey on entry_category e  (cost=0.15..0.20 rows=1 width=92)
        Index Cond: (entry_id = te.entry_category)

create index idx_fk_te on transaction_entry(entry_category);
After:

  ->  Nested Loop  (cost=0.15..10.22 rows=1 width=116)
        Join Filter: (t.transaction_id = te.entry_id)
        ->  Nested Loop  (cost=0.15..9.20 rows=1 width=100)
              ->  Seq Scan on transaction t  (cost=0.00..1.01 rows=1 width=100)
                    Filter: (transaction_date = '2015-09-02'::date)
              ->  Index Scan using transaction_type_pkey on transaction_type tp  (cost=0.15..8.17 rows=1 width=8)
                    Index Cond: (type_id = t.transaction_type)
        ->  Seq Scan on transaction_entry te  (cost=0.00..1.01 rows=1 width=24)
  ->  Index Scan using entry_category_pkey on entry_category e  (cost=0.15..8.17 rows=1 width=92)
        Index Cond: (entry_id = te.entry_category)