php网站文件夹结构,wordpress 自定义结构 标题,锦江会员通app下载,产品设计方案范本简介
SQL优化中读懂执行计划尤其重要#xff0c;以下举例说明在执行计划中常见的参数其所代表的含义。
创建测试数据
-- 创建测试表
drop table if exists customers ;
drop table if exists orders ;
drop table if exists order_items ;
drop table if exists products ;…简介
SQL优化中读懂执行计划尤其重要以下举例说明在执行计划中常见的参数其所代表的含义。
创建测试数据
-- 创建测试表
drop table if exists customers ;
drop table if exists orders ;
drop table if exists order_items ;
drop table if exists products ;
CREATE TABLE customers (customer_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,registration_date DATE NOT NULL
);
CREATE TABLE orders (order_id SERIAL PRIMARY KEY,customer_id INT REFERENCES customers(customer_id),order_date DATE NOT NULL,total_amount NUMERIC(10, 2) NOT NULL
);
CREATE TABLE order_items (order_item_id SERIAL PRIMARY KEY,order_id INT REFERENCES orders(order_id),product_name VARCHAR(100) NOT NULL,quantity INT NOT NULL,price_per_item NUMERIC(10, 2) NOT NULL
);
CREATE TABLE products (product_id SERIAL PRIMARY KEY,product_name VARCHAR(100) NOT NULL,product_category VARCHAR(100) NOT NULL,price NUMERIC(10, 2) NOT NULL
);-- 插入数据
INSERT INTO customers (name, registration_date)
VALUES
(Alice, 2022-01-10),
(Bob, 2022-01-15),
(Charlie, 2022-01-20);
INSERT INTO products (product_name, product_category, price)
VALUES
(Laptop, Electronics, 1200.00),
(Headphones, Electronics, 150.00),
(Coffee Maker, Home Appliances, 80.00);
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
(1, 2022-01-12, 1350.00),
(2, 2022-01-16, 80.00),
(3, 2022-01-21, 1200.00);INSERT INTO order_items (order_id, product_name, quantity, price_per_item)
VALUES
(1, Laptop, 1, 1200.00),
(1, Headphones, 1, 150.00),
(2, Coffee Maker, 1, 80.00),
(3, Laptop, 1, 1200.00);
创建测试SQL
SELECTc.customer_id,c.name AS customer_name,EXTRACT(YEAR FROM o.order_date) AS order_year,COUNT(o.order_id) AS total_orders,SUM(o.total_amount) AS total_spent,COUNT(oi.order_item_id) AS total_order_items
FROMcustomers c
JOINorders o ON c.customer_id o.customer_id
JOINorder_items oi ON o.order_id oi.order_id
GROUP BYc.customer_id, c.name, EXTRACT(YEAR FROM o.order_date)
HAVINGSUM(o.total_amount) 500
ORDER BYtotal_spent DESC;
可选参数 explain (ANALYZEBUFFERSCOSTSFORMATGENERIC_PLANSETTINGSSUMMARYTIMINGVERBOSEWAL
ANALYZE:
执行查询并返回实际执行时间和行数统计信息。通过 ANALYZE 参数您可以得到实际执行的时间、读取的行数以及执行的次数。
BUFFERS:
显示每一步骤的缓冲区Buffer访问情况包括共享缓冲区、临时缓冲区的读取与写入。这能帮助您分析查询是否大量访问磁盘或者是否存在频繁的内存缓冲区使用。
COSTS :
显示估计的执行成本包括启动成本执行查询前的初始成本和总成本。默认情况下EXPLAIN 会显示成本使用 COSTS 参数可以控制是否显示这些成本。
FORMAT :
可以指定输出的格式常用的格式有 JSON TEXT XML YAML 。这对于自动化系统解析查询计划非常有用。
GENERIC_PLAN:
示 EXPLAIN 使用的通用查询计划而不是为特定参数生成的计划。对于预处理语句或准备好的查询使用此选项可以查看 PostgreSQL 生成的通用计划。
SETTINGS:
显示在执行查询时使用的设置如 work_mem、max_parallel_workers_per_gather 等。这有助于了解查询执行时使用了哪些配置参数。
SUMMARY:
显示执行计划的总结信息包括计划时间、执行时间、缓冲区统计等。默认情况下SUMMARY 会显示。
TIMING:
显示每个执行步骤的时间默认是启用的。禁用后可以减少执行计划输出的细节这在某些场景下有助于简化分析。
VERBOSE:
显示更多详细信息包括表名、索引名和每个扫描步骤涉及的列名。适合进行详细调试。
WAL:
显示查询生成了多少 WAL预写日志活动。这对于调试写操作尤其有用可以分析查询对 WAL 的影响。
列举示例 postgres# explain (ANALYZE)
postgres-# SELECT
postgres-# c.customer_id,
postgres-# c.name AS customer_name,
postgres-# EXTRACT(YEAR FROM o.order_date) AS order_year,
postgres-# COUNT(o.order_id) AS total_orders,
postgres-# SUM(o.total_amount) AS total_spent,
postgres-# COUNT(oi.order_item_id) AS total_order_items
postgres-# FROM
postgres-# customers c
postgres-# JOIN
postgres-# orders o ON c.customer_id o.customer_id
postgres-# JOIN
postgres-# order_items oi ON o.order_id oi.order_id
postgres-# GROUP BY
postgres-# c.customer_id, c.name, EXTRACT(YEAR FROM o.order_date)
postgres-# HAVING
postgres-# SUM(o.total_amount) 500
postgres-# ORDER BY
postgres-# total_spent DESC;QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------Sort (cost86.40..86.64 rows97 width302) (actual time0.055..0.057 rows2 loops1)Sort Key: (sum(o.total_amount)) DESCSort Method: quicksort Memory: 25kB- HashAggregate (cost78.61..83.20 rows97 width302) (actual time0.046..0.049 rows2 loops1)Group Key: c.customer_id, EXTRACT(year FROM o.order_date)Filter: (sum(o.total_amount) 500::numeric)Batches: 1 Memory Usage: 37kBRows Removed by Filter: 1- Hash Join (cost59.83..74.98 rows290 width278) (actual time0.034..0.038 rows4 loops1)Hash Cond: (o.customer_id c.customer_id)- Hash Join (cost42.62..56.29 rows290 width32) (actual time0.014..0.016 rows4 loops1)Hash Cond: (oi.order_id o.order_id)- Seq Scan on order_items oi (cost0.00..12.90 rows290 width8) (actual time0.003..0.004 rows4 loops1)- Hash (cost24.50..24.50 rows1450 width28) (actual time0.004..0.005 rows3 loops1)Buckets: 2048 Batches: 1 Memory Usage: 17kB- Seq Scan on orders o (cost0.00..24.50 rows1450 width28) (actual time0.003..0.003 rows3 loops1)- Hash (cost13.20..13.20 rows320 width222) (actual time0.015..0.015 rows3 loops1)Buckets: 1024 Batches: 1 Memory Usage: 9kB- Seq Scan on customers c (cost0.00..13.20 rows320 width222) (actual time0.007..0.008 rows3 loops1)Planning Time: 0.301 msExecution Time: 0.097 ms
(21 rows)
以上案例为例PG执行计划遵循从下到上从里到外的规则。 - Hash (cost24.50..24.50 rows1450 width28) (actual time0.004..0.005 rows3 loops1)Buckets: 2048 Batches: 1 Memory Usage: 17kB- Seq Scan on orders o (cost0.00..24.50 rows1450 width28) (actual time0.003..0.003 rows3 loops1)
Seq Scan on orders 对表orders顺序扫描 (cost0.00…24.50 rows1450 width28) 预估耗费成本rows1450预估扫描行1450 width28预估扫描每行的宽度以字节为单位即每行大约占用 28字节。 (actual time0.003…0.003 rows3 loops1) 实际消费成本0.003…0.003 0.003排序开始时间0.003排序结束时间
对orders表扫描结束之后会对其进行构建hash,常被用关联、嵌套等情况。 (cost24.50…24.50 rows1450 width28) (actual time0.004…0.005 rows3 loops1) 这两段解释同上相同 Buckets: 2048 Batches: 1 Memory Usage: 17kB Buckets: 2048 表示哈希表中有 2048 个桶每个桶用于存储散列结果相同的行。这是哈希表的一部分设计用于分配空间。 Batches: 1 表示只需一批数据处理因为表的大小足够小整个哈希表可以存储在内存中。如果数据量非常大PostgreSQL 可能会将哈希表分成多个批次处理以防止内存不足。 Memory Usage: 17kB 是哈希表在内存中的大小显示这次哈希操作所消耗的内存为 17KB。 - Hash (cost13.20..13.20 rows320 width222) (actual time0.015..0.015 rows3 loops1)Buckets: 1024 Batches: 1 Memory Usage: 9kB- Seq Scan on customers c (cost0.00..13.20 rows320 width222) (actual time0.007..0.008 rows3 loops1)Seq Scan on customers c 对customers 表进行顺序扫描 (cost0.00…13.20 rows320 width222) cost0.00…13.20 扫描耗费的成本 rows320 这是估计需要排序的行数 width222这是估算每行的宽度以字节为单位即每行大约占用 222字节。 actual time0.007…0.008 为实际耗时,0.007毫秒是排序开始的时间0.008毫秒是排序结束的时间。 rows3 表示查询实际排序的行数是 2 行。 loops1 表示实际执行了一次 Sort (cost86.40..86.64 rows97 width302) (actual time0.055..0.057 rows2 loops1)Sort Key: (sum(o.total_amount)) DESCSort Method: quicksort Memory: 25kB- HashAggregate (cost78.61..83.20 rows97 width302) (actual time0.046..0.049 rows2 loops1)Group Key: c.customer_id, EXTRACT(year FROM o.order_date)Filter: (sum(o.total_amount) 500::numeric)Batches: 1 Memory Usage: 37kBRows Removed by Filter: 1
HashAggregate (cost78.61…83.20 rows97 width302) (actual time0.046…0.049 rows2 loops1) 表示进行聚合操作所需要的耗费。 Group Key: c.customer_id, EXTRACT(year FROM o.order_date) Group Key 表示使用的分组键 Filter: (sum(o.total_amount) ‘500’::numeric) 表示用的过滤条件 Batches: 1 Memory Usage: 37kB 表示哈希聚合操作占用了 37kB 内存 Rows Removed by Filter: 1 被过滤掉的数据行数
此执行计划可以看出其预估行rows 和实际的rows 有较大的出入。实际上统计信息不是最新信息的问题导致重新对其analyze单独执行重新收集一下统计信息就可以。 当执行vacuum full之后也会自动进行analyze postgres# explain (ANALYZE)
postgres-# SELECT
postgres-# c.customer_id,
postgres-# c.name AS customer_name,
postgres-# EXTRACT(YEAR FROM o.order_date) AS order_year,
postgres-# COUNT(o.order_id) AS total_orders,
postgres-# SUM(o.total_amount) AS total_spent,
postgres-# COUNT(oi.order_item_id) AS total_order_items
postgres-# FROM
postgres-# customers c
postgres-# JOIN
postgres-# orders o ON c.customer_id o.customer_id
postgres-# JOIN
postgres-# order_items oi ON o.order_id oi.order_id
postgres-# GROUP BY
postgres-# c.customer_id, c.name, EXTRACT(YEAR FROM o.order_date)
postgres-# HAVING
postgres-# SUM(o.total_amount) 500
postgres-# ORDER BY
postgres-# total_spent DESC;QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------Sort (cost3.35..3.36 rows1 width302) (actual time0.063..0.065 rows2 loops1)Sort Key: (sum(o.total_amount)) DESCSort Method: quicksort Memory: 25kB- HashAggregate (cost3.28..3.34 rows1 width302) (actual time0.052..0.055 rows2 loops1)Group Key: c.customer_id, EXTRACT(year FROM o.order_date)Filter: (sum(o.total_amount) 500::numeric)Batches: 1 Memory Usage: 24kBRows Removed by Filter: 1- Hash Join (cost2.14..3.23 rows4 width278) (actual time0.037..0.041 rows4 loops1)Hash Cond: (o.customer_id c.customer_id)- Hash Join (cost1.07..2.13 rows4 width32) (actual time0.014..0.016 rows4 loops1)Hash Cond: (oi.order_id o.order_id)- Seq Scan on order_items oi (cost0.00..1.04 rows4 width8) (actual time0.002..0.003 rows4 loops1)- Hash (cost1.03..1.03 rows3 width28) (actual time0.005..0.005 rows3 loops1)Buckets: 1024 Batches: 1 Memory Usage: 9kB- Seq Scan on orders o (cost0.00..1.03 rows3 width28) (actual time0.003..0.004 rows3 loops1)- Hash (cost1.03..1.03 rows3 width222) (actual time0.014..0.014 rows3 loops1)Buckets: 1024 Batches: 1 Memory Usage: 9kB- Seq Scan on customers c (cost0.00..1.03 rows3 width222) (actual time0.010..0.010 rows3 loops1)Planning Time: 0.568 msExecution Time: 0.129 ms
(21 rows)
增加buffers参数之后的执行计划会增加Buffers: shared hit3 用于记录SQL在执行数据存取过程中使用到了多少个数据块
postgres# explain (ANALYZE,BUFFERS)
postgres-# SELECT
postgres-# c.customer_id,
postgres-# c.name AS customer_name,
postgres-# EXTRACT(YEAR FROM o.order_date) AS order_year,
postgres-# COUNT(o.order_id) AS total_orders,
postgres-# SUM(o.total_amount) AS total_spent,
postgres-# COUNT(oi.order_item_id) AS total_order_items
postgres-# FROM
postgres-# customers c
postgres-# JOIN
postgres-# orders o ON c.customer_id o.customer_id
postgres-# JOIN
postgres-# order_items oi ON o.order_id oi.order_id
postgres-# GROUP BY
postgres-# c.customer_id, c.name, EXTRACT(YEAR FROM o.order_date)
postgres-# HAVING
postgres-# SUM(o.total_amount) 500
postgres-# ORDER BY
postgres-# total_spent DESC;QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------Sort (cost3.35..3.36 rows1 width302) (actual time0.072..0.074 rows2 loops1)Sort Key: (sum(o.total_amount)) DESCSort Method: quicksort Memory: 25kBBuffers: shared hit3- HashAggregate (cost3.28..3.34 rows1 width302) (actual time0.053..0.056 rows2 loops1)Group Key: c.customer_id, EXTRACT(year FROM o.order_date)Filter: (sum(o.total_amount) 500::numeric)Batches: 1 Memory Usage: 24kBRows Removed by Filter: 1Buffers: shared hit3- Hash Join (cost2.14..3.23 rows4 width278) (actual time0.038..0.043 rows4 loops1)Hash Cond: (o.customer_id c.customer_id)Buffers: shared hit3- Hash Join (cost1.07..2.13 rows4 width32) (actual time0.013..0.016 rows4 loops1)Hash Cond: (oi.order_id o.order_id)Buffers: shared hit2- Seq Scan on order_items oi (cost0.00..1.04 rows4 width8) (actual time0.003..0.004 rows4 loops1)Buffers: shared hit1- Hash (cost1.03..1.03 rows3 width28) (actual time0.005..0.005 rows3 loops1)Buckets: 1024 Batches: 1 Memory Usage: 9kBBuffers: shared hit1- Seq Scan on orders o (cost0.00..1.03 rows3 width28) (actual time0.003..0.004 rows3 loops1)Buffers: shared hit1- Hash (cost1.03..1.03 rows3 width222) (actual time0.013..0.014 rows3 loops1)Buckets: 1024 Batches: 1 Memory Usage: 9kBBuffers: shared hit1- Seq Scan on customers c (cost0.00..1.03 rows3 width222) (actual time0.009..0.010 rows3 loops1)Buffers: shared hit1Planning:Buffers: shared hit2Planning Time: 0.247 msExecution Time: 0.124 ms增加COSTS参数之后的执行计划中增加了 I/O Timings: shared read 用于解释将数据读取缓存到缓存所需要的时间耗费
Sort (cost3.35..3.36 rows1 width302) (actual time0.057..0.059 rows2 loops1)Sort Key: (sum(o.total_amount)) DESCSort Method: quicksort Memory: 25kBBuffers: shared hit3- HashAggregate (cost3.28..3.34 rows1 width302) (actual time0.049..0.051 rows2 loops1)Group Key: c.customer_id, EXTRACT(year FROM o.order_date)Filter: (sum(o.total_amount) 500::numeric)Batches: 1 Memory Usage: 24kBRows Removed by Filter: 1Buffers: shared hit3- Hash Join (cost2.14..3.23 rows4 width278) (actual time0.036..0.041 rows4 loops1)Hash Cond: (o.customer_id c.customer_id)Buffers: shared hit3- Hash Join (cost1.07..2.13 rows4 width32) (actual time0.018..0.021 rows4 loops1)Hash Cond: (oi.order_id o.order_id)Buffers: shared hit2- Seq Scan on order_items oi (cost0.00..1.04 rows4 width8) (actual time0.002..0.003 rows4 loops1)Buffers: shared hit1- Hash (cost1.03..1.03 rows3 width28) (actual time0.004..0.004 rows3 loops1)Buckets: 1024 Batches: 1 Memory Usage: 9kBBuffers: shared hit1- Seq Scan on orders o (cost0.00..1.03 rows3 width28) (actual time0.002..0.003 rows3 loops1)Buffers: shared hit1- Hash (cost1.03..1.03 rows3 width222) (actual time0.011..0.011 rows3 loops1)Buckets: 1024 Batches: 1 Memory Usage: 9kBBuffers: shared hit1- Seq Scan on customers c (cost0.00..1.03 rows3 width222) (actual time0.007..0.008 rows3 loops1)Buffers: shared hit1Planning:Buffers: shared hit43 read3I/O Timings: shared read0.035Planning Time: 0.432 msExecution Time: 0.114 ms
I/O Timings: shared read0.035 表示查询过程中从磁盘读取到共享缓冲区的数据块总耗时为 0.035 毫秒。这个时间量通常是从物理磁盘读取数据的花费。I/O 时间越短说明磁盘 I/O 性能越好。 增加FORMAT 修改执行计划输出格式为json格式
explain (ANALYZE,BUFFERS,COSTS,VERBOSE,WAL,FORMAT JSON)SELECTc.customer_id,c.name AS customer_name,EXTRACT(YEAR FROM o.order_date) AS order_year,COUNT(o.order_id) AS total_orders,SUM(o.total_amount) AS total_spent,COUNT(oi.order_item_id) AS total_order_items
FROMcustomers c
JOINorders o ON c.customer_id o.customer_id
JOINorder_items oi ON o.order_id oi.order_id
GROUP BYc.customer_id, c.name, EXTRACT(YEAR FROM o.order_date)
HAVINGSUM(o.total_amount) 500
ORDER BYtotal_spent DESC; 文章转载自： http://www.morning.ydyjf.cn.gov.cn.ydyjf.cn http://www.morning.wxccm.cn.gov.cn.wxccm.cn http://www.morning.ntzfj.cn.gov.cn.ntzfj.cn http://www.morning.clnmf.cn.gov.cn.clnmf.cn http://www.morning.lxhrq.cn.gov.cn.lxhrq.cn http://www.morning.rnzjc.cn.gov.cn.rnzjc.cn http://www.morning.rqfzp.cn.gov.cn.rqfzp.cn http://www.morning.ntgjm.cn.gov.cn.ntgjm.cn http://www.morning.drwpn.cn.gov.cn.drwpn.cn http://www.morning.xsszn.cn.gov.cn.xsszn.cn http://www.morning.cwrnr.cn.gov.cn.cwrnr.cn http://www.morning.wnqbf.cn.gov.cn.wnqbf.cn http://www.morning.qctsd.cn.gov.cn.qctsd.cn http://www.morning.rcgzg.cn.gov.cn.rcgzg.cn http://www.morning.npcxk.cn.gov.cn.npcxk.cn http://www.morning.cwjsz.cn.gov.cn.cwjsz.cn http://www.morning.rwjh.cn.gov.cn.rwjh.cn http://www.morning.touziyou.cn.gov.cn.touziyou.cn http://www.morning.nclbk.cn.gov.cn.nclbk.cn http://www.morning.rqhdt.cn.gov.cn.rqhdt.cn http://www.morning.lhxrn.cn.gov.cn.lhxrn.cn http://www.morning.rpwht.cn.gov.cn.rpwht.cn http://www.morning.nssjy.cn.gov.cn.nssjy.cn http://www.morning.mnrqq.cn.gov.cn.mnrqq.cn http://www.morning.tfcwj.cn.gov.cn.tfcwj.cn http://www.morning.xjnjb.cn.gov.cn.xjnjb.cn http://www.morning.dkqyg.cn.gov.cn.dkqyg.cn http://www.morning.srgnd.cn.gov.cn.srgnd.cn http://www.morning.mrckk.cn.gov.cn.mrckk.cn http://www.morning.kgxrq.cn.gov.cn.kgxrq.cn http://www.morning.xqwq.cn.gov.cn.xqwq.cn http://www.morning.kgltb.cn.gov.cn.kgltb.cn http://www.morning.xwlhc.cn.gov.cn.xwlhc.cn http://www.morning.pclgj.cn.gov.cn.pclgj.cn http://www.morning.rkxdp.cn.gov.cn.rkxdp.cn http://www.morning.wlsrd.cn.gov.cn.wlsrd.cn http://www.morning.qfqld.cn.gov.cn.qfqld.cn http://www.morning.lthgy.cn.gov.cn.lthgy.cn http://www.morning.qrpx.cn.gov.cn.qrpx.cn http://www.morning.21r000.cn.gov.cn.21r000.cn http://www.morning.hwlmy.cn.gov.cn.hwlmy.cn http://www.morning.nshhf.cn.gov.cn.nshhf.cn http://www.morning.wlggr.cn.gov.cn.wlggr.cn http://www.morning.dnvhfh.cn.gov.cn.dnvhfh.cn http://www.morning.mhmsn.cn.gov.cn.mhmsn.cn http://www.morning.tqjwx.cn.gov.cn.tqjwx.cn http://www.morning.ckntb.cn.gov.cn.ckntb.cn http://www.morning.fnlnp.cn.gov.cn.fnlnp.cn http://www.morning.jnkng.cn.gov.cn.jnkng.cn http://www.morning.nccqs.cn.gov.cn.nccqs.cn http://www.morning.snlxb.cn.gov.cn.snlxb.cn http://www.morning.wdjcr.cn.gov.cn.wdjcr.cn http://www.morning.ljglc.cn.gov.cn.ljglc.cn http://www.morning.qineryuyin.com.gov.cn.qineryuyin.com http://www.morning.rbmnq.cn.gov.cn.rbmnq.cn http://www.morning.nhzzn.cn.gov.cn.nhzzn.cn http://www.morning.nnrqg.cn.gov.cn.nnrqg.cn http://www.morning.ygkk.cn.gov.cn.ygkk.cn http://www.morning.ljsxg.cn.gov.cn.ljsxg.cn http://www.morning.pndhh.cn.gov.cn.pndhh.cn http://www.morning.xqknl.cn.gov.cn.xqknl.cn http://www.morning.zmpqt.cn.gov.cn.zmpqt.cn http://www.morning.zycll.cn.gov.cn.zycll.cn http://www.morning.jqcrf.cn.gov.cn.jqcrf.cn http://www.morning.mnrqq.cn.gov.cn.mnrqq.cn http://www.morning.nzkkh.cn.gov.cn.nzkkh.cn http://www.morning.sqqkr.cn.gov.cn.sqqkr.cn http://www.morning.twgzq.cn.gov.cn.twgzq.cn http://www.morning.rjnrf.cn.gov.cn.rjnrf.cn http://www.morning.hmtft.cn.gov.cn.hmtft.cn http://www.morning.fgkwh.cn.gov.cn.fgkwh.cn http://www.morning.wztlr.cn.gov.cn.wztlr.cn http://www.morning.kybyf.cn.gov.cn.kybyf.cn http://www.morning.hwhnx.cn.gov.cn.hwhnx.cn http://www.morning.gnzsd.cn.gov.cn.gnzsd.cn http://www.morning.0small.cn.gov.cn.0small.cn http://www.morning.rkmsm.cn.gov.cn.rkmsm.cn http://www.morning.mlnzx.cn.gov.cn.mlnzx.cn http://www.morning.qbmpb.cn.gov.cn.qbmpb.cn http://www.morning.syqtt.cn.gov.cn.syqtt.cn
