0

I am trying to run the query below on a database with hundreds of thousands of lines but it is taking over 12 hours so far. It is the lateral left join that is taking so long. The reason I need the lateral left join is because I am trying to get the last updated category for each transaction (ordered by c.updated_at DESC). Does anyone have any suggestions on how to speed my query up? I think I might need to create indexes but I am unsure of what indexes to create/how to do it.

Select *
From tp_base_fi_account_transactions t
Left join lateral (
        select category_id
        from tp_base_fi_account_transaction_categories c 
        where c.transaction_id=t.id 
        order by c.updated_at DESC
        LIMIT 1
) category on TRUE
inner join tp_base_transaction_categories cats on cats.id=category.category_id
inner join tp_base_vendor_associations va ON va.id = t.vendor_association_id
inner join tp_base_vendors v ON v.id = va.vendor_id
inner join tp_base_userprofile up ON up.user_id = t.assigned_user_id
inner join tp_base_organizations o ON o.id = up.organization_id

The schema is as follows:

**tp_base_fi_account_transaction_categories c** columns: ['id', 'created_at', 'updated_at', 'transaction_id', 'category_id', 'user_confirmed']
**tp_base_fi_account_transactions t** columns: ['id', 'transaction_id', 'reference_number', 'amount', 'date', 'ppd_id', 'primary_type', 'is_pending', 'created_at', 'updated_at', 'organization_fi_account_id', 'vendor_association_id', 'receipt_id', 'group_id', 'changed_by_id', 'payment_method_id', 'user_confirmed', 'acct_class_id', 'department_id', 'memo', 'location_id', 'settlement_date']
**tp_base_vendor_associations va** columns: ['id', 'name', 'mid', 'mcc', 'created_at', 'updated_at', 'vendor_id']
**tp_base_vendors v** columns: ['id', 'name', 'domain', 'created_at', 'updated_at', 'is_matchable']
**tp_base_userprofile up** columns: ['id', 'approval_group_id', 'user_id', 'avatar', 'display_name', 'organization_id']
**tp_base_organizations o** columns: ['id', 'name', 'created_by_id']

When I run explain analyze on the query and limit the results to 5 this is the query plan I get:

