Postgresql使用索引对连接表进行排序
我目前正在研究Postgres 9.2中的复杂排序问题
您可以在此处找到本课题(简体)中使用的源代码: http://sqlfiddle.com/#!12/9857e/11 我有一个包含不同类型的各种列的巨大(>>>&n; 20Mio行)表. CREATE TABLE data_table ( id bigserial PRIMARY KEY,column_a character(1),column_b integer -- ~100 more columns ); 假设我想在2列(ASC)上对此表进行排序. 为了实现这些目标,我执行以下操作: CREATE TABLE meta_table ( id bigserial PRIMARY KEY,id_data bigint NOT NULL -- refers to the data_table ); --Function to get the Column A of the current row CREATE OR REPLACE FUNCTION get_column_a(bigint) RETURNS character AS 'SELECT column_a FROM data_table WHERE id=$1' LANGUAGE sql IMMUTABLE STRICT; --Function to get the Column B of the current row CREATE OR REPLACE FUNCTION get_column_b(bigint) RETURNS integer AS 'SELECT column_b FROM data_table WHERE id=$1' LANGUAGE sql IMMUTABLE STRICT; --Creating a index on expression: CREATE INDEX meta_sort_index ON meta_table USING btree (get_column_a(id_data),get_column_b(id_data),id_data); 然后我将data_table的Id复制到meta_table: INSERT INTO meta_table(id_data) (SELECT id FROM data_table); 稍后我可以使用类似的简单插入向表中添加其他行. SELECT get_column_a(id_data),id_data FROM meta_table ORDER BY 1,2,3 OFFSET 900000 LIMIT 100; (如果我想要所有数据,可以在data_table上使用额外的INNER JOIN.) Limit (cost=498956.59..499012.03 rows=100 width=8) -> Index Only Scan using meta_sort_index on meta_table (cost=0.00..554396.21 rows=1000000 width=8) 这是一个非常有效的计划(Postgres 9.2中的Index Only Scans是新的). SELECT get_column_a(id_data),id_data FROM meta_table WHERE (get_column_a(id_data),id_data ) >= (get_column_a(587857),get_column_b(587857),587857 ) ORDER BY 1,3 LIMIT 100; 这运行得更快.结果计划是: Limit (cost=0.51..61.13 rows=100 width=8) -> Index Only Scan using meta_sort_index on meta_table (cost=0.51..193379.65 rows=318954 width=8) Index Cond: (ROW((get_column_a(id_data)),(get_column_b(id_data)),id_data) >= ROW('Z'::bpchar,27857,587857)) 到目前为止,一切都很完美,postgres做得很好! 我们假设我想将第二列的顺序更改为DESC. SELECT get_column_a(id_data),id_data FROM meta_table WHERE (get_column_a(id_data) > get_column_a(587857)) OR (get_column_a(id_data) = get_column_a(587857) AND ((get_column_b(id_data) > get_column_b(587857)) OR ( (get_column_b(id_data) = get_column_b(587857)) AND (id_data >= 587857)))) ORDER BY 1,3 LIMIT 100; 现在,计划更改和查询变得缓慢: Limit (cost=0.00..1095.94 rows=100 width=8) -> Index Only Scan using meta_sort_index on meta_table (cost=0.00..1117877.41 rows=102002 width=8) Filter: (((get_column_a(id_data)) > 'Z'::bpchar) OR (((get_column_a(id_data)) = 'Z'::bpchar) AND (((get_column_b(id_data)) > 27857) OR (((get_column_b(id_data)) = 27857) AND (id_data >= 587857))))) 如何使用DESC-Ordering的高效旧计划? (我已经尝试使用自己的运算符类声明自己的类型,但这太慢了)
你需要重新思考你的方法.从哪里开始?这是一个明显的例子,基本上是你对SQL所采用的那种功能方法的性能限制.函数在很大程度上是计划不透明的,并且由于存储过程的计划无法折叠在一起,因此对于检索到的每一行,您在data_table上强制执行两次不同的查找.
现在,更糟糕的是,您正在根据另一个表中的数据索引一个表.这可能适用于仅附加工作负载(允许插入但没有更新),但如果data_table可以应用更新,它将无法工作.如果data_table中的数据发生变化,则会使索引返回错误的结果. 在这些情况下,您几乎总是更好地在连接中写入显式,并让计划者找出检索数据的最佳方法. 现在您的问题是,当您更改第二列的顺序时,您的索引变得不那么有用(并且更加密集的磁盘I / O).另一方面,如果data_table上有两个不同的索引并且有一个显式连接,PostgreSQL可以更轻松地处理这个问题. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |