0

I add the Query Plan of my query. And this takes too much time to execute and give data. https://explain.depesz.com/s/4JtZ

I want to reduce the execution time of this query. So require to optimize this query.

"HashAggregate  (cost=524165.80..524217.33 rows=5153 width=490) (actual time=32063.455..32099.569 rows=65270 loops=1)"
"  Group Key: "*SELECT* 1".id, "*SELECT* 1".brand_name, "*SELECT* 1".product_brand_id, "*SELECT* 1".product_group_name, "*SELECT* 1".product_group, "*SELECT* 1".is_active_funnel, "*SELECT* 1".product_id, "*SELECT* 1".product_uom_qty, "*SELECT* 1".name, "*SE (...)"
"  Buffers: shared hit=2302480 read=905637, temp read=33523 written=33066"
"  ->  Append  (cost=499083.23..523779.32 rows=5153 width=490) (actual time=20538.949..31988.845 rows=65270 loops=1)"
"        Buffers: shared hit=2302480 read=905637, temp read=33523 written=33066"
"        ->  Subquery Scan on "*SELECT* 1"  (cost=499083.23..499583.23 rows=5000 width=441) (actual time=20538.947..20745.331 rows=65185 loops=1)"
"              Buffers: shared hit=2302480 read=374450, temp read=30565 written=30096"
"              ->  WindowAgg  (cost=499083.23..499495.73 rows=5000 width=401) (actual time=20538.943..20727.954 rows=65185 loops=1)"
"                    Buffers: shared hit=2302480 read=374450, temp read=30565 written=30096"
"                    ->  Sort  (cost=499083.23..499095.73 rows=5000 width=377) (actual time=20538.667..20561.559 rows=65185 loops=1)"
"                          Sort Key: f.product_brand_id, f.product_group_id, sale_order_report_ept.funnel_id, sale_order_report_ept.pages"
"                          Sort Method: external merge  Disk: 15208kB"
"                          Buffers: shared hit=2302480 read=374450, temp read=30200 written=29788"
"                          ->  WindowAgg  (cost=498663.54..498776.04 rows=5000 width=377) (actual time=20428.724..20485.944 rows=65185 loops=1)"
"                                Buffers: shared hit=2302480 read=374450, temp read=28299 written=27880"
"                                ->  Sort  (cost=498663.54..498676.04 rows=5000 width=345) (actual time=20428.350..20438.577 rows=65185 loops=1)"
"                                      Sort Key: f.product_brand_id, f.product_group_id, f.active"
"                                      Sort Method: external merge  Disk: 14792kB"
"                                      Buffers: shared hit=2302480 read=374450, temp read=26749 written=26783"
"                                      ->  WindowAgg  (cost=497831.35..498356.35 rows=5000 width=345) (actual time=20223.861..20395.462 rows=65185 loops=1)"
"                                            Buffers: shared hit=2302480 read=374450, temp read=24900 written=24929"
"                                            ->  GroupAggregate  (cost=497831.35..498256.35 rows=5000 width=305) (actual time=20223.843..20340.007 rows=65185 loops=1)"
"                                                  Group Key: sale_order_report_ept.funnel_id, sale_order_report_ept.pages, pt.name, sale_order_report_ept.id, sale_order_report_ept.name, sale_order_report_ept.country_id, sale_order_report_ept.date, f.id"
"                                                  Buffers: shared hit=2302480 read=374450, temp read=24900 written=24929"
"                                                  ->  Sort  (cost=497831.35..497843.85 rows=5000 width=305) (actual time=20223.819..20247.667 rows=65201 loops=1)"
"                                                        Sort Key: sale_order_report_ept.funnel_id, sale_order_report_ept.pages, pt.name, sale_order_report_ept.id, sale_order_report_ept.name, sale_order_report_ept.country_id, sale_order_report_ept.date, f.i (...)"
"                                                        Sort Method: external merge  Disk: 14176kB"
"                                                        Buffers: shared hit=2302480 read=374450, temp read=24900 written=24929"
"                                                        ->  Merge Left Join  (cost=497444.15..497524.15 rows=5000 width=305) (actual time=20070.057..20108.329 rows=65201 loops=1)"
"                                                              Merge Cond: (sale_order_report_ept.line_id = cogs.sale_line_id)"
"                                                              Buffers: shared hit=2302480 read=374450, temp read=23128 written=23152"
"                                                              ->  Sort  (cost=497384.07..497386.57 rows=1000 width=301) (actual time=10624.025..10636.213 rows=65201 loops=1)"
"                                                                    Sort Key: sale_order_report_ept.line_id"
"                                                                    Sort Method: external merge  Disk: 13720kB"
"                                                                    Buffers: shared hit=11556 read=306442, temp read=22799 written=22822"
"                                                                    ->  Hash Left Join  (cost=497313.61..497334.25 rows=1000 width=301) (actual time=10514.522..10588.994 rows=65201 loops=1)"
"                                                                          Hash Cond: (f.id = pc.funnel_id)"
"                                                                          Buffers: shared hit=11556 read=306442, temp read=21084 written=21100"
"                                                                          ->  Hash Left Join  (cost=70.95..88.93 rows=1000 width=229) (actual time=2084.410..2144.930 rows=65201 loops=1)"
"                                                                                Hash Cond: (sale_order_report_ept.funnel_id = f.id)"
"                                                                                Buffers: shared hit=11556 read=47801, temp read=21084 written=21100"
"                                                                                ->  Hash Left Join  (cost=48.41..63.73 rows=1000 width=160) (actual time=2084.197..2129.464 rows=65201 loops=1)"
"                                                                                      Hash Cond: (pp.product_tmpl_id = pt.id)"
"                                                                                      Buffers: shared hit=11556 read=47786, temp read=21084 written=21100"
"                                                                                      ->  Hash Left Join  (cost=12.83..25.49 rows=1000 width=136) (actual time=2084.010..2117.463 rows=65201 loops=1)"
"                                                                                            Hash Cond: (sale_order_report_ept.product_id = pp.id)"
"                                                                                            Buffers: shared hit=11556 read=47758, temp read=21084 written=21100"
"                                                                                            ->  Function Scan on sale_order_report_ept  (cost=0.25..10.25 rows=1000 width=136) (actual time=2083.910..2095.841 rows=65201 loops=1)"
"                                                                                                  Buffers: shared hit=11554 read=47755, temp read=21084 written=21100"
"                                                                                            ->  Hash  (cost=8.37..8.37 rows=337 width=8) (actual time=0.092..0.092 rows=337 loops=1)"
"                                                                                                  Buckets: 1024  Batches: 1  Memory Usage: 22kB"
"                                                                                                  Buffers: shared hit=2 read=3"
"                                                                                                  ->  Seq Scan on product_product pp  (cost=0.00..8.37 rows=337 width=8) (actual time=0.013..0.057 rows=337 loops=1)"
"                                                                                                        Buffers: shared hit=2 read=3"
"                                                                                      ->  Hash  (cost=31.37..31.37 rows=337 width=32) (actual time=0.183..0.183 rows=337 loops=1)"
"                                                                                            Buckets: 1024  Batches: 1  Memory Usage: 30kB"
"                                                                                            Buffers: shared read=28"
"                                                                                            ->  Seq Scan on product_template pt  (cost=0.00..31.37 rows=337 width=32) (actual time=0.010..0.139 rows=337 loops=1)"
"                                                                                                  Buffers: shared read=28"
"                                                                                ->  Hash  (cost=18.35..18.35 rows=335 width=69) (actual time=0.209..0.209 rows=335 loops=1)"
"                                                                                      Buckets: 1024  Batches: 1  Memory Usage: 44kB"
"                                                                                      Buffers: shared read=15"
"                                                                                      ->  Seq Scan on funnel_management f  (cost=0.00..18.35 rows=335 width=69) (actual time=0.006..0.152 rows=335 loops=1)"
"                                                                                            Buffers: shared read=15"
"                                                                          ->  Hash  (cost=497238.85..497238.85 rows=305 width=76) (actual time=8430.107..8430.107 rows=175 loops=1)"
"                                                                                Buckets: 1024  Batches: 1  Memory Usage: 18kB"
"                                                                                Buffers: shared read=258641"
"                                                                                ->  Subquery Scan on pc  (cost=496640.88..497238.85 rows=305 width=76) (actual time=8423.114..8430.075 rows=175 loops=1)"
"                                                                                      Buffers: shared read=258641"
"                                                                                      ->  GroupAggregate  (cost=496640.88..497235.80 rows=305 width=76) (actual time=8423.114..8430.058 rows=175 loops=1)"
"                                                                                            Group Key: fuc.funnel_id"
"                                                                                            Buffers: shared read=258641"
"                                                                                            ->  Merge Left Join  (cost=496640.88..496963.53 rows=26769 width=28) (actual time=8423.099..8427.673 rows=26264 loops=1)"
"                                                                                                  Merge Cond: (fuc.funnel_id = raw_data_userclicks.funnel_id)"
"                                                                                                  Buffers: shared read=258641"
"                                                                                                  ->  Sort  (cost=4733.29..4800.22 rows=26769 width=12) (actual time=29.545..30.780 rows=26264 loops=1)"
"                                                                                                        Sort Key: fuc.funnel_id"
"                                                                                                        Sort Method: quicksort  Memory: 2000kB"
"                                                                                                        Buffers: shared read=1374"
"                                                                                                        ->  Seq Scan on funnel_unique_clicks fuc  (cost=0.00..2764.66 rows=26769 width=12) (actual time=10.079..26.705 rows=26264 loops=1)"
"                                                                                                              Filter: ((date >= '2019-07-07'::date) AND (date <= '2019-10-07'::date))"
"                                                                                                              Rows Removed by Filter: 66447"
"                                                                                                              Buffers: shared read=1374"
"                                                                                                  ->  Sort  (cost=491907.58..491908.12 rows=214 width=20) (actual time=8393.550..8393.563 rows=175 loops=1)"
"                                                                                                        Sort Key: raw_data_userclicks.funnel_id"
"                                                                                                        Sort Method: quicksort  Memory: 38kB"
"                                                                                                        Buffers: shared read=257267"
"                                                                                                        ->  HashAggregate  (cost=491895.02..491897.16 rows=214 width=20) (actual time=8393.492..8393.513 rows=175 loops=1)"
"                                                                                                              Group Key: raw_data_userclicks.funnel_id"
"                                                                                                              Buffers: shared read=257267"
"                                                                                                              ->  Seq Scan on raw_data_userclicks  (cost=0.00..455212.74 rows=2934582 width=18) (actual time=709.188..5429.022 rows=2928713 loops=1)"
"                                                                                                                    Filter: ((date >= '2019-07-07'::date) AND (date <= '2019-10-07'::date))"
"                                                                                                                    Rows Removed by Filter: 10267670"
"                                                                                                                    Buffers: shared read=257267"
"                                                              ->  Sort  (cost=60.08..62.58 rows=1000 width=12) (actual time=9446.025..9448.089 rows=49301 loops=1)"
"                                                                    Sort Key: cogs.sale_line_id"
"                                                                    Sort Method: quicksort  Memory: 3677kB"
"                                                                    Buffers: shared hit=2290924 read=68008, temp read=329 written=330"
"                                                                    ->  Function Scan on cogs  (cost=0.25..10.25 rows=1000 width=12) (actual time=9440.934..9443.004 rows=49301 loops=1)"
"                                                                          Buffers: shared hit=2290924 read=68008, temp read=329 written=330"
"        ->  Subquery Scan on "*SELECT* 2"  (cost=24160.76..24170.32 rows=153 width=486) (actual time=11238.976..11239.090 rows=85 loops=1)"
"              Buffers: shared read=531187, temp read=2958 written=2970"
"              ->  GroupAggregate  (cost=24160.76..24168.79 rows=153 width=490) (actual time=11238.975..11239.075 rows=85 loops=1)"
"                    Group Key: foo.product_id, foo.p_name, foo.product_group_id, foo.product_brand_id, foo.product_group_name, foo.brand_name"
"                    Buffers: shared read=531187, temp read=2958 written=2970"
"                    ->  Sort  (cost=24160.76..24161.14 rows=153 width=168) (actual time=11238.967..11238.971 rows=85 loops=1)"
"                          Sort Key: foo.product_id, foo.p_name, foo.product_group_id, foo.product_brand_id, foo.product_group_name, foo.brand_name"
"                          Sort Method: quicksort  Memory: 37kB"
"                          Buffers: shared read=531187, temp read=2958 written=2970"
"                          ->  Subquery Scan on foo  (cost=24147.18..24155.21 rows=153 width=168) (actual time=11116.210..11238.909 rows=85 loops=1)"
"                                Buffers: shared read=531187, temp read=2958 written=2970"
"                                ->  GroupAggregate  (cost=24147.18..24153.68 rows=153 width=200) (actual time=11116.210..11238.880 rows=85 loops=1)"
"                                      Group Key: p.id, brand.name, pg.name"
"                                      Buffers: shared read=531187, temp read=2958 written=2970"
"                                      ->  Sort  (cost=24147.18..24147.56 rows=153 width=117) (actual time=11114.849..11162.566 rows=233926 loops=1)"
"                                            Sort Key: p.id, brand.name, pg.name"
"                                            Sort Method: external merge  Disk: 23664kB"
"                                            Buffers: shared read=531187, temp read=2958 written=2970"
"                                            ->  Hash Join  (cost=677.53..24141.63 rows=153 width=117) (actual time=32.354..11021.615 rows=233926 loops=1)"
"                                                  Hash Cond: ((aml.product_id = pp_1.id) AND (pc_1.id = p.categ_id))"
"                                                  Buffers: shared read=531187"
"                                                  ->  Nested Loop  (cost=629.86..24043.22 rows=6643 width=39) (actual time=29.491..10728.991 rows=2444166 loops=1)"
"                                                        Buffers: shared read=531152"
"                                                        ->  Hash Join  (cost=5.62..34.21 rows=1 width=44) (actual time=0.042..0.172 rows=9 loops=1)"
"                                                              Hash Cond: ((split_part((irp.res_id)::text, ','::text, 2))::integer = pc_1.id)"
"                                                              Buffers: shared read=8"
"                                                              ->  Bitmap Heap Scan on ir_property irp  (cost=4.35..32.91 rows=9 width=40) (actual time=0.027..0.090 rows=9 loops=1)"
"                                                                    Recheck Cond: ((name)::text = 'property_stock_valuation_account_id'::text)"
"                                                                    Heap Blocks: exact=5"
"                                                                    Buffers: shared read=7"
"                                                                    ->  Bitmap Index Scan on ir_property_name_index  (cost=0.00..4.35 rows=9 width=0) (actual time=0.018..0.018 rows=9 loops=1)"
"                                                                          Index Cond: ((name)::text = 'property_stock_valuation_account_id'::text)"
"                                                                          Buffers: shared read=2"
"                                                              ->  Hash  (cost=1.12..1.12 rows=12 width=4) (actual time=0.008..0.008 rows=12 loops=1)"
"                                                                    Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                                                                    Buffers: shared read=1"
"                                                                    ->  Seq Scan on product_category pc_1  (cost=0.00..1.12 rows=12 width=4) (actual time=0.005..0.006 rows=12 loops=1)"
"                                                                          Buffers: shared read=1"
"                                                        ->  Bitmap Heap Scan on account_move_line aml  (cost=624.24..23667.29 rows=34172 width=21) (actual time=29.241..1151.341 rows=271574 loops=9)"
"                                                              Recheck Cond: (account_id = (split_part((irp.value_reference)::text, ','::text, 2))::integer)"
"                                                              Filter: ((date <= '2019-10-07'::date) AND (company_id = 1))"
"                                                              Heap Blocks: exact=524439"
"                                                              Buffers: shared read=531144"
"                                                              ->  Bitmap Index Scan on account_move_line_account_id_index  (cost=0.00..615.70 rows=34331 width=0) (actual time=19.156..19.156 rows=271574 loops=9)"
"                                                                    Index Cond: (account_id = (split_part((irp.value_reference)::text, ','::text, 2))::integer)"
"                                                                    Buffers: shared read=6705"
"                                                  ->  Hash  (cost=46.28..46.28 rows=93 width=112) (actual time=1.808..1.808 rows=93 loops=1)"
"                                                        Buckets: 1024  Batches: 1  Memory Usage: 18kB"
"                                                        Buffers: shared read=35"
"                                                        ->  Hash Left Join  (cost=36.48..46.28 rows=93 width=112) (actual time=1.004..1.787 rows=93 loops=1)"
"                                                              Hash Cond: (pg.product_brand_id = brand.id)"
"                                                              Buffers: shared read=35"
"                                                              ->  Hash Left Join  (cost=35.30..44.83 rows=93 width=84) (actual time=0.993..1.762 rows=93 loops=1)"
"                                                                    Hash Cond: (p.product_group_id = pg.id)"
"                                                                    Buffers: shared read=34"
"                                                                    ->  Hash Join  (cost=33.38..42.64 rows=93 width=48) (actual time=0.971..1.723 rows=93 loops=1)"
"                                                                          Hash Cond: (pp_1.product_tmpl_id = p.id)"
"                                                                          Buffers: shared read=33"
"                                                                          ->  Seq Scan on product_product pp_1  (cost=0.00..8.37 rows=337 width=8) (actual time=0.021..0.727 rows=337 loops=1)"
"                                                                                Buffers: shared read=5"
"                                                                          ->  Hash  (cost=32.21..32.21 rows=93 width=44) (actual time=0.945..0.945 rows=93 loops=1)"
"                                                                                Buckets: 1024  Batches: 1  Memory Usage: 15kB"
"                                                                                Buffers: shared read=28"
"                                                                                ->  Seq Scan on product_template p  (cost=0.00..32.21 rows=93 width=44) (actual time=0.011..0.927 rows=93 loops=1)"
"                                                                                      Filter: ((type)::text = 'product'::text)"
"                                                                                      Rows Removed by Filter: 244"
"                                                                                      Buffers: shared read=28"
"                                                                    ->  Hash  (cost=1.41..1.41 rows=41 width=40) (actual time=0.018..0.018 rows=41 loops=1)"
"                                                                          Buckets: 1024  Batches: 1  Memory Usage: 11kB"
"                                                                          Buffers: shared read=1"
"                                                                          ->  Seq Scan on product_group_ept pg  (cost=0.00..1.41 rows=41 width=40) (actual time=0.007..0.012 rows=41 loops=1)"
"                                                                                Buffers: shared read=1"
"                                                              ->  Hash  (cost=1.08..1.08 rows=8 width=36) (actual time=0.008..0.008 rows=8 loops=1)"
"                                                                    Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                                                                    Buffers: shared read=1"
"                                                                    ->  Seq Scan on product_brand_ept brand  (cost=0.00..1.08 rows=8 width=36) (actual time=0.005..0.006 rows=8 loops=1)"
"                                                                          Buffers: shared read=1"
"Planning Time: 11.155 ms"
"Execution Time: 32118.703 ms"

