postgresql - 即使查询使用了索引扫描，需要 40 秒才能获得 1000 条记录

Question

我的表创建脚本如下，我的表根据销售时间按月分区。平均一天包含 300 万条记录。完全我的表包含 10 亿条记录

CREATE TABLE MyTable(
Id bigint not null,
Col1 text,
Col2 text,
Col3 text,
Col4 text,
Col5 text,
Col6 text,
Col7 text,
Col8 text,
Col9 text,
Saletime timestamp without timezone,
CONSTRAINT pk_tbl_mytable PRIMARY KEY(id,saletime)
Usint INDEX TABLESPACE “MY_TS”
)PARTIOTION BY RANGE(Saletime)
TABLESPACE “MY_TS”;

我的查询如下

with CTE AS(
SELECT Id,SaleTime from tbl_search_log
where saletime>’2019-11-01 00:00:00’ and saletime<’2019-12-30 00:00:00’
and siteid in(708,805,432,403)
order by saletime desc limit 1000 offset 0
)
Select S.Id,col1,col2,col3,col4,col5,col6,col7,col8
from mytable S
join CTE on S.id=CTE.Id
where S.saletime>’2019-11-01 00:00:00’ and S.saletime<’2019-12-30 00:00:00’
order by S.saletime desc
limit 1000;

当我使用上面的查询选择 1000 条记录时，几乎需要 35 到 40 秒 我的查询解释（分析，缓冲区）日志如下

Limit (cost=10924.12. 10926.62 rows=1000 width=209) (actual time=D40650.143..40650.302 rows=1000 loops=1) "

Buffers: shared hit-3034 read-2120"

Sort

(cost=10924.12. . 10926.62 rous-1000 width=209) (actual time=40650.142.40650.212 rows=1000 loops=1)"

Sort Key saletime DESOM

Sort Method: quicksort Memory: 290KB" Buffers: shared hit=3034 read=2120"

Nested Loop (cost=8146.68.. 10874.29 rows=1000 width=209) actual time=D63.635..40643.680 rows=1000 loops=1)" Buffers shared hit=3034 read=2120

Limit (cost=8146.12..8262.79 rows=1000 width=16 actual time=13014 16.019 rows=1000 loops=1)

Buffers: shared hit=154"

-> Gather Merge (cost=8029.44.19796.80 rows=100856 width=16) actual time=12.954..15.511 rows=2000 loops=1)"

workers Planned: 2

Workers Launched: 2

Buffers: shared hit=168"

Sort (cost=7029.42..7155.49 rous=50428 width=16) (actual time-0.872..1.273 roMs=667 loops=3) "

Sort Keys Plc log 201911. saletime DESC Sort Method: top-N heapsort Memory: 190KB" Horker 0: Sort Method: quicksort Memory 25kB

Horker 1: Sort Method: quicksort Memory: 25KB"

Buffers: shared hit=168

Sort (cost=7029.42..7155.49 rous=50428 width=16) (actual time-0.872..1.273 roMs=667 loops=3) "

Sort Keys Plc log 201911. saletime DESC Sort Method: top-N heapsort Memory: 190KB" Horker 0: Sort Method: quicksort Memory 25kB

Horker 1: Sort Method: quicksort Memory: 25KB"

Buffers: shared hit=168

Parallel Index Only Scan using purlog 201911 p_rlog 201911 (cost=0.56..4012.37 rows=50428 width=16) (actual time=0.009..0.517 rows=2786 loops=3)" Index Cond: (siteid = ANY (4708, 805, 345, 403 :: integer [])) AND (saletime 2019-11-01 00:00:00 : : timestamp without time zone) AND

(saletime < '2019-11-30 00:00:00 : timestamp without

time zone))"


Heap Fetches: 0"

Buffers: shared hit=154" ->

Index Scan using p_mytablelog201911 pkey on p_mytablelog201911 s cost=0.56..2.59 rows=1 width=153) (actyal time=40.599..40.602 row3=1 loops=1000) 
Index Cond: ((id = p rlog 201911.id) AND (saletime >= 2019-11-01 00:00:00 Btimestamp without time zone) AND
(saletime <= 2019-11-30 00:00:00 :: timestamp without time zone)) " 
Buffers:shared hit=2880 read=2120
planing time:0.800 ms
Execution Time: 40651.003 ms

请建议我解决这个性能问题。

tbl_search_log 和 MyTable 有近 10 亿条记录。tbl_search_log 在 1 秒内给出结果。但我的桌子只需要更多时间。

注意： 我正在使用 Windows Server OS 和 PostgreSQL 12 32 GB RAM Work_mem 1GB Statistics 10000 Random_Page_Cost 1.0 Shared_Buffers 8GB

标签： postgresqlpostgresql-12