Skip site navigation (1) Skip section navigation (2)

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: 2008-01-30 03:39:12
Message-ID: 47A053B3.C5F7.004C.0@nsasia.co.th (view raw or flat)
Thread:
Lists: pgsql-sql
SQL I use for create related table and view:
 
 
CREATE TABLE t_payment_detail
(
  "sysid" bigserial NOT NULL,
  receiptno varchar(10) NOT NULL,
  refpath varchar(255) NOT NULL,
  refno varchar(100) NOT NULL,
  CONSTRAINT t_payment_detail_pkey PRIMARY KEY ("sysid")
) 
WITHOUT OIDS;
 
CREATE TABLE t_receipt_cancel
(
  receiptsysid int8 NOT NULL,
  amount float8 NOT NULL,
  receiptcomment varchar(255) NOT NULL,
  CONSTRAINT t_receipt_cancel_pkey PRIMARY KEY (receiptsysid)
) 
WITH OIDS;
 
CREATE TABLE t_charge
(
  "sysid" bigserial NOT NULL,
  mbrsysid int8 NOT NULL,
  chargelistsysid int8 NOT NULL,
  refno varchar(20) NOT NULL,
  chargedate timestamp NOT NULL,
  quantity float8 NOT NULL,
  unitprice float8 NOT NULL,
  amount float8 NOT NULL,
  vat float8 NOT NULL,
  service float8 NOT NULL,
  CONSTRAINT t_charge_pkey PRIMARY KEY ("sysid")
) 
WITH OIDS;
 

CREATE TABLE t_posbill
(
  "sysid" bigserial NOT NULL,
  billno varchar(20) NOT NULL DEFAULT ''::character varying,
  billdate timestamp NOT NULL DEFAULT '1601-01-01 00:00:00'::timestamp
without time zone,
  mbrsysid int8 NOT NULL DEFAULT 0,
  totalamount float8 NOT NULL DEFAULT 0,
  totalvat float8 NOT NULL DEFAULT 0,
  totalservice float8 NOT NULL DEFAULT 0,
  CONSTRAINT t_posbill_pkey PRIMARY KEY ("sysid")
) 
WITHOUT OIDS;
 
 
 
CREATE TABLE t_creditnotes
(
  "sysid" bigserial NOT NULL,
  mbrsysid int8 NOT NULL,
  chargelistsysid int8 NOT NULL,
  chitno varchar(20) NOT NULL,
  chitdate timestamp NOT NULL,
  refno varchar(20) NOT NULL,
  chargedate timestamp NOT NULL,
  quantity float8 NOT NULL,
  unitprice float8 NOT NULL,
  amount float8 NOT NULL,
  vat float8 NOT NULL,
  service float8 NOT NULL,
  CONSTRAINT t_creditnotes_pkey PRIMARY KEY ("sysid")
) 
WITH OIDS;
 
 
 
CREATE TABLE t_invoice
(
  "sysid" bigserial NOT NULL,
  mbrsysid int8 NOT NULL,
  chargelistsysid int8 NOT NULL,
  invno varchar(50) NOT NULL,
  invdate timestamp NOT NULL,
  totalvalue float8 NOT NULL,
  totalvat float8 NOT NULL,
  totalservice float8 NOT NULL,
  CONSTRAINT t_invoice_pkey PRIMARY KEY ("sysid")
) 
WITH OIDS;
 
CREATE TABLE t_receipt
(
  "sysid" bigserial NOT NULL,
  mbrsysid int8 NOT NULL,
  receiptno varchar(10) NOT NULL,
  receiptdate timestamp NOT NULL,
  paymethod varchar(30) NOT NULL,
  amount float8 NOT NULL,
  flagprint bool NOT NULL DEFAULT false,
  CONSTRAINT t_receipt_pkey PRIMARY KEY ("sysid")
) 
WITH OIDS;
 
CREATE TABLE t_moneytransfer
(
  "sysid" bigserial NOT NULL,
  mbrsysid int8 NOT NULL,
  transferno varchar(10) NOT NULL,
  transferdate timestamp NOT NULL,
  transferamount float8 NOT NULL,
  CONSTRAINT t_moneytransfer_pkey PRIMARY KEY ("sysid")
) 
WITH OIDS;
 
 
 
CREATE TABLE t_carryforward
(
  "sysid" bigserial NOT NULL,
  mbrsysid int8 NOT NULL,
  cfno varchar(10) NOT NULL,
  cfdate timestamp NOT NULL,
  amount float8 NOT NULL,
  CONSTRAINT t_carryforward_pkey PRIMARY KEY ("sysid")
) 
WITH OIDS;
 

CREATE OR REPLACE VIEW v_invtransaction_main AS 
((((( 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
   FROM t_charge
  GROUP BY 'C'::text || t_charge.refno::text, t_charge.mbrsysid, 
'CHIT'::text , t_charge.chargedate, t_charge.refno
UNION ALL 
 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
   FROM t_posbill)
UNION ALL 
 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, - s
um(t_creditnotes.vat) AS totvat, -
sum(t_creditnotes.service) AS totservice, 'Cr' AS cr_dr,
t_creditnotes.refno AS transrefno
   FROM t_creditnotes
  GROUP BY 'CN'::text || t_creditnotes.refno::text,
t_creditnotes.mbrsysid, 'Credit Note'::text, t_creditnotes.chargedate,
t_creditnotes.refno)
UNION ALL 
 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
   FROM t_invoice
  GROUP BY 'I'::text || t_invoice.invno::text, t_invoice.mbrsysid,
'Monthly Invoice'::text, t_invoice.invdate,t_invoice.invno)
UNION ALL 
 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
   FROM t_receipt
  WHERE NOT (t_receipt."sysid" IN ( SELECT
t_receipt_cancel.receiptsysid FROM t_receipt_cancel)))
UNION ALL 
 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
   FROM t_moneytransfer)
UNION ALL 
 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
   FROM t_carryforward;
 
 
 
 
 
CREATE OR REPLACE VIEW v_invtransaction AS 
 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
   FROM t_payment_detail
   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;
 
 
 
 
 
 
 



>>> Andreas Joseph Krogh <andreak(at)officenet(dot)no> 1/29/2008 17:24 >>>
On Tuesday 29 January 2008 09:18:00 Premsun Choltanwanich wrote:
> I already install the latest version of PostgreSQL on my machine then
try
> again. I found that it has a little improvement about 0.5 second but
I
> think it still slow.
>
> What is the 'more detail' you need?

Your tables, views and index definitions.

> Any other advise?

You haven't provided any information on how your tables/views look like
and 
what indexes you have defined. A rule of thumb is to define an index
for each 
column you join on.

-- 
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to
|
Karenslyst All* 11      | know how to do a thing and to watch        
|
PO. Box 529 Sk*yen      | somebody else doing it wrong, without      
|
0214 Oslo               | comment.                                   
|
NORWAY                  |                                            
|
Tlf:    +47 24 15 38 90 |                                            
|
Fax:    +47 24 15 38 91 |                                            
|
Mobile: +47 909  56 963 |                                            
|
------------------------+---------------------------------------------+

---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space m
ap settings

NETsolutions Asia Limited
+66 (2) 237 7247
http://www.nsasia.co.th

In response to

pgsql-sql by date

Next:From: Steve MidgleyDate: 2008-01-30 04:31:58
Subject: Re: Proposed archival read only trigger on rows - prevent history modification
Previous:From: Premsun CholtanwanichDate: 2008-01-30 03:38:49
Subject: Re: Slow Query problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group