Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Header And Logo
|
Site Navigation
Re: Slow Query problem
- From: "Premsun Choltanwanich" <Premsun(at)nsasia(dot)co(dot)th>
- To: "Andreas Joseph Krogh" <andreak(at)officenet(dot)no>
- Cc: <pgsql-sql(at)postgresql(dot)org>,"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
- Subject: Re: Slow Query problem
- Date: Wed, 30 Jan 2008 10:39:12 +0700
- Message-id: <47A053B3.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><STRONG>SQL I use for create related table and view:</STRONG></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_payment_detail<BR>(<BR> "sysid" bigserial NOT NULL,<BR> receiptno varchar(10) NOT NULL,<BR> refpath varchar(255) NOT NULL,<BR> refno varchar(100) NOT NULL,<BR> CONSTRAINT t_payment_detail_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITHOUT OIDS;</DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_receipt_cancel<BR>(<BR> receiptsysid int8 NOT NULL,<BR> amount float8 NOT NULL,<BR> receiptcomment varchar(255) NOT NULL,<BR> CONSTRAINT t_receipt_cancel_pkey PRIMARY KEY (receiptsysid)<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_charge<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> chargelistsysid int8 NOT NULL,<BR> refno varchar(20) NOT NULL,<BR> chargedate timestamp NOT NULL,<BR> quantity float8 NOT NULL,<BR> unitprice float8 NOT NULL,<BR> amount float8 NOT NULL,<BR> vat float8 NOT NULL,<BR> service float8 NOT NULL,<BR> CONSTRAINT t_charge_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV><BR>CREATE TABLE t_posbill<BR>(<BR> "sysid" bigserial NOT NULL,<BR> billno varchar(20) NOT NULL DEFAULT ''::character varying,<BR> billdate timestamp NOT NULL DEFAULT '1601-01-01 00:00:00'::timestamp without time zone,<BR> mbrsysid int8 NOT NULL DEFAULT 0,<BR> totalamount float8 NOT NULL DEFAULT 0,<BR> totalvat float8 NOT NULL DEFAULT 0,<BR> totalservice float8 NOT NULL DEFAULT 0,<BR> CONSTRAINT t_posbill_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITHOUT OIDS;</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_creditnotes<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> chargelistsysid int8 NOT NULL,<BR> chitno varchar(20) NOT NULL,<BR> chitdate timestamp NOT NULL,<BR> refno varchar(20) NOT NULL,<BR> chargedate timestamp NOT NULL,<BR> quantity float8 NOT NULL,<BR> unitprice float8 NOT NULL,<BR> amount float8 NOT NULL,<BR> vat float8 NOT NULL,<BR> service float8 NOT NULL,<BR> CONSTRAINT t_creditnotes_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_invoice<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> chargelistsysid int8 NOT NULL,<BR> invno varchar(50) NOT NULL,<BR> invdate timestamp NOT NULL,<BR> totalvalue float8 NOT NULL,<BR> totalvat float8 NOT NULL,<BR> totalservice float8 NOT NULL,<BR> CONSTRAINT t_invoice_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_receipt<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> receiptno varchar(10) NOT NULL,<BR> receiptdate timestamp NOT NULL,<BR> paymethod varchar(30) NOT NULL,<BR> amount float8 NOT NULL,<BR> flagprint bool NOT NULL DEFAULT false,<BR> CONSTRAINT t_receipt_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_moneytransfer<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> transferno varchar(10) NOT NULL,<BR> transferdate timestamp NOT NULL,<BR> transferamount float8 NOT NULL,<BR> CONSTRAINT t_moneytransfer_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>CREATE TABLE t_carryforward<BR>(<BR> "sysid" bigserial NOT NULL,<BR> mbrsysid int8 NOT NULL,<BR> cfno varchar(10) NOT NULL,<BR> cfdate timestamp NOT NULL,<BR> amount float8 NOT NULL,<BR> CONSTRAINT t_carryforward_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;</DIV>
<DIV> </DIV>
<DIV><BR>CREATE OR REPLACE VIEW v_invtransaction_main AS <BR>((((( SELECT 'C'::text || t_charge.refno::text AS transinvno, t_charge.mbrsysid, 'CHIT'::text AS particular, t_charge.chargedate AS transdate, sum(t_charge.amount) AS totamount, sum(t_charge.vat) AS totvat, sum(t_charge.service) AS totservice, 'Dr' AS cr_dr, t_charge.refno AS transrefno<BR> FROM t_charge<BR> GROUP BY 'C'::text || t_charge.refno::text, t_charge.mbrsysid, 'CHIT'::text , t_charge.chargedate, t_charge.refno<BR>UNION ALL <BR> SELECT 'B'::text || t_posbill.billno::text AS transinvno, t_posbill.mbrsysid, 'POS'::text AS particular, t_posbill.billdate AS transdate, t_posbill.totalamount AS totamount, t_posbill.totalvat AS totvat, t_posbill.totalservice AS totservice, 'Dr' AS cr_dr, t_posbill.billno AS transrefno<BR> FROM t_posbill)<BR>UNION ALL <BR> SELECT 'CN'::text || t_creditnotes.refno::text AS transinvno, t_creditnotes.mbrsysid, 'Credit Note'::text AS particular, t_creditnotes.chargedate AS transdate, - sum(t_creditnotes.amount) AS totamount, - sum(t_creditnotes.vat) AS totvat, - sum(t_creditnotes.service) AS totservice, 'Cr' AS cr_dr, t_creditnotes.refno AS transrefno<BR> FROM t_creditnotes<BR> GROUP BY 'CN'::text || t_creditnotes.refno::text, t_creditnotes.mbrsysid, 'Credit Note'::text, t_creditnotes.chargedate, t_creditnotes.refno)<BR>UNION ALL <BR> SELECT 'I'::text || t_invoice.invno::text AS transinvno, t_invoice.mbrsysid, 'Monthly Invoice '::text AS particular, t_invoice.invdate AS transdate, sum(t_invoice.totalvalue) AS totamount, sum(t_invoice.totalvat) AS totvat, sum(t_invoice.totalservice) AS totservice, 'Dr' AS cr_dr, t_invoice.invno AS transrefno<BR> FROM t_invoice<BR> GROUP BY 'I'::text || t_invoice.invno::text, t_invoice.mbrsysid, 'Monthly Invoice'::text, t_invoice.invdate,t_invoice.invno)<BR>UNION ALL <BR> SELECT 'P'::text || t_receipt.receiptno::text AS transinvno, t_receipt.mbrsysid, t_receipt.paymethod::text AS particular, t_receipt.receiptdate AS transdate, t_receipt.amount * (- 1::double precision) AS totamount, '0' AS totvat, '0' AS totservice, 'Cr' AS cr_dr, t_receipt.receiptno AS transrefno<BR> FROM t_receipt<BR> WHERE NOT (t_receipt."sysid" IN ( SELECT t_receipt_cancel.receiptsysid FROM t_receipt_cancel)))<BR>UNION ALL <BR> SELECT 'T'::text || t_moneytransfer.transferno::text AS transinvno, t_moneytransfer.mbrsysid, 'Transfer'::text AS particular, t_moneytransfer.transferdate AS transdate, t_moneytransfer.transferamount * (- 1::double precision) AS totamount, '0' AS totvat, '0' AS totservice, 'Dr' AS cr_dr, t_moneytransfer.transferno AS transrefno<BR> FROM t_moneytransfer)<BR>UNION ALL <BR> SELECT 'CF'::text || t_carryforward.cfno::text AS transinvno, t_carryforward.mbrsysid, 'Carry Forward'::text AS particular, t_carryforward.cfdate AS transdate, t_carryforward.amount AS totamount, '0' AS totvat, '0' AS totservice, 'Dr' AS cr_dr, t_carryforward.cfno AS transrefno<BR> FROM t_carryforward;</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>CREATE OR REPLACE VIEW v_invtransaction AS <BR> SELECT t_payment_detail.receiptno, v_invtransaction_main.transinvno, v_invtransaction_main.mbrsysid, v_invtransaction_main.particular, v_invtransaction_main.transdate, v_invtransaction_main.totamount, v_invtransaction_main.totvat, v_invtransaction_main.totservice, v_invtransaction_main.cr_dr, v_invtransaction_main.transrefno<BR> FROM t_payment_detail<BR> RIGHT JOIN v_invtransaction_main ON v_invtransaction_main.transrefno::text = t_payment_detail.refno::text AND v_invtransaction_main.particular = t_payment_detail.refpath::text;<BR></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><BR><BR><BR>>>> Andreas Joseph Krogh <andreak(at)officenet(dot)no> 1/29/2008 17:24 >>><BR>On Tuesday 29 January 2008 09:18:00 Premsun Choltanwanich wrote:<BR>> I already install the latest version of PostgreSQL on my machine then try<BR>> again. I found that it has a little improvement about 0.5 second but I<BR>> think it still slow.<BR>><BR>> What is the 'more detail' you need?<BR><BR>Your tables, views and index definitions.<BR><BR>> Any other advise?<BR><BR>You haven't provided any information on how your tables/views look like and <BR>what indexes you have defined. A rule of thumb is to define an index for each <BR>column you join on.<BR><BR>-- <BR>Andreas Joseph Krogh <andreak(at)officenet(dot)no><BR>Senior Software Developer / Manager<BR>------------------------+---------------------------------------------+<BR>OfficeNet AS | The most difficult thing in the world is to |<BR>Karenslyst All? 11 | know how to do a thing and to watch |<BR>PO. Box 529 Sk?yen | somebody else doing it wrong, without |<BR>0214 Oslo | comment. |<BR>NORWAY | |<BR>Tlf: +47 24 15 38 90 | |<BR>Fax: +47 24 15 38 91 | |<BR>Mobile: +47 909 56 963 | |<BR>------------------------+---------------------------------------------+<BR><BR>---------------------------(end of broadcast)---------------------------<BR>TIP 5: don't forget to increase your free space map settings<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>
- References:
- Slow Query problem
- From: Premsun Choltanwanich
- Re: Slow Query problem
- From: Tom Lane
- Re: Slow Query problem
- From: Premsun Choltanwanich
- Re: Slow Query problem
- From: Andreas Joseph Krogh
- Slow Query problem
- Prev by Date: JOINing SET returning function.
- Next by Date: Re: Slow Query problem
- Previous by thread: Re: Slow Query problem
- Next by thread: Re: Slow Query problem
- Indexes: