我正在Reddit上收集关于股票的对话，以下是我的帖子
create table stock
(
code varchar(10) not null
constraint "PK_c73240b87ebf65d15337579f9fd"
primary key,
confirmed boolean,
"confirmationTime" timestamp
);
create table conversation
(
id varchar(100) not null
constraint "PK_864528ec4274360a40f66c29845"
primary key,
"createdUtc" timestamp not null,
type conversation_type_enum not null,
votes integer not null,
text varchar not null,
source varchar not null,
"sourceUrl" varchar not null,
"parentId" varchar(100)
constraint "FK_58aa115ae3e7e7c6f79ad38d0c0"
references conversation
on delete cascade
);
create table conversation_stocks_stock
(
"conversationId" varchar(100) not null
constraint "FK_f78b7bf984f96648653812c5500"
references conversation
on update cascade on delete cascade,
"stockCode" varchar(10) not null
constraint "FK_927e043874ad9469e451671a530"
references stock
on update cascade on delete cascade,
constraint "PK_5478028d1f4bf3c91dc0da2849d"
primary key ("conversationId", "stockCode")
);
create index "IDX_f78b7bf984f96648653812c550"
on conversation_stocks_stock ("conversationId");
create index "IDX_927e043874ad9469e451671a53"
on conversation_stocks_stock ("stockCode");
create index conversationCreatedUtc on conversation ("createdUtc");直到最近，我还在用下面的方法计算在某段时间内人们对某只股票的兴趣
CREATE OR REPLACE FUNCTION calculate_stock_interest(stock varchar, start_time timestamp, end_time timestamp) RETURNS integer AS $$
BEGIN
RETURN
(with recursive convo AS (
select id, votes, "parentId", "createdUtc" from conversation
join conversation_stocks_stock css on css."conversationId" = id
where css."stockCode" = stock
and "createdUtc" > start_time and "createdUtc" <= end_time
union
select thread.id, thread.votes, thread."parentId", thread."createdUtc" from conversation thread
join convo com on thread."parentId" = com.id
where thread."createdUtc" > start_time and thread."createdUtc" <= end_time
) select COALESCE((count(*) + sum(convo.votes)), 0) from convo left join conversation_stocks_stock css on convo.id = css."conversationId");
END
$$ LANGUAGE plpgsql
LEAKPROOF
STRICT
PARALLEL SAFE;这会在不到100ms的时间内吐出一个结果，然而我注意到它产生了一个错误的结果。假设一个线程写于2023 - 01 - 03，这个线程下的注解写于2023 - 01 - 05，如果我尝试执行select calculate_stock_interest('AAA', '2023-01-02', '2023-01-04')，函数将返回2，正如预期的那样，然而，对于select calculate_stock_interest('AAA', '2023-01-04', '2023-01-06')，它将返回0。这是因为递归将看到线程在请求的时间跨度之外，并且将不遍历子注解。
所以我用下面的方法重写了这个函数，解决了这个问题
CREATE OR REPLACE FUNCTION calculate_stock_interest(stock varchar, start_time timestamp, end_time timestamp) RETURNS integer AS $$
BEGIN
RETURN
(with recursive convo AS (
select id, votes, "parentId", "createdUtc" from conversation
join conversation_stocks_stock css on css."conversationId" = id
where css."stockCode" = stock
union
select thread.id, thread.votes, thread."parentId", thread."createdUtc" from conversation thread
join convo com on thread."parentId" = com.id
) select
COALESCE((count(*) + sum(convo.votes)), 0)
from convo
left join conversation_stocks_stock css on convo.id = css."conversationId"
where "createdUtc" > start_time and "createdUtc" <= end_time);
END
$$ LANGUAGE plpgsql
LEAKPROOF
STRICT
PARALLEL SAFE;现在我得到了正确的结果，但是......子查询不再命中conversationCreatedUtc。它产生了数千个结果，函数总体上需要几秒钟才能运行......我需要执行函数数千次......
关于如何提高这里的性能有什么提示吗？
- 编辑**
以下查询
explain with recursive convo AS (
select id, votes, "parentId", "createdUtc" from conversation
join conversation_stocks_stock css on css."conversationId" = id
where css."stockCode" = 'AAA'
union
select thread.id, thread.votes, thread."parentId", thread."createdUtc" from conversation thread
join convo com on thread."parentId" = com.id
) select
COALESCE((count(*) + sum(convo.votes)), 0)
from convo
left join conversation_stocks_stock css on convo.id = css."conversationId"
where "createdUtc" > '2022-01-01' and "createdUtc" <= '2022-02-01'给了我
Aggregate (cost=184925.75..184925.76 rows=1 width=8)
CTE convo
-> Recursive Union (cost=4.82..184275.09 rows=7753 width=26)
-> Nested Loop (cost=4.82..161.30 rows=13 width=26)
-> Bitmap Heap Scan on conversation_stocks_stock css_1 (cost=4.40..51.58 rows=13 width=7)
" Recheck Cond: ((""stockCode"")::text = 'AAA'::text)"
" -> Bitmap Index Scan on ""IDX_927e043874ad9469e451671a53"" (cost=0.00..4.39 rows=13 width=0)"
" Index Cond: ((""stockCode"")::text = 'AAA'::text)"
" -> Index Scan using ""PK_864528ec4274360a40f66c29845"" on conversation (cost=0.42..8.44 rows=1 width=26)"
" Index Cond: ((id)::text = (css_1.""conversationId"")::text)"
-> Hash Join (cost=4.23..18395.87 rows=774 width=26)
" Hash Cond: ((thread.""parentId"")::text = (com.id)::text)"
-> Seq Scan on conversation thread (cost=0.00..17345.66 rows=276866 width=26)
-> Hash (cost=2.60..2.60 rows=130 width=218)
-> WorkTable Scan on convo com (cost=0.00..2.60 rows=130 width=218)
-> Nested Loop Left Join (cost=0.29..649.57 rows=219 width=4)
-> CTE Scan on convo (cost=0.00..193.83 rows=39 width=222)
" Filter: ((""createdUtc"" > '2022-01-01 00:00:00'::timestamp without time zone) AND (""createdUtc"" <= '2022-02-01 00:00:00'::timestamp without time zone))"
" -> Index Only Scan using ""IDX_f78b7bf984f96648653812c550"" on conversation_stocks_stock css (cost=0.29..11.63 rows=6 width=7)"
" Index Cond: (""conversationId"" = (convo.id)::text)"
JIT:
Functions: 24
" Options: Inlining false, Optimization false, Expressions true, Deforming true"和analyze verbose得到以下结果：
analyze verbose
analyzing "public.migrations"
"migrations": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows
analyzing "public.conversation"
"conversation": scanned 14577 of 14577 pages, containing 276866 live rows and 2161 dead rows; 30000 rows in sample, 276866 estimated total rows
analyzing "public.conversation_stocks_stock"
"conversation_stocks_stock": scanned 799 of 799 pages, containing 147195 live rows and 727 dead rows; 30000 rows in sample, 147195 estimated total rows
analyzing "public.stock"
"stock": scanned 91 of 91 pages, containing 12778 live rows and 4 dead rows; 12778 rows in sample, 12778 estimated total rows
analyzing "pg_catalog.pg_type"
"pg_type": scanned 15 of 15 pages, containing 613 live rows and 28 dead rows; 613 rows in sample, 613 estimated total rows
analyzing "public.stock_interest"
"stock_interest": scanned 280 of 280 pages, containing 43897 live rows and 0 dead rows; 30000 rows in sample, 43897 estimated total rows
analyzing "pg_catalog.pg_foreign_table"
"pg_foreign_table": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_authid"
"pg_authid": scanned 1 of 1 pages, containing 12 live rows and 0 dead rows; 12 rows in sample, 12 estimated total rows
analyzing "pg_catalog.pg_statistic_ext_data"
"pg_statistic_ext_data": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_user_mapping"
"pg_user_mapping": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_subscription"
"pg_subscription": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_attribute"
"pg_attribute": scanned 57 of 57 pages, containing 3059 live rows and 143 dead rows; 3059 rows in sample, 3059 estimated total rows
analyzing "pg_catalog.pg_proc"
"pg_proc": scanned 94 of 94 pages, containing 3204 live rows and 4 dead rows; 3204 rows in sample, 3204 estimated total rows
analyzing "pg_catalog.pg_class"
"pg_class": scanned 13 of 13 pages, containing 413 live rows and 33 dead rows; 413 rows in sample, 413 estimated total rows
analyzing "pg_catalog.pg_attrdef"
"pg_attrdef": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
analyzing "pg_catalog.pg_constraint"
"pg_constraint": scanned 3 of 3 pages, containing 114 live rows and 0 dead rows; 114 rows in sample, 114 estimated total rows
analyzing "pg_catalog.pg_inherits"
"pg_inherits": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_index"
"pg_index": scanned 4 of 4 pages, containing 164 live rows and 8 dead rows; 164 rows in sample, 164 estimated total rows
analyzing "pg_catalog.pg_operator"
"pg_operator": scanned 14 of 14 pages, containing 804 live rows and 0 dead rows; 804 rows in sample, 804 estimated total rows
analyzing "pg_catalog.pg_opfamily"
"pg_opfamily": scanned 2 of 2 pages, containing 146 live rows and 0 dead rows; 146 rows in sample, 146 estimated total rows
analyzing "pg_catalog.pg_opclass"
"pg_opclass": scanned 3 of 3 pages, containing 177 live rows and 0 dead rows; 177 rows in sample, 177 estimated total rows
analyzing "pg_catalog.pg_am"
"pg_am": scanned 1 of 1 pages, containing 7 live rows and 0 dead rows; 7 rows in sample, 7 estimated total rows
analyzing "pg_catalog.pg_amop"
"pg_amop": scanned 7 of 7 pages, containing 945 live rows and 0 dead rows; 945 rows in sample, 945 estimated total rows
analyzing "pg_catalog.pg_amproc"
"pg_amproc": scanned 5 of 5 pages, containing 697 live rows and 0 dead rows; 697 rows in sample, 697 estimated total rows
analyzing "pg_catalog.pg_language"
"pg_language": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows
analyzing "pg_catalog.pg_largeobject_metadata"
"pg_largeobject_metadata": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_aggregate"
"pg_aggregate": scanned 2 of 2 pages, containing 145 live rows and 0 dead rows; 145 rows in sample, 145 estimated total rows
analyzing "pg_catalog.pg_statistic_ext"
"pg_statistic_ext": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_rewrite"
"pg_rewrite": scanned 13 of 13 pages, containing 140 live rows and 5 dead rows; 140 rows in sample, 140 estimated total rows
analyzing "pg_catalog.pg_trigger"
"pg_trigger": scanned 1 of 1 pages, containing 12 live rows and 0 dead rows; 12 rows in sample, 12 estimated total rows
analyzing "pg_catalog.pg_event_trigger"
"pg_event_trigger": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_description"
"pg_description": scanned 44 of 44 pages, containing 5099 live rows and 0 dead rows; 5099 rows in sample, 5099 estimated total rows
analyzing "pg_catalog.pg_cast"
"pg_cast": scanned 2 of 2 pages, containing 230 live rows and 0 dead rows; 230 rows in sample, 230 estimated total rows
analyzing "pg_catalog.pg_enum"
"pg_enum": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
analyzing "pg_catalog.pg_namespace"
"pg_namespace": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows
analyzing "pg_catalog.pg_conversion"
"pg_conversion": scanned 2 of 2 pages, containing 128 live rows and 0 dead rows; 128 rows in sample, 128 estimated total rows
analyzing "pg_catalog.pg_depend"
"pg_depend": scanned 66 of 66 pages, containing 8884 live rows and 140 dead rows; 8884 rows in sample, 8884 estimated total rows
analyzing "pg_catalog.pg_database"
"pg_database": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows
analyzing "pg_catalog.pg_db_role_setting"
"pg_db_role_setting": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_tablespace"
"pg_tablespace": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
analyzing "pg_catalog.pg_auth_members"
"pg_auth_members": scanned 1 of 1 pages, containing 3 live rows and 0 dead rows; 3 rows in sample, 3 estimated total rows
analyzing "pg_catalog.pg_shdepend"
"pg_shdepend": scanned 1 of 1 pages, containing 12 live rows and 0 dead rows; 12 rows in sample, 12 estimated total rows
analyzing "pg_catalog.pg_shdescription"
"pg_shdescription": scanned 1 of 1 pages, containing 3 live rows and 0 dead rows; 3 rows in sample, 3 estimated total rows
analyzing "pg_catalog.pg_ts_config"
"pg_ts_config": scanned 1 of 1 pages, containing 29 live rows and 0 dead rows; 29 rows in sample, 29 estimated total rows
analyzing "pg_catalog.pg_ts_config_map"
"pg_ts_config_map": scanned 3 of 3 pages, containing 551 live rows and 0 dead rows; 551 rows in sample, 551 estimated total rows
analyzing "pg_catalog.pg_ts_dict"
"pg_ts_dict": scanned 1 of 1 pages, containing 29 live rows and 0 dead rows; 29 rows in sample, 29 estimated total rows
analyzing "pg_catalog.pg_ts_parser"
"pg_ts_parser": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
analyzing "pg_catalog.pg_ts_template"
"pg_ts_template": scanned 1 of 1 pages, containing 5 live rows and 0 dead rows; 5 rows in sample, 5 estimated total rows
analyzing "pg_catalog.pg_extension"
"pg_extension": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
analyzing "pg_catalog.pg_foreign_data_wrapper"
"pg_foreign_data_wrapper": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_foreign_server"
"pg_foreign_server": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_policy"
"pg_policy": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_replication_origin"
"pg_replication_origin": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_default_acl"
"pg_default_acl": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_init_privs"
"pg_init_privs": scanned 3 of 3 pages, containing 199 live rows and 0 dead rows; 199 rows in sample, 199 estimated total rows
analyzing "pg_catalog.pg_seclabel"
"pg_seclabel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_shseclabel"
"pg_shseclabel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_collation"
"pg_collation": scanned 26 of 26 pages, containing 810 live rows and 0 dead rows; 810 rows in sample, 810 estimated total rows
analyzing "pg_catalog.pg_partitioned_table"
"pg_partitioned_table": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_range"
"pg_range": scanned 1 of 1 pages, containing 6 live rows and 0 dead rows; 6 rows in sample, 6 estimated total rows
analyzing "pg_catalog.pg_transform"
"pg_transform": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_sequence"
"pg_sequence": scanned 1 of 1 pages, containing 1 live rows and 1 dead rows; 1 rows in sample, 1 estimated total rows
analyzing "pg_catalog.pg_publication"
"pg_publication": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_publication_rel"
"pg_publication_rel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_subscription_rel"
"pg_subscription_rel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "pg_catalog.pg_largeobject"
"pg_largeobject": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
analyzing "information_schema.sql_parts"
"sql_parts": scanned 1 of 1 pages, containing 10 live rows and 0 dead rows; 10 rows in sample, 10 estimated total rows
analyzing "information_schema.sql_implementation_info"
"sql_implementation_info": scanned 1 of 1 pages, containing 12 live rows and 0 dead rows; 12 rows in sample, 12 estimated total rows
analyzing "information_schema.sql_features"
"sql_features": scanned 8 of 8 pages, containing 713 live rows and 0 dead rows; 713 rows in sample, 713 estimated total rows
analyzing "information_schema.sql_sizing"
"sql_sizing": scanned 1 of 1 pages, containing 23 live rows and 0 dead rows; 23 rows in sample, 23 estimated total rows
[2023-01-09 16:43:34] completed in 529 msEXPLAIN（ANALYZE，VERBOSE，BUFFERS）输出以下内容：
Aggregate (cost=184942.19..184942.20 rows=1 width=8) (actual time=545.520..545.524 rows=1 loops=1)
" Output: COALESCE((count(*) + sum(convo.votes)), '0'::bigint)"
Buffers: shared hit=107922 read=9064
CTE convo
-> Recursive Union (cost=4.82..184276.59 rows=7803 width=26) (actual time=22.848..545.265 rows=383 loops=1)
Buffers: shared hit=107922 read=9064
-> Nested Loop (cost=4.82..161.30 rows=13 width=26) (actual time=22.845..23.245 rows=80 loops=1)
" Output: conversation.id, conversation.votes, conversation.""parentId"", conversation.""createdUtc"""
Inner Unique: true
Buffers: shared hit=370
-> Bitmap Heap Scan on public.conversation_stocks_stock css_1 (cost=4.40..51.58 rows=13 width=7) (actual time=0.035..0.100 rows=80 loops=1)
" Output: css_1.""conversationId"", css_1.""stockCode"""
" Recheck Cond: ((css_1.""stockCode"")::text = 'AAA'::text)"
Heap Blocks: exact=48
Buffers: shared hit=50
" -> Bitmap Index Scan on ""IDX_927e043874ad9469e451671a53"" (cost=0.00..4.39 rows=13 width=0) (actual time=0.023..0.023 rows=80 loops=1)"
" Index Cond: ((css_1.""stockCode"")::text = 'AAA'::text)"
Buffers: shared hit=2
" -> Index Scan using ""PK_864528ec4274360a40f66c29845"" on public.conversation (cost=0.42..8.44 rows=1 width=26) (actual time=0.004..0.004 rows=1 loops=80)"
" Output: conversation.id, conversation.""createdUtc"", conversation.type, conversation.votes, conversation.text, conversation.source, conversation.""sourceUrl"", conversation.""parentId"""
" Index Cond: ((conversation.id)::text = (css_1.""conversationId"")::text)"
Buffers: shared hit=320
-> Hash Join (cost=4.23..18395.92 rows=779 width=26) (actual time=8.887..65.172 rows=41 loops=8)
" Output: thread.id, thread.votes, thread.""parentId"", thread.""createdUtc"""
" Hash Cond: ((thread.""parentId"")::text = (com.id)::text)"
Buffers: shared hit=107552 read=9064
-> Seq Scan on public.conversation thread (cost=0.00..17345.66 rows=276866 width=26) (actual time=0.002..28.900 rows=276866 loops=8)
" Output: thread.id, thread.""createdUtc"", thread.type, thread.votes, thread.text, thread.source, thread.""sourceUrl"", thread.""parentId"""
Buffers: shared hit=107552 read=9064
-> Hash (cost=2.60..2.60 rows=130 width=218) (actual time=0.109..0.109 rows=48 loops=8)
Output: com.id
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> WorkTable Scan on convo com (cost=0.00..2.60 rows=130 width=218) (actual time=0.086..0.090 rows=48 loops=8)
Output: com.id
-> Nested Loop Left Join (cost=0.29..664.49 rows=223 width=4) (actual time=545.489..545.490 rows=0 loops=1)
Output: convo.votes
Buffers: shared hit=107922 read=9064
-> CTE Scan on convo (cost=0.00..195.08 rows=39 width=222) (actual time=545.488..545.489 rows=0 loops=1)
" Output: convo.id, convo.votes, convo.""parentId"", convo.""createdUtc"""
" Filter: ((convo.""createdUtc"" > '2022-01-01 00:00:00'::timestamp without time zone) AND (convo.""createdUtc"" <= '2022-02-01 00:00:00'::timestamp without time zone))"
Rows Removed by Filter: 383
Buffers: shared hit=107922 read=9064
" -> Index Only Scan using ""IDX_f78b7bf984f96648653812c550"" on public.conversation_stocks_stock css (cost=0.29..11.98 rows=6 width=7) (never executed)"
" Output: css.""conversationId"""
" Index Cond: (css.""conversationId"" = (convo.id)::text)"
Heap Fetches: 0
Planning:
Buffers: shared hit=43
Planning Time: 0.963 ms
JIT:
Functions: 27
" Options: Inlining false, Optimization false, Expressions true, Deforming true"
" Timing: Generation 2.297 ms, Inlining 0.000 ms, Optimization 1.153 ms, Emission 22.291 ms, Total 25.740 ms"
Execution Time: 548.467 ms
1条答案
按热度按时间nwlqm0z11#
您需要会话的索引.“parentId”。