Does anyone have any idea?

  • Could you please give your query ? – Arkhena Oct 11 at 8:49
  • Please paste the result of EXPLAIN (ANALYZE, BUFFERS) for your query. At first glance, I suspect the many functions as the cause of misestimates: the optimizer cannot estimate function results. – Laurenz Albe Oct 11 at 11:03
  • Why did you remove the query? – a_horse_with_no_name Oct 11 at 12:46
  • @LaurenzAlbe Please see the updated question. – Dhaduk Mitesh Oct 11 at 13:05
0

Please check below section has been properly written. In short you may have to check the joins are proper as I do not see any of the tables are joined for the selected table, the below section. If unexpected cartesian joins visible then you may have to consider introducing proper joins.

SELECT sale_order_report_ept.product_id,
            sale_order_report_ept.row_id,
            sale_order_report_ept.product_uom_qty,
            sale_order_report_ept.name,
            sale_order_report_ept.router_id,
            sale_order_report_ept.date,
            sale_order_report_ept.picking_done_date,
            sale_order_report_ept.funnel_id,
            sale_order_report_ept.product_group_id,
            sale_order_report_ept.product_group_name,
            sale_order_report_ept.brand_id,
            sale_order_report_ept.brand_name,
            sale_order_report_ept.revenue,
            sale_order_report_ept.revenue_with_tax,
            sale_order_report_ept.original_amount,
            sale_order_report_ept.refund_amount,
            sale_order_report_ept.pages,
            sale_order_report_ept.checkout_line,
            sale_order_report_ept.upsell_line,
            sale_order_report_ept.id,
            sale_order_report_ept.line_count,
            sale_order_report_ept.cancel_order_id,
            sale_order_report_ept.go_id,
            sale_order_report_ept.line_id,
            sale_order_report_ept.medium_id,
            sale_order_report_ept.source_id,
            sale_order_report_ept.country_id,
            sale_order_report_ept.payment_acquirer_id
           FROM sale_order_report_ept('2019-09-20'::date, '2019-10-10'::date, 'confirm_date'::text) sale_order_report_ept(product_id, row_id, product_uom_qty, name, router_id, date, picking_done_date, funnel_id, product_group_id, product_group_name, brand_id, brand_name, revenue, revenue_with_tax, original_amount, refund_amount, pages, checkout_line, upsell_line, id, line_count, cancel_order_id, go_id, line_id, medium_id, source_id, country_id, payment_acquirer_id)
