Postgresql
查詢執行時間過長
我添加了我的查詢的查詢計劃。這需要太多時間來執行和提供數據。 https://explain.depesz.com/s/4JtZ
我想減少這個查詢的執行時間。所以需要優化這個查詢。
"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"有誰有想法嗎？
請檢查以下部分是否已正確編寫。簡而言之，您可能需要檢查連接是否正確，因為我沒有看到任何表已為所選表連接，下一節。如果意外的笛卡爾連接可見，那麼您可能必須考慮引入正確的連接。
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)
有兩個低級步驟會佔用您的大部分執行時間。
-> Function Scan on cogs (cost=0.25..10.25 rows=1000 width=12) (actual time=9440.934..9443.004 rows=49301 loops=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) Filter: ((date <= '2019-10-07'::date) AND (company_id = 1)) Heap Blocks: exact=524439" Buffers: shared read=531144"我在想 (account_id, company_id, date) 上的索引可能會大大加快這一速度，因為您將能夠在索引中應用這些條件，而不是作為表的過濾器，但現在我認為不是因為過濾器似乎沒有刪除任何行。但是我覺得你訪問 524439 個塊來獲取 271574（一半）行仍然很奇怪。這張桌子是吸塵的嗎？