加入1M行表的查询速度慢 [英] Join query on 1M row table slow
问题描述
我有一个非常简单的选择查询,它连接一个表格
(p)与125K行和另一个表格(pc)几乎是一行一百万行:
从product_categories pc中选择p。*
内部联接产品p
on pc.product_id = p.id
其中pc.category_id = $ category_id
由p.title订购
限制25
抵消$ offset
查询通常需要大约五秒钟来执行
(所有其他PG查询执行得足够快)。我对所需的一切都有
索引,并且EXPLAIN显示
他们正在被使用。还有什么我可以做的吗
提高性能 - 比如在配置中调整一些设置
?
Redhat 9 ,PG 7.4.1。
__________________________________
你是Yahoo!?
Yahoo!财务:通过在线提交快速获得退款。
http://纳税。 yahoo.com/filing.html
--------------------------- (广播结束)---------------------------
提示6:您是否搜索了我们的列表档案?
http://archives.postgresql.org >
I have a pretty simple select query that joins a table
(p) with 125K rows with another table (pc) with almost
one million rows:
select p.*
from product_categories pc
inner join products p
on pc.product_id = p.id
where pc.category_id = $category_id
order by p.title
limit 25
offset $offset
The query usually takes about five seconds to execute
(all other PG queries perform fast enough). I have
indexes on everything needed, and EXPLAIN shows
they''re being used. Is there anything else I can do to
improve performance - such as tweaking some settings
in the config?
Redhat 9, PG 7.4.1.
__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
推荐答案
category_id
由p.title订购
限制25
offset
category_id
order by p.title
limit 25
offset
offset
查询通常需要大约五秒才能执行
(所有其他PG查询执行得足够快)。我对所需的一切都有
索引,并且EXPLAIN显示
他们正在被使用。还有什么我可以做的吗
提高性能 - 比如在配置中调整一些设置
?
Redhat 9 ,PG 7.4.1。
__________________________________
你是Yahoo!?
Yahoo!财务:通过在线提交快速获得退款。
http://纳税。 yahoo.com/filing.html
--------------------------- (广播结束)---------------------------
提示6:您是否搜索了我们的列表档案?
http://archives.postgresql.org >
offset
The query usually takes about five seconds to execute
(all other PG queries perform fast enough). I have
indexes on everything needed, and EXPLAIN shows
they''re being used. Is there anything else I can do to
improve performance - such as tweaking some settings
in the config?
Redhat 9, PG 7.4.1.
__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
2004年2月10日星期二,CSN写道:
On Tue, 10 Feb 2004, CSN wrote:
我有一个非常简单的选择查询加入表
(p)125K行与另一个表(pc)几乎有一百万行:
从product_categories pc中选择p。*
内连接产品p
在pc.product_id = p.id
其中pc.category_id =
I have a pretty simple select query that joins a table
(p) with 125K rows with another table (pc) with almost
one million rows:
select p.*
from product_categories pc
inner join products p
on pc.product_id = p.id
where pc.category_id =
这篇关于加入1M行表的查询速度慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!