0

There are two low level steps which take up much of your run time.

->  Function Scan on cogs  (cost=0.25..10.25 rows=1000 width=12) (actual time=9440.934..9443.004 rows=49301 loops=1)

We have no visibility into what that function does. You will have to look into it yourself, or show us the source code

->  Bitmap Heap Scan on account_move_line aml  (cost=624.24..23667.29 rows=34172 width=21) (actual time=29.241..1151.341 rows=271574 loops=9)
      Filter: ((date <= '2019-10-07'::date) AND (company_id = 1))
      Heap Blocks: exact=524439"
      Buffers: shared read=531144"

I was thinking that an index on (account_id, company_id, date) will probably speed this up considerably as you will be able to apply those conditions inside the index, rather than as a filter to the table, but now I think not because the filter doesn't seem to remove any rows. But I find still it odd that you visit 524439 blocks to fetch 271574 (half as many) rows. Is this table vacuumed?

  • Is this table vacuumed? >> YES – Dhaduk Mitesh Oct 12 at 6:38
0

Few possibilities:

Rows Removed by Filter: 88963

This is filtering large number of rows. You may want to ensure that this filtering happens very early in the query. You can trick the planner by using OFFSET 0 in your subqueries.


AND p.type::TEXT = 'product'::TEXT

LEFT JOINed table having WHERE condition may not result correctly, unless you are pretty sure you want it that way else you have to move the WHERE condition in JOIN ON clause


Seq Scan on funnel_unique_clicks fuc (cost=0.00..2,764.66 rows=4,025 width=12) (actual time=15.379..17.303 rows=3,748 loops=1)
Filter: ((date >= '2019-09-20'::date) AND (date <= '2019-10-10'::date))
Rows Removed by Filter: 88963

This sequential scan is on good amount of rows. Adding an index here might help.


Function Scan on cogs (cost=0.25..10.25 rows=1,000 width=12) (actual time=11,128.663..11,128.873 rows=5,589 loops=1)

Presense of function prevents planner to use the index. You can add the function in the index itself so planner can use the index if its present.

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.