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?
Best Answer
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.