我们如何改进庞大数据库的查询执行.. [英] How we improve query execution of a bulky database..

问题描述

嗨朋友们，


我在Postgresql 8.3工作。我有一个表A，这个表包含多个属性，我们已经对Id，liveId属性进行了索引。但是这个表还包含insertedDate，还有一个非常重要的事情就是每秒都会在这个表中发生大约30次点击。


当我们从这个表中获取记录时它需要很多时候昨天我把索引放在这个专栏上，在索引到4-5小时之后它工作得很棒，但是现在当表格大小增加时，它比以前花了太多时间。因此我放弃了那个索引，但我想提高抓取速度。


如果我再次进行索引，那么我将把它放到哪个方向。我是数据库处理的新手，所以请帮助我..


所以请给我指导..在此先感谢..

等待您的早期回复。 ..

Hi Friends,

I am working in Postgresql 8.3. In that I have one table A, this table contains number of attributes and we already put indexing on Id, liveId attributes. But this table also contains the insertedDate and one more and very important things is that in every seconds approx 30 hits occurred in this table.

When we are fetching the records from this table Its takes to much time. Yesterday I had put indexing on this column and after indexing till 4-5 hours it working awesome, but now when the table size is increase it was taking too much time even more than earlier. Therefore I had drop that index but I want to increase the fetching speed.

If I will put indexing again then which way I will put it. I am new to Database handling so please help me..

So Please gave me guidance.. Thanks in Advance..
Waiting for your early response...

推荐答案

您使用什么查询从表中获取数据？

What query do you use to fetch data from table?

postgresql是否有查询分析工具？

Does postgresql have a query analysis tool?

您好Rski，

感谢您的回复。


我使用子查询，查询是： -


select * from tableA where liveid in（select table from tableB where vehicleid in（select table from tableC where registrationNumber =''MH4R2423'）））和inserteddate>''2010-02-20' '和insertdate<''2010-02-21''。

当我在insertdate上编制索引直到4-5小时它的效果非常好。但是当表大小增加时，它需要花费太多时间，因此我放弃了该索引。


亲爱的RedSon，我是Postgres的新手

我只知道如果我们要查看qery性能，那么我们正在使用Explain Analyze" Users查询


我不是数据库专家我是开发人员因此我对此不太了解。我仍然试图解决这个问题，但如果你知道任何事情，那么Plz会帮助我。

衷心感谢您的帮助

Hi Rski,
Thanks for reply.

I am using subquery, the query is:-

select * from tableA where liveid in (select id from tableB where vehicleid in (select id from tableC where registrationNumber=''MH4R2423'')) and inserteddate>''2010-02-20'' and inserteddate<''2010-02-21''.

When I did indexing on inserteddate till 4-5 hours it work quite good. But when the table size is increase its takes too much time therefore I drop that index.

Dear RedSon, I am new to Postgres
and I only know that if we want to check the qery performance then we are using Explain Analyze "Users Query"

I am not a Database Guy I am a Developer So I can''t know too much about this. Still I am trying to fix this issue, but if you know anything then Plz help me.
Heartily Appreciate for your help

这篇关于我们如何改进庞大数据库的查询执行..的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！