Here's a query I've got:

SELECT "balance_transactions"."fee"
FROM "balance_transactions" 
JOIN charges ON balance_transactions.source = charges.balance_id 
WHERE "balance_transactions"."account_id" = 123 
AND (balance_transactions.type = 'charge' 
AND charges.refunded = false 
AND charges.invoice IS NOT NULL) 
AND ("balance_transactions"."created" BETWEEN '2013-12-29' AND '2014-01-28');

What's hurting my brain is figuring out what I need to index to optimize a query like this.

Would I create individual indexes on each column in that WHERE statement? Or a single index that encompasses multiple columns? Or something else?

I happen to be using Postgres.

Here's the output of EXPLAIN ANALYZE:

 Nested Loop  (cost=40.44..9294.43 rows=744 width=4) (actual time=0.791..32.687 rows=1131 loops=1)
   ->  Bitmap Heap Scan on balance_transactions  (cost=40.02..2266.08 rows=1023 width=22) (actual time=0.737..1.006 rows=1201 loops=1)
         Recheck Cond: ((account_id = 79) AND ((type)::text = 'charge'::text) AND (created >= '2013-12-29 00:00:00'::timestamp without time zone) AND (created <= '2014-01-28 00:00:00'::timestamp without time zone))
         ->  Bitmap Index Scan on index_balance_transactions_account_type_created  (cost=0.00..39.77 rows=1023 width=0) (actual time=0.716..0.716 rows=1201 loops=1)
               Index Cond: ((account_id = 79) AND ((type)::text = 'charge'::text) AND (created >= '2013-12-29 00:00:00'::timestamp without time zone) AND (created <= '2014-01-28 00:00:00'::timestamp without time zone))
   ->  Index Scan using index_stripe_charges_on_stripe_id on charges  (cost=0.42..6.86 rows=1 width=18) (actual time=0.025..0.025 rows=1 loops=1201)
         Index Cond: ((balance_id)::text = (balance_transactions.source)::text)
         Filter: ((NOT refunded) AND (invoice IS NOT NULL))
         Rows Removed by Filter: 0
 Total runtime: 32.924 ms
(10 rows)
  • Generally it is advised that we create indexes for all fields after where clause. – Ashish Charan Jan 31 '14 at 18:00
  • 1
    Run explain plan on your query and include that in your question along with the version of Postgres your on. – Kuberchaun Jan 31 '14 at 18:06
  • @JustBob Updated question. – Shpigford Jan 31 '14 at 19:50

I would suggest the following two indexes:

charges(balance_id, refunded, invoice)
balance_transactions(account_id, type, created, source, fee)

The first is a covering index for charges. The index should be used for the query with no need for the data tables.

The second is a covering index for balance_transactions. The first three will satisfy the where clause. The four column will be used for the join and the final is needed for the select.

  • Depending on data volume and data distribution would a filtered indexed have any value here? – Kuberchaun Jan 31 '14 at 18:10
  • @JustBob . . . Of course, it could. My interpretation of the query is that any account could be chosen (for example), so I don't see the advantage of a filtered index. – Gordon Linoff Jan 31 '14 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.