pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow Query problem
7+ messages / 3 participants
[nested] [flat]
* Slow Query problem
@ 2008-01-28 07:18 Premsun Choltanwanich <Premsun@nsasia.co.th>
2008-01-28 12:26 ` Re: Slow Query problem Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 7+ messages in thread
From: Premsun Choltanwanich @ 2008-01-28 07:18 UTC (permalink / raw)
To: pgsql-sql
Dear All,
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.
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.
The invoice table union view is then joined with a table of receipts (which have a total of about 150,000 records).
It takes around 3.5 seconds for "select * from view_transaction where member_id = 999 and receipt_no is null" (which returns unpaid invoices).
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.
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).
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?
Any comments on this and suggestions would be appreciated. If there is documentation where I can read up please let me have a link.
Thank You,
Premsun
NETsolutions Asia Limited
+66 (2) 237 7247
http://www.nsasia.co.th
Attachments:
IMAGE.jpg (image/jpg, 6.3K) download
view image
* Re: Slow Query problem
2008-01-28 07:18 Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
@ 2008-01-28 12:26 ` Andreas Joseph Krogh <andreak@officenet.no>
2008-01-28 17:20 ` Re: Slow Query problem Tom Lane <tgl@sss.pgh.pa.us>
2008-01-30 03:38 ` Re: Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
0 siblings, 2 replies; 7+ messages in thread
From: Andreas Joseph Krogh @ 2008-01-28 12:26 UTC (permalink / raw)
To: pgsql-sql; +Cc: Premsun Choltanwanich <Premsun@nsasia.co.th>
On Monday 28 January 2008 08:18:24 Premsun Choltanwanich wrote:
> Dear All,
>
> 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.
>
> 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.
>
> The invoice table union view is then joined with a table of receipts (which
> have a total of about 150,000 records).
>
> It takes around 3.5 seconds for "select * from view_transaction where
> member_id = 999 and receipt_no is null" (which returns unpaid invoices).
>
> 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.
>
> 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).
>
> 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?
>
> Any comments on this and suggestions would be appreciated. If there is
> documentation where I can read up please let me have a link.
It is very hard to help without you providing the schema for the tables/views
involved. It sounds like you don't have any indexes if you experience
performance-problems on queries like "select * from view_transaction where
member_id = 999 and receipt_no is null". But again, without the definition
of the view and underlying tables, it's very hard to help.
--
Andreas Joseph Krogh <andreak@officenet.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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow Query problem
2008-01-28 07:18 Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
2008-01-28 12:26 ` Re: Slow Query problem Andreas Joseph Krogh <andreak@officenet.no>
@ 2008-01-28 17:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-29 08:18 ` Re: Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2008-01-28 17:20 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql@postgresql.org, "Premsun Choltanwanich" <Premsun@nsasia.co.th>
Andreas Joseph Krogh <andreak@officenet.no> writes:
> On Monday 28 January 2008 08:18:24 Premsun Choltanwanich wrote:
>> 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.
> It is very hard to help without you providing the schema for the tables/views
> involved.
One suggestion is that 8.2.x is significantly smarter about joins to
unions than previous releases were. Whether this would help in your
particular case is impossible to say, though, without more detail.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow Query problem
2008-01-28 07:18 Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
2008-01-28 12:26 ` Re: Slow Query problem Andreas Joseph Krogh <andreak@officenet.no>
2008-01-28 17:20 ` Re: Slow Query problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-01-29 08:18 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
2008-01-29 10:24 ` Re: Slow Query problem Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 7+ messages in thread
From: Premsun Choltanwanich @ 2008-01-29 08:18 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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?
Any other advise?
>>> Tom Lane <tgl@sss.pgh.pa.us> 1/29/2008 0:20 >>>
Andreas Joseph Krogh <andreak@officenet.no> writes:
> On Monday 28 January 2008 08:18:24 Premsun Choltanwanich wrote:
>> 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.
> It is very hard to help without you providing the schema for the tables/views
> involved.
One suggestion is that 8.2.x is significantly smarter about joins to
unions than previous releases were. Whether this would help in your
particular case is impossible to say, though, without more detail.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
NETsolutions Asia Limited
+66 (2) 237 7247
http://www.nsasia.co.th
Attachments:
IMAGE.jpg (image/jpg, 6.3K) download
view image
* Re: Slow Query problem
2008-01-28 07:18 Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
2008-01-28 12:26 ` Re: Slow Query problem Andreas Joseph Krogh <andreak@officenet.no>
2008-01-28 17:20 ` Re: Slow Query problem Tom Lane <tgl@sss.pgh.pa.us>
2008-01-29 08:18 ` Re: Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
@ 2008-01-29 10:24 ` Andreas Joseph Krogh <andreak@officenet.no>
2008-01-30 03:39 ` Re: Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
0 siblings, 1 reply; 7+ messages in thread
From: Andreas Joseph Krogh @ 2008-01-29 10:24 UTC (permalink / raw)
To: Premsun Choltanwanich <Premsun@nsasia.co.th>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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@officenet.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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Slow Query problem
2008-01-28 07:18 Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
2008-01-28 12:26 ` Re: Slow Query problem Andreas Joseph Krogh <andreak@officenet.no>
2008-01-28 17:20 ` Re: Slow Query problem Tom Lane <tgl@sss.pgh.pa.us>
2008-01-29 08:18 ` Re: Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
2008-01-29 10:24 ` Re: Slow Query problem Andreas Joseph Krogh <andreak@officenet.no>
@ 2008-01-30 03:39 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
0 siblings, 0 replies; 7+ messages in thread
From: Premsun Choltanwanich @ 2008-01-30 03:39 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql; Tom Lane <tgl@sss.pgh.pa.us>
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@officenet.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@officenet.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
Attachments:
IMAGE.jpg (image/jpg, 6.3K) download
view image
* Re: Slow Query problem
2008-01-28 07:18 Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
2008-01-28 12:26 ` Re: Slow Query problem Andreas Joseph Krogh <andreak@officenet.no>
@ 2008-01-30 03:38 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
1 sibling, 0 replies; 7+ messages in thread
From: Premsun Choltanwanich @ 2008-01-30 03:38 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; 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@officenet.no> 1/28/2008 19:26 >>>
On Monday 28 January 2008 08:18:24 Premsun Choltanwanich wrote:
> Dear All,
>
> 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.
>
> 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.
>
> The invoice table union view is then joined with a table of receipts
(which
> have a total of about 150,000 records).
>
> It takes around 3.5 seconds for "select * from view_transaction
where
> member_id = 999 and receipt_no is null" (which returns unpaid
invoices).
>
> 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.
>
> 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).
>
> 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?
>
> Any comments on this and suggestions would be appreciated. If there
is
> documentation where I can read up please let me have a link.
It is very hard to help without you providing the schema for the
tables/views
involved. It sounds like you don't have any indexes if you experience
performance-problems on queries like "select * from view_transaction
where
member_id = 999 and receipt_no is null". But again, without the
definition
of the view and underlying tables, it's very hard to help.
--
Andreas Joseph Krogh <andreak@officenet.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 |
|
------------------------+---------------------------------------------+
NETsolutions Asia Limited
+66 (2) 237 7247
http://www.nsasia.co.th
Attachments:
IMAGE.jpg (image/jpg, 6.3K) download
view image
end of thread, other threads:[~2008-01-30 03:39 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-28 07:18 Slow Query problem Premsun Choltanwanich <Premsun@nsasia.co.th>
2008-01-28 12:26 ` Andreas Joseph Krogh <andreak@officenet.no>
2008-01-28 17:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-29 08:18 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
2008-01-29 10:24 ` Andreas Joseph Krogh <andreak@officenet.no>
2008-01-30 03:39 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
2008-01-30 03:38 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox