Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Header And Logo
|
Site Navigation
Slow Query problem
- From: "Premsun Choltanwanich" <Premsun(at)nsasia(dot)co(dot)th>
- To: <pgsql-sql(at)postgresql(dot)org>
- Subject: Slow Query problem
- Date: Mon, 28 Jan 2008 14:18:24 +0700
- Message-id: <479DE418.C5F7.004C.0@nsasia.co.th> <text/plain>
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=windows-874">
<META content="MSHTML 6.00.2900.3243" name=GENERATOR></HEAD>
<BODY style="MARGIN: 4px 4px 1px; FONT: 10pt MS Sans Serif">
<DIV>Dear All,<BR> <BR>I am currently using PostgreSQL database version 8.0.13. My problem relates to a slow result when a query using a defined view joins to another table for a result.<BR> <BR>Background: I have 7 tables of invoice transactions. The tables are slightly different in that they record different data (some different columns in each table). There are about 250,000 records when a union view is created. A simply query on this union performs satisfactorily.</DIV>
<DIV> </DIV>
<DIV>The invoice table union view is then joined with a table of receipts (which have a total of about 150,000 records). </DIV>
<DIV> </DIV>
<DIV>It takes around 3.5 seconds for "select * from view_transaction where member_id = 999 and receipt_no is null" (which returns unpaid invoices).</DIV>
<DIV> </DIV>
<DIV>By hard coding I created a single table from the 7 invoice tables (instead of creating a union) and then used it with receipt table. This time for the same query improved to 1.8 seconds.</DIV>
<DIV> </DIV>
<DIV>To further improve things I tried to code the selection rather than to use a view, and so "select * from temp_transaction where member_id = 999 and receipt_no is null" provided the result in .5 second. (2 records returned containing the details of receipt_no, transaction_no, transaction_type, transaction_amount, member_id).</DIV>
<DIV> </DIV>
<DIV>I would prefer to be able to have completed the above by using unions and views. Is it possible to do this, or am I better creating a permanent table of invoices and writing the query as I did above?</DIV>
<DIV> </DIV>
<DIV>Any comments on this and suggestions would be appreciated. If there is documentation where I can read up please let me have a link.<BR> <BR>Thank You,<BR> <BR>Premsun<BR> </DIV><BR>
<p>
<font size="2" color="#0033cc" face="Trebuchet MS"><b>NETsolutions Asia
Limited </b></font>
</p>
<p>
<font size="2" color="#0033cc" face="Trebuchet MS"><b>+66 (2) 237 7247 </b></font>
</p>
<p>
<a href="http://www.nsasia.co.th";><img border="0" alt="NETsolutions Asia Limited" src="cid:RQHFQWYUJULD.44e30700.jpg">
</a> </p>
</BODY></HTML>
- Follow-Ups:
- Re: Slow Query problem
- From: Andreas Joseph Krogh
- Re: Slow Query problem
- Prev by Date: Re: extract or date_part on an interval? How many e
- Next by Date: Re: Slow Query problem
- Previous by thread: extract or date_part on an interval? How many e
- Next by thread: Re: Slow Query problem
- Indexes: