作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我搜索了一下我的问题,但找不到解决方案。
我在 x86_64-unknown-linux-gnu 上运行 PostgreSQL 9.2.4,由 gcc (Debian 4.7.2-5) 4.7.2, 64-bit 编译
,我的查询非常简单.
EXPLAIN (ANALYZE) SELECT CUSTOMER, PRICE, BUYDATE FROM dbo.Invoice WHERE CUSTOMER = 11111111 AND BUYDATE BETWEEN '2012-11-01' AND '2013-10-31';
输出:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on Invoice (cost=88193.54..152981.03 rows=20699 width=14) (actual time=987.205..987.242 rows=36 loops=1)
Recheck Cond: ((CUSTOMER = 11111111) AND (BUYDATE >= '2012-11-01'::date) AND (BUYDATE <= '2013-10-31'::date))
-> BitmapAnd (cost=88193.54..88193.54 rows=20699 width=0) (actual time=987.189..987.189 rows=0 loops=1)
-> Bitmap Index Scan on ix_Invoice (cost=0.00..1375.69 rows=74375 width=0) (actual time=0.043..0.043 rows=40 loops=1)
Index Cond: (CUSTOMER = 11111111)
-> Bitmap Index Scan on ix_Invoice3 (cost=0.00..86807.24 rows=4139736 width=0) (actual time=986.562..986.562 rows=4153999 loops=1)
Index Cond: ((BUYDATE >= '2012-11-01'::date) AND (BUYDATE <= '2013-10-31'::date))
Total runtime: 987.294 ms
(8 rows)
表结构:
Column | Type | Modifiers | Storage | Stats target | Description
-----------------+---------------------------+-----------+----------+--------------+-------------
profitcenter | character varying(5) | not null | extended | |
invoicenumber | character varying(10) | not null | extended | |
invoiceposition | character varying(6) | not null | extended | |
buydate | date | not null | plain | |
customer | integer | | plain | |
nettowert | numeric(18,2) | | main | |
Indexes:
"filialbons_key" PRIMARY KEY, btree (profitcenter, invoicenumber, invoiceposition, buydate)
"ix_Invoice" btree (customer)
"ix_Invoice2" btree (invoicenumber)
"ix_Invoice3" btree (buydate)
"ix_Invoice4" btree (articlenumber)
Has OIDs: no
查询的示例输出:
customer | price | buydate
--------------+-----------+----
11111111 | 8.32 | 2013-02-06
11111111 | 5.82 | 2013-02-06
11111111 | 16.64 | 2013-02-06
我在 MSSQL 2010 上运行了相同的查询?将日期列表示为 varchar(),速度要快得多。
谢谢你的帮助
最佳答案
您可以尝试通过收集更多的统计数据来帮助计划者选择更好的计划:
ALTER TABLE 发票 ALTER COLUMN 客户 SET STATISTICS 1000;
ALTER TABLE 发票 ALTER COLUMN buydate SET STATISTICS 1000;
分析发票;
关于postgresql - DATE Between 的慢速选择查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19977909/
我是一名优秀的程序员,十分优秀!