Slow queries & missing indices

[brot]

I'm using postgres and the latest available firefly iii (docker) version.
Beside firefly iii I configured pghero to analyze slow queries.

The database size is 32,8MB
The biggest tables are

table-name	size
journal_meta	10.4 MB
transactions	3.57 MB
transaction_journals	2.71 MB

The top result of pghero takes an average time of 34 ms and was called 26,191 times.

SELECT "transaction_groups"."id" AS "transaction_group_id",
       "transaction_groups"."user_id" AS "user_id",
       "transaction_groups"."user_group_id" AS "user_group_id",
       "transaction_groups"."created_at" AS "created_at",
       "transaction_groups"."updated_at" AS "updated_at",
       "transaction_groups"."title" AS "transaction_group_title",
       "transaction_journals"."id" AS "transaction_journal_id",
       "transaction_journals"."transaction_type_id",
       "transaction_journals"."description",
       "transaction_journals"."date",
       "transaction_journals"."order",
       "transaction_types"."type" AS "transaction_type_type",
       "source"."id" AS "source_transaction_id",
       "source"."account_id" AS "source_account_id",
       "source"."reconciled",
       "source"."amount" AS "amount",
       "source"."transaction_currency_id" AS "currency_id",
       "currency"."code" AS "currency_code",
       "currency"."name" AS "currency_name",
       "currency"."symbol" AS "currency_symbol",
       "currency"."decimal_places" AS "currency_decimal_places",
       "source"."foreign_amount" AS "foreign_amount",
       "source"."foreign_currency_id" AS "foreign_currency_id",
       "foreign_currency"."code" AS "foreign_currency_code",
       "foreign_currency"."name" AS "foreign_currency_name",
       "foreign_currency"."symbol" AS "foreign_currency_symbol",
       "foreign_currency"."decimal_places" AS "foreign_currency_decimal_places",
       "destination"."account_id" AS "destination_account_id",
       "categories"."id" AS "category_id",
       "categories"."name" AS "category_name"
FROM "transaction_journals"
LEFT JOIN "transaction_groups" ON "transaction_journals"."transaction_group_id" = "transaction_groups"."id"
LEFT JOIN "transactions" AS "source" ON "source"."transaction_journal_id" = "transaction_journals"."id"
AND "source"."amount" < $1
LEFT JOIN "transactions" AS "destination" ON "destination"."transaction_journal_id" = "transaction_journals"."id"
AND "destination"."amount" > $2
LEFT JOIN "transaction_types" ON "transaction_types"."id" = "transaction_journals"."transaction_type_id"
LEFT JOIN "transaction_currencies" AS "currency" ON "currency"."id" = "source"."transaction_currency_id"
LEFT JOIN "transaction_currencies" AS "foreign_currency" ON "foreign_currency"."id" = "source"."foreign_currency_id"
LEFT JOIN "category_transaction_journal" ON "category_transaction_journal"."transaction_journal_id" = "transaction_journals"."id"
LEFT JOIN "categories" ON "category_transaction_journal"."category_id" = "categories"."id"
WHERE "transaction_journals"."user_id" = $3
  AND "transaction_journals"."user_id" IS NOT NULL
  AND "transaction_groups"."deleted_at" IS NULL
  AND "transaction_journals"."deleted_at" IS NULL
  AND "source"."deleted_at" IS NULL
  AND "destination"."deleted_at" IS NULL
  AND "transaction_journals"."date" >= $4
  AND "transaction_journals"."date" <= $5
  AND "transaction_types"."type" in ($6)
  AND ("source"."account_id" in ($7,
                                 $8,
                                 $9,
                                 $10,
                                 $11,
                                 $12,
                                 $13,
                                 $14,
                                 $15,
                                 $16,
                                 $17,
                                 $18,
                                 $19,
                                 $20,
                                 $21,
                                 $22,
                                 $23,
                                 $24,
                                 $25,
                                 $26,
                                 $27,
                                 $28,
                                 $29,
                                 $30,
                                 $31,
                                 $32,
                                 $33,
                                 $34,
                                 $35,
                                 $36,
                                 $37,
                                 $38,
                                 $39,
                                 $40)
       OR "destination"."account_id" in ($41,
                                         $42,
                                         $43,
                                         $44,
                                         $45,
                                         $46,
                                         $47,
                                         $48,
                                         $49,
                                         $50,
                                         $51,
                                         $52,
                                         $53,
                                         $54,
                                         $55,
                                         $56,
                                         $57,
                                         $58,
                                         $59,
                                         $60,
                                         $61,
                                         $62,
                                         $63,
                                         $64,
                                         $65,
                                         $66,
                                         $67,
                                         $68,
                                         $69,
                                         $70,
                                         $71,
                                         $72,
                                         $73,
                                         $74))
  AND "categories"."id" in ($75)
  AND "transaction_journals"."deleted_at" IS NULL
