How to improve the performance of queries when joining a lot of huge tables?

user_16674220 0
Viewed 198

This is my SQL script, I have to join 7 tables

SELECT concat_ws('-', it.item_id, it.model_id) AS product_id,
       concat_ws('-', aip.partner_item_id, aip.partner_model_id) AS product_reseller_id,
       i.name as item_name,
       im.name AS model_name,
       p.partner_code,
       sum(it.quantity) AS transfer_total,
       sum(isb.remaining_item) as remaining_stock,
       sum(isb.sold_item) as partner_sold
FROM transfer t
INNER JOIN partner p ON p.reseller_store_id = t.reseller_store_id
INNER JOIN item_transfer it ON t.id = it.transfer_id
INNER JOIN item i ON i.id = it.item_id
INNER JOIN item_model im ON it.model_id = im.id
INNER JOIN affiliate_item_mapping aip on it.item_id = aip.seller_item_id and it.model_id = aip.seller_model_id
and t.reseller_store_id = aip.reseller_store_id
LEFT JOIN inventory_summary_branch isb on isb.inventory_summary_id = concat_ws('-', aip.partner_item_id, aip.partner_model_id)
WHERE p.store_id = 9805
GROUP BY it.item_id, it.model_id, p.partner_code, i.id, im.id, aip.id, isb.inventory_summary_id

This is the result of SQL EXPLAIN:

GroupAggregate  (cost=13861.57..13861.62 rows=1 width=885) (actual time=1890.392..1890.525 rows=15 loops=1)
  Group Key: it.item_id, it.model_id, p.partner_code, i.id, im.id, aip.id, isb.inventory_summary_id
  Buffers: shared hit=118610
  ->  Sort  (cost=13861.57..13861.58 rows=1 width=765) (actual time=1890.310..1890.338 rows=21 loops=1)
        Sort Key: it.item_id, it.model_id, p.partner_code, aip.id, isb.inventory_summary_id
        Sort Method: quicksort  Memory: 28kB
        Buffers: shared hit=118610
        ->  Nested Loop  (cost=1.27..13861.56 rows=1 width=765) (actual time=73.156..1890.057 rows=21 loops=1)
              Buffers: shared hit=118610
              ->  Nested Loop  (cost=0.85..13853.14 rows=1 width=753) (actual time=73.134..1889.495 rows=21 loops=1)
                    Buffers: shared hit=118526
                    ->  Nested Loop  (cost=0.43..13845.32 rows=1 width=609) (actual time=73.099..1888.733 rows=21 loops=1)
                          Join Filter: ((p.reseller_store_id = t.reseller_store_id) AND (it.transfer_id = t.id))
                          Rows Removed by Join Filter: 2142
                          Buffers: shared hit=118442
                          ->  Nested Loop  (cost=0.43..13840.24 rows=1 width=633) (actual time=72.793..1879.961 rows=21 loops=1)
                                Join Filter: ((aip.seller_item_id = it.item_id) AND (aip.seller_model_id = it.model_id))
                                Rows Removed by Join Filter: 6003
                                Buffers: shared hit=118379
                                ->  Nested Loop Left Join  (cost=0.43..13831.47 rows=1 width=601) (actual time=72.093..1861.415 rows=24 loops=1)
                                      Buffers: shared hit=118307
                                      ->  Nested Loop  (cost=0.00..11.44 rows=1 width=572) (actual time=0.042..0.696 rows=24 loops=1)
                                            Join Filter: (p.reseller_store_id = aip.reseller_store_id)
                                            Rows Removed by Join Filter: 150
                                            Buffers: shared hit=7
                                            ->  Seq Scan on partner p  (cost=0.00..10.38 rows=1 width=524) (actual time=0.026..0.039 rows=6 loops=1)
                                                  Filter: (store_id = 9805)
                                                  Buffers: shared hit=1
                                            ->  Seq Scan on affiliate_item_mapping aip  (cost=0.00..1.03 rows=3 width=48) (actual time=0.006..0.043 rows=29 loops=6)
                                                  Buffers: shared hit=6
                                      ->  Index Scan using branch_id_inventory_summary_id_inventory_summary_branch on inventory_summary_branch isb  (cost=0.43..13820.01 rows=1 width=29) (actual time=77.498..77.498 rows=0 loops=24)
                                            Index Cond: ((inventory_summary_id)::text = concat_ws('-'::text, aip.partner_item_id, aip.partner_model_id))
                                            Buffers: shared hit=118300
                                ->  Seq Scan on item_transfer it  (cost=0.00..5.31 rows=231 width=32) (actual time=0.024..0.391 rows=251 loops=24)
                                      Buffers: shared hit=72
                          ->  Seq Scan on transfer t  (cost=0.00..3.83 rows=83 width=16) (actual time=0.011..0.256 rows=103 loops=21)
                                Buffers: shared hit=63
                    ->  Index Scan using pk_item on item i  (cost=0.42..7.81 rows=1 width=152) (actual time=0.022..0.023 rows=1 loops=21)
                          Index Cond: (id = it.item_id)
                          Buffers: shared hit=84
              ->  Index Scan using pk_item_model on item_model im  (cost=0.43..8.41 rows=1 width=20) (actual time=0.016..0.018 rows=1 loops=21)
                    Index Cond: (id = it.model_id)
                    Buffers: shared hit=84
Planning time: 10.051 ms
Execution time: 1890.943 ms

Of course, this statement works fine, but it's slow. Is there a better way to write this code?

How can I improve the performance? Join or sub-query is better in this case? Anyone, please give me a hand

database-performance query-optimization postgresql sql database
3 Answers

Inner joins will always be slower, especially with so many tables.

You could change from an inner join on the whole table to just the columns you need and see if that improves it at all:

From:

INNER JOIN partner p ON p.reseller_store_id = t.reseller_store_id

To:

inner join (select id, partner_code from partner) as p ON p.reseller_store_id = t.reseller_store_id

See if that speeds things up at all.

If not I would recommend indexes on the keys

user_167666850

2 things can help you

  1. do VACCUME ANALYZE for all the tables involved.
  2. create indexe on item_transfer.item_id & model_id
user_13674540

Essentially all of your time (77.498*24) is spend on the index scan of branch_id_inventory_summary_id_inventory_summary_branch.

About the only explanation I can see for this is that the index isn't suited to the query, and it is being full-index scanned (in lieu of full scanning the table), rather than being efficiently scanned. This probably means the index includes the column inventory_summary_id, but it is not the leading column. (It would be nice if EXPLAIN were to make this inefficient type of usage clearer than it currently does).

You would probably benefit from an index such as on inventory_summary_branch (inventory_summary_id) which has a better chance of being used efficiently.

I don't know why it wouldn't just do a hash join of that table. Maybe your work_mem is too low.

user_17212390
Related