Limit  (cost=28872.80..138364.49 rows=5 width=1461) (actual time=231.504..647.825 rows=5 loops=1)
  ->  Nested Loop  (cost=28872.80..17255720972.54 rows=787991 width=1461) (actual time=231.503..647.803 rows=5 loops=1)
        ->  Nested Loop  (cost=28872.38..17255320333.89 rows=787991 width=1375) (actual time=231.488..647.740 rows=5 loops=1)
              ->  Nested Loop  (cost=28871.96..17254951399.67 rows=787991 width=1286) (actual time=231.471..647.663 rows=5 loops=1)
                    ->  Nested Loop  (cost=28871.54..17248296815.67 rows=787991 width=1224) (actual time=231.443..647.520 rows=5 loops=1)
                          ->  Hash Join  (cost=6982.90..221853.70 rows=787991 width=1220) (actual time=114.346..114.996 rows=5 loops=1)
                                Hash Cond: (up.organization_id = o.id)
                                ->  Hash Join  (cost=6969.09..210746.32 rows=856978 width=1001) (actual time=114.117..114.748 rows=5 loops=1)
                                      Hash Cond: (t.assigned_user_id = up.user_id)
                                      ->  Seq Scan on tp_base_fi_account_transactions t  (cost=0.00..27129.78 rows=856978 width=747) (actual time=0.012..0.020 rows=7 loops=1)
                                      ->  Hash  (cost=3546.93..3546.93 rows=73293 width=254) (actual time=114.010..114.010 rows=73293 loops=1)
                                            Buckets: 16384  Batches: 8  Memory Usage: 2360kB
                                            ->  Seq Scan on tp_base_userprofile up  (cost=0.00..3546.93 rows=73293 width=254) (actual time=0.024..51.276 rows=73293 loops=1)
                                ->  Hash  (cost=10.58..10.58 rows=258 width=219) (actual time=0.200..0.200 rows=258 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 60kB
                                      ->  Seq Scan on tp_base_organizations o  (cost=0.00..10.58 rows=258 width=219) (actual time=0.006..0.086 rows=258 loops=1)
                          ->  Limit  (cost=21888.65..21888.65 rows=1 width=12) (actual time=106.499..106.500 rows=1 loops=5)
                                ->  Sort  (cost=21888.65..21888.65 rows=2 width=12) (actual time=106.495..106.495 rows=1 loops=5)
                                      Sort Key: c.updated_at DESC
                                      Sort Method: quicksort  Memory: 25kB
                                      ->  Seq Scan on tp_base_fi_account_transaction_categories c  (cost=0.00..21888.64 rows=2 width=12) (actual time=48.199..106.454 rows=1 loops=5)
                                            Filter: (transaction_id = t.id)
                                            Rows Removed by Filter: 1029685
                    ->  Index Scan using tp_base_transaction_categories_pkey on tp_base_transaction_categories cats  (cost=0.42..8.44 rows=1 width=62) (actual time=0.020..0.020 rows=1 loops=5)
                          Index Cond: (id = c.category_id)
              ->  Index Scan using tp_base_vendor_associations_pkey on tp_base_vendor_associations va  (cost=0.42..0.46 rows=1 width=89) (actual time=0.010..0.010 rows=1 loops=5)
                    Index Cond: (id = t.vendor_association_id)
        ->  Index Scan using tp_base_vendors_pkey on tp_base_vendors v  (cost=0.42..0.50 rows=1 width=86) (actual time=0.008..0.009 rows=1 loops=5)
              Index Cond: (id = va.vendor_id)
Planning time: 3.422 ms
Execution time: 651.187 ms
3
  • Does c.transaction_id have an index?
    – jjanes
    Mar 12, 2020 at 17:05
  • Unrelated to your question, but: Postgres 9.3 is no longer supported you should plan an upgrade as soon as possible.
    – user1822
    Mar 12, 2020 at 17:09
  • c.transaction_id does not have an index because I was unsure how to create for what I need. Do you have suggestions on how to do this? @jjanes Mar 12, 2020 at 17:30

1 Answer 1

1

I don't think the left join here makes any sense, as you are then doing an inner join keyed on equality of one of the nullable columns, so the null rows can't be returned anyway. But based on the plan, I think the planner is smart enough to realize this and ignore the LEFT.

You might need an index on transaction_id. Creating it is nothing special, just:

create index on tp_base_fi_account_transaction_categories (transaction_id);

It might be slightly faster to do this instead:

create index on tp_base_fi_account_transaction_categories (transaction_id, updated_at);

It looks like transaction_id is mostly unique within the table (based on a sample size of 5 from your LIMITed EXPLAIN ANALYZE), so adding the updated_at to the index will probably not help a huge amount.

Since you selecting all of tp_base_fi_account_transactions (unless those inner joins function to filter them out and not just to bring in extra columns), a potentially faster alternative might be to do a sub-select to fetch just the newest rows for each transaction_id in bulk, then join to that without the lateral. That join could proceed with a HashJoin rather than a nested loop.

Select *
From tp_base_fi_account_transactions t
join (
        select distinct on (transaction_id) transaction_id, category_id
        from tp_base_fi_account_transaction_categories        
        order by transaction_id, updated_at DESC
) category on transaction_id=t.id
inner join tp_base_transaction_categories cats on cats.id=category.category_id
inner join tp_base_vendor_associations va ON va.id = t.vendor_association_id
inner join tp_base_vendors v ON v.id = va.vendor_id
inner join tp_base_userprofile up ON up.user_id = t.assigned_user_id
inner join tp_base_organizations o ON o.id = up.organization_id
4
  • This is great thank you! Just to clarify it should be Select * From tp_base_fi_account_transactions t join ( select distinct on (transaction_id) transaction_id, category_id from tp_base_fi_account_transaction_categories order by transaction_id, updated_at DESC ) category on category.transaction_id=t.id ... with the difference being the category.transaction_id? Are there any other indices that might be helpful? Trying to understand how they work because I haven't used them before @jjanes Mar 12, 2020 at 18:43
  • Also would this query get the same results WITHOUT only taking the most recent transaction? I.e. would have duplicate transaction ids? Or would it not get the category correctly? Select * From tp_base_fi_account_transactions t join tp_base_fi_account_transaction_categories ctg on t.id=ctg.transaction_id inner join tp_base_vendor_associations va ON va.id = t.vendor_association_id inner join tp_base_vendors v ON v.id = va.vendor_id inner join tp_base_userprofile up ON up.user_id = t.assigned_user_id inner join tp_base_organizations o ON o.id = up.organization_id @jjanes Mar 12, 2020 at 19:16
  • Right, I didn't realize another table had the same column name in it; so that is the way to alias it. If you do it this way, it might choose to full scan the table, or it might choose to walk the full index if you have one on (transaction_id, updated_at, category_id). It is hard to know which would be faster without trying it.
    – jjanes
    Mar 12, 2020 at 23:20
  • Unless transaction_id is unique within tp_base_fi_account_transaction_categories, then that query you show would return extra rows compared to your original. (And if it is unique, then you would get the same rows and it should be faster and also a more natural way to write it, no point in doing an ORDER BY when there is only one row)
    – jjanes
    Mar 12, 2020 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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