ORDER BY "transaction_journals"."date" DESC,
         "transaction_journals"."order" ASC,
         "transaction_journals"."id" DESC,
         "transaction_journals"."description" DESC,
         "source"."amount" DESC

But non of the tables in the SQL have helpful indices

 public | categories_pkey                                      | index | fireflyiii | categories
 public | transaction_currencies_code_unique                   | index | fireflyiii | transaction_currencies
 public | transaction_currencies_pkey                          | index | fireflyiii | transaction_currencies
 public | transaction_groups_pkey                              | index | fireflyiii | transaction_groups
 public | transaction_journals_pkey                            | index | fireflyiii | transaction_journals
 public | transaction_types_pkey                               | index | fireflyiii | transaction_types
 public | transaction_types_type_unique                        | index | fireflyiii | transaction_types
 public | transactions_pkey                                    | index | fireflyiii | transactions

Is there an easy way to find out where this SQL query is called?
Should I add more indices on my own directly in the database or should firefly iii handle all the index creation?
PgHero found also other queries, but I would like to understand how to deal with these "slow" queries (<20ms and called more than 100 times)

[JC5]

Is there an easy way to find out where this SQL query is called?

Everywhere, it's used to collect transactions.

Should I add more indices on my own directly in the database or should firefly iii handle all the index creation?

Indexes are defined in the migrations, I'm not sure how to make sure PostgreSQL actually creates them. I'll do some research!

[brot]

Thanks @JC5 for the fast reply.

The migration table in my database shows 49 rows. So it seems that all migrations are applied to the database.

But for me it looks like that there are a lot of indices missing. The migration files don't add any unique() or index() for the transactions or transaction_journals table as far as I can tell. (This is the same for a lot of other tables)
Although it could be a good idea to add indices to columns where a foreign key is defined on.

I will try to fix the slow queries on my instance with adding indices manually and will see if this improves the web interface performance.

[brot]

@JC5 I created the indices PgHero recommended and the web interface is now way faster. I think the most important index was

CREATE INDEX CONCURRENTLY IF NOT EXISTS transaction_journals_date ON transaction_journals (date);

But I created also the following recommended ones. Would be great if you could add this indices to the migrations

CREATE INDEX CONCURRENTLY IF NOT EXISTS transaction_transaction_journal_id ON transactions (transaction_journal_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS journal_meta_transaction_journal_id ON journal_meta (transaction_journal_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS transaction_journals_transaction_group_id ON transaction_journals (transaction_group_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS journal_meta_data ON journal_meta (data);
CREATE INDEX CONCURRENTLY IF NOT EXISTS transaction_journals_bill_id_date ON transaction_journals (bill_id, date);
CREATE INDEX CONCURRENTLY IF NOT EXISTS category_transaction_journal_transaction_journal_id ON category_transaction_journal (transaction_journal_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS journal_meta_name ON journal_meta (name);
CREATE INDEX CONCURRENTLY IF NOT EXISTS transaction_account_id ON transactions (account_id);

Not sure how much data other users have in their firefly iii instance, but I have imported my financial data since 2005/2006 so these are the row counts of the biggest tables

table	rows
category_transaction_journal	18958
transaction_groups	20347
transaction_journals	20918
transactions	41836
journal_meta	62651

Compared to the databases I used to work with, that's not a lot of data. Nevertheless, these tables have enough data for the right indices to deliver better performance

[JC5]

Thanks, will do!

I'm starting to doubt myself, but does PostgreSQL need separate indices on primary keys?

[brot]

No, for primary keys PostgreSQL will create the index implicitly.

[brot]

@JC5 Thanks for the update!

Now PgHero shows me that there are "Duplicate Indexes"
On transaction_currencies transaction_currencies_code_index (code) is covered by transaction_currencies_code_unique (code)

[JC5]

Ah yes, I'll drop it in the next release, thanks for letting me know.