Indexing

index.txt

### Before
1)
    EXPLAIN ANALYZE SELECT  "cards".* FROM "cards"  WHERE "cards"."expansion_id" = 130  ORDER BY name;
    -------------------------------------------------------------------------------
    Sort  (cost=1367.55..1368.17 rows=248 width=348) (actual time=12.634..12.654 rows=249 loops=1)
      Sort Key: name
      Sort Method: quicksort  Memory: 148kB
      ->  Seq Scan on cards  (cost=0.00..1357.69 rows=248 width=348) (actual time=0.195..9.857 rows=249 loops=1)
            Filter: (expansion_id = 130)
            Rows Removed by Filter: 22526
    Total runtime: 12.837 ms
    (6 rows)

2)
    EXPLAIN ANALYZE SELECT { //BUNCH O' STUFF// } FROM "transactions" INNER JOIN "cards" ON "cards"."id" = "transactions"."card_id" WHERE "transactions"."trade_id" = 19 AND "transactions"."giver_id" = 1  ORDER BY cards.name DESC;
    -------------------------------------------------------------------------------
     Sort  (cost=9.39..9.39 rows=1 width=376) (actual time=0.113..0.113 rows=2 loops=1)
       Sort Key: cards.name
       Sort Method: quicksort  Memory: 26kB
       ->  Nested Loop  (cost=0.29..9.38 rows=1 width=376) (actual time=0.047..0.056 rows=2 loops=1)
             ->  Seq Scan on transactions  (cost=0.00..1.06 rows=1 width=28) (actual time=0.016..0.016 rows=2 loops=1)
                   Filter: ((trade_id = 19) AND (giver_id = 1))
                   Rows Removed by Filter: 2
             ->  Index Scan using cards_pkey on cards  (cost=0.29..8.31 rows=1 width=348) (actual time=0.015..0.017 rows=1 loops=2)
                   Index Cond: (id = transactions.card_id)
     Total runtime: 0.226 ms
    (10 rows)


### AFTER
1)
    EXPLAIN ANALYZE SELECT  "cards".* FROM "cards"  WHERE "cards"."expansion_id" = 130  ORDER BY name;
    -------------------------------------------------------------------------------
     Sort  (cost=461.97..462.59 rows=248 width=348) (actual time=2.871..2.894 rows=249 loops=1)
       Sort Key: name
       Sort Method: quicksort  Memory: 148kB
       ->  Index Scan using index_cards_on_expansion_id on cards  (cost=0.29..452.11 rows=248 width=348) (actual time=0.018..0.221 rows=249 loops=1)
             Index Cond: (expansion_id = 130)
     Total runtime: 2.944 ms
    (6 rows)

2)
    EXPLAIN ANALYZE SELECT { //BUNCH O' STUFF// } FROM "transactions" INNER JOIN "cards" ON "cards"."id" = "transactions"."card_id" WHERE "transactions"."trade_id" = 19 AND "transactions"."giver_id" = 1  ORDER BY cards.name DESC;
    -------------------------------------------------------------------------------
     Sort  (cost=9.39..9.39 rows=1 width=376) (actual time=0.056..0.056 rows=2 loops=1)
       Sort Key: cards.name
       Sort Method: quicksort  Memory: 26kB
       ->  Nested Loop  (cost=0.29..9.38 rows=1 width=376) (actual time=0.033..0.042 rows=2 loops=1)
             ->  Seq Scan on transactions  (cost=0.00..1.06 rows=1 width=28) (actual time=0.014..0.016 rows=2 loops=1)
                   Filter: ((trade_id = 19) AND (giver_id = 1))
                   Rows Removed by Filter: 2
             ->  Index Scan using cards_pkey on cards  (cost=0.29..8.31 rows=1 width=348) (actual time=0.011..0.011 rows=1 loops=2)
                   Index Cond: (id = transactions.card_id)
     Total runtime: 0.103 ms
    (10